How to update array of JSON data in postgres?

Question

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.

Iain Shelvington · Accepted Answer · 2022-03-12 08:28:48Z

2

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)

answered Mar 12, 2022 at 8:28

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ayush Naithani Over a year ago

Thanks, can i set that to null? I did the same but it doesn’t seem to be working for null?

Iain Shelvington Over a year ago

@AyushNaithani you should be able to pass the string 'null' as the third parameter, what did you try?

Ayush Naithani Over a year ago

It worked now i was writing NULL. Thanks

jian · Accepted Answer · 2022-03-12 11:15:26Z

0

Hopefully the following command also works.

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

answered Mar 12, 2022 at 11:15

jian

5,0231 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to update array of JSON data in postgres?

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related