How to update array of JSON data in postgres?-CodePudding

create table test 
add column data jsonb;

insert into test values 
( 
    '[{
        "name": "Alexa", 
        "age": "20"
    }, 
    {
        "name": "Siri", 
        "age": "42"
    }]' 
);

Table Data Looks like this:

data <------ column_name

[{"name": "Alexa", "age": "20"}, {"name": "Siri", "age": "42"}]

I am familiar with how to update json data, Here i want to take json data from array and change it. I want to change "name" attribute of first json object "Alexa" to "Cortana", Is it possible to do that is postgres? P.S. This is not the actual data that I have broken down my doubt to simple problem.

CodePudding user response：

You can use the jsonb_set function to return a JSON object with a section replaced with a new value

UPDATE test
SET data = jsonb_set(data, '{0,name}', '"Cortana"', true)

CodePudding user response：

Hopefully the following command also works.

    update test set data = data - 0 
|| jsonb_build_object('name','Cortana', 'age', '20') returning *;