PostgreSQL JSONB - Update key name on all rows with a single query

Question

I have a jsonb field in a PostgreSQL table and I want to change only the key name, keeping the same value for it.

Currently, I'm doing this in Rails like this:

Event.each do |event|
   event.metadata['new_key'] = metadata['old_key']
   event.metadata.delete('old_key')
   event.save
end

Is there a way to achieve the same result using a single update_all query?

Top level key? Sample value? Postgres version?

Erwin Brandstetter
– Erwin Brandstetter

2019-04-17 18:42:00 +00:00
Commented Apr 17, 2019 at 18:42 — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 17, 2019 at 18:42

Erwin Brandstetter · Accepted Answer · 2019-04-17 18:59:40Z

2

Assuming a top-level key and current PostgreSQL,
you can achieve this by removing the old key and adding the new - with the value copied from the old:

UPDATE event
SET    metadata = (metadata - 'old_key')
               || jsonb_build_object('new_key', metadata->'old_key')
WHERE  metadata ? 'old_key';  -- only where the key exists

db<>fiddle here

edited Apr 17, 2019 at 18:59

answered Apr 17, 2019 at 18:52

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL JSONB - Update key name on all rows with a single query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related