64

In postgresql 9.5, is there a way to rename an attribute in a jsonb field?

For example:

{ "nme" : "test" }

should be renamed to

{ "name" : "test"}
klin
  • 112,967
  • 15
  • 204
  • 232
T. Kong
  • 643
  • 1
  • 5
  • 5

3 Answers3

129

In UPDATE use delete (-) and concatenate (||) operators, e.g.:

create table example(id int primary key, js jsonb);
insert into example values
    (1, '{"nme": "test"}'),
    (2, '{"nme": "second test"}');

update example
set js = js - 'nme' || jsonb_build_object('name', js->'nme')
where js ? 'nme'
returning *;

 id |           js            
----+-------------------------
  1 | {"name": "test"}
  2 | {"name": "second test"}
(2 rows)
klin
  • 112,967
  • 15
  • 204
  • 232
  • 5
    In case somebody wonders, `returning *` is not mandatory for the query to work. It only causes a display (like a `select`) of all updated rows, that is, all rows. – Manu CJ Mar 21 '18 at 13:49
  • @klin: I tried your query it works. But is there any way that we get ids in {} and not simply mentioned in double quotes? http://rextester.com/HQS81928 – Pranav Unde May 04 '18 at 00:42
  • @PranavUnde - please ask a new question with an appropriate example. – klin May 04 '18 at 11:23
  • Its works fine, can you please tell how to update nested objects like {"skill":{"c":{"name":"C", "code":"c"}}}, how to update c attribute inside skill. – Mahesh May 22 '18 at 13:04
  • 1
    Found the solution, first add the object with new attribute and then remove the existing attribute. something like this. `update example SET js = JSONB_SET(js::JSONB, '{skill,c++}', js->'skill'->'c', true) where js->'skill' ? 'c';` `update example SET js = js#-'{skill, c}' where js->'skill' ? 'c';` – Mahesh May 22 '18 at 13:36
  • 1
    This is awesome, thanks! Here's an example with Rails ActiveRecord for anyone it may help: https://www.todayilearned.fyi/by/odlp/til-you-can-rename-postgresql-jsonb-attributes-in-a-query – odlp May 24 '19 at 13:35
26

I used the following for handling nested attributes and skipping any json that doesn't use the old name:

UPDATE table_name
SET json_field_name = jsonb_set(json_field_name #- '{path,to,old_name}',
                                '{path,to,new_name}',
                                json_field_name#>'{path,to,old_name}')
WHERE json_field_name#>'{path,to}' ? 'old_name';

just for reference docs:

andilabs
  • 22,159
  • 14
  • 114
  • 151
Emil Laine
  • 41,598
  • 9
  • 101
  • 157
  • That worked for me, although I messed up the first entry of `{path, to, old_name}` it didn't seem to affect the result and I still got the right thing – Matheus Felipe Jan 30 '19 at 00:00
  • @MatheusFelipe, can you please help me. I need to change LicenseDate to LicenseExpirationDate UPDATE dbo."dcp_OrgLevelEntityItems" SET "Attributes" = jsonb_set("Attributes" => '{LicenseDate}', '{LicenseExpirationDate}') WHERE "Id" = 1 Sample JSON - Column name is Attributes { "Name": "LIC", "Street": 223871, "City": 59390, "LicenseDate": "01092019" } – Velkumar May 15 '20 at 17:13
9

This is an old question, but still comes up high in the search rankings for this particular task. One approach that isn't all that JSON-ey but can still be a decent solution (if there is a minimal risk of key-naming collision) is to handle the field as TEXT, do a replace (could be a regex too) and then cast back to JSON.

Something like this, borrowing @klin's setup:

CREATE TABLE example(id INT PRIMARY KEY, js JSONB);
INSERT INTO example VALUES
    (1, '{"nme": "test"}'),
    (2, '{"nme": "second test"}');

UPDATE EXAMPLE
SET js = (REPLACE(js::TEXT, '"nme"', '"name"'))::JSONB
RETURNING *;
rocksteady
  • 1,697
  • 14
  • 18