CODEWITHSUNDEEP
jsonpostgresqljsonbpostgresql-9.5postgresql-json
pgsandstrom
pgsandstrom

Reputation: 14399

Update every value in an array in postgres json

In my postgres database I have json that looks similar to this:

{
    "myArray": [
        {
            "myValue": 1
        },
        {
            "myValue": 2
        },
        {
            "myValue": 3
        }
    ]
}

Now I want to rename myValue to otherValue. I can't be sure about the length of the array! Preferably I would like to use something like set_jsonb with a wildcard as the array index, but that does not seem to be supported. So what is the nicest solution?

Upvotes: 1

Views: 1400

Answers (2)

Micah Danger
Micah Danger

Reputation: 119

Using json functions are definitely the most elegant, but you can get by on using character replacement. Cast the json(b) as text, perform the replace, then change it back to json(b). In this example I included the quotes and colon to help the text replace target the json keys without conflict with values.

CREATE TABLE mytable ( id INT, data JSONB );

INSERT INTO mytable VALUES (1, '{"myArray": [{"myValue": 1},{"myValue": 2},{"myValue": 3}]}');
INSERT INTO mytable VALUES (2, '{"myArray": [{"myValue": 4},{"myValue": 5},{"myValue": 6}]}');

SELECT * FROM mytable;

UPDATE mytable
SET data = REPLACE(data :: TEXT, '"myValue":', '"otherValue":') :: JSONB;

SELECT * FROM mytable;

http://sqlfiddle.com/#!17/1c28a/9/4

Upvotes: 0

klin
klin

Reputation: 121524

You have to decompose a whole jsonb object, modify individual elements and build the object back.

The custom function will be helpful:

create or replace function jsonb_change_keys_in_array(arr jsonb, old_key text, new_key text)
returns jsonb language sql as $$
    select jsonb_agg(case 
        when value->old_key is null then value
        else value- old_key || jsonb_build_object(new_key, value->old_key) 
        end)
    from jsonb_array_elements(arr)
$$;

Use:

with my_table (id, data) as (
values(1, 
'{
    "myArray": [
        {
            "myValue": 1
        },
        {
            "myValue": 2
        },
        {
            "myValue": 3
        }
    ]
}'::jsonb)
)

select 
    id, 
    jsonb_build_object(
        'myArray',
        jsonb_change_keys_in_array(data->'myArray', 'myValue', 'otherValue')
        )
from my_table;

 id |                           jsonb_build_object                           
----+------------------------------------------------------------------------
  1 | {"myArray": [{"otherValue": 1}, {"otherValue": 2}, {"otherValue": 3}]}
(1 row)

Upvotes: 1

Related Questions