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"}
-
1Take a look at string replacement (dba.stackexchange.com/a/172601/41320)Ilya Serbis– Ilya Serbis2018-03-27 14:28:01 +00:00Commented Mar 27, 2018 at 14:28
-
similar question added stackoverflow.com/questions/59908676/…Akshay Vijay Jain– Akshay Vijay Jain2020-01-25 11:58:05 +00:00Commented Jan 25, 2020 at 11:58
Add a comment
|
3 Answers
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)
6 Comments
Manu J4
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.
Pranav Unde
@klin: I tried your query it works. But is there any way that we get ids in {} and not simply mentioned in double quotes? rextester.com/HQS81928
klin
@PranavUnde - please ask a new question with an appropriate example.
Mahesh
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';
odlp
This is awesome, thanks! Here's an example with Rails ActiveRecord for anyone it may help: todayilearned.fyi/by/odlp/…
|
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:
#-Delete the field or element with specified path (for JSON arrays, negative integers count from the end) https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE#>Get JSON object at the specified path https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-OP-TABLE
2 Comments
Matheus Felipe
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
Velkumar
@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" }
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 *;
