CODEWITHSUNDEEP
jsonpostgresqlsql-updatejsonbpostgresql-9.5
T. Kong
T. Kong

Reputation: 763

PostgreSQL rename attribute in jsonb field

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"}

Upvotes: 74

Views: 34122

Answers (3)

rocksteady
rocksteady

Reputation: 2242

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 *;

Upvotes: 17

Emil Laine
Emil Laine

Reputation: 42828

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:

Upvotes: 36

klin
klin

Reputation: 121604

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)

Upvotes: 139

Related Questions