CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecordjsonb
felipeecst
felipeecst

Reputation: 1415

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

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?

Upvotes: 0

Views: 300

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

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

Upvotes: 2

Related Questions