'How to update array of JSON data in postgres?

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.

sqljsonpostgresql


Solution 1:[1]

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)

Solution 2:[2]

Hopefully the following command also works.

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Iain Shelvington
Solution 2 Mark

Related Questions