Adding New Element to a Json Array

Question

I want to add a new array element to a3 at below json:

{
    "a1": "e1",
    "a2": {
        "b1": "y1",
        "b2": "y2"
    },
    "a3": [{
            "arr1": "1"
        },
        {
            "arr2": "2"
        }
    ]
}

So I want above json to be like this one:

{
    "a1": "e1",
    "a2": {
        "b1": "y1",
        "b2": "y2"
    },
    "a3": [{
            "arr1": "1"
        },
        {
            "arr2": "2"
        },
        {
            "arr3": "3"
        }
    ]
}

I can add new element with below command. But when it comes to array I couldnt find a way to add new element.

SELECT jsonb_set('{ "a1": "e1", "a2": { "b1": "y1", "b2": "y2" }, "a3": [{ "arr1": "1" }, { "arr2": "2" }] }'::jsonb,
'{a2,b3}', 
'"4"');

What command I sould use to add { "arr3": "3" } to a3?

edit: if arr3 already exists, the command should change its value. Shouldn't add duplicate {"arr3":"3"}.

klin · Accepted Answer · 2019-02-27 13:32:03Z

1

Use the concatenate operator || to the array:

with my_data(json_data) as (
values
    ('{ "a1": "e1", "a2": { "b1": "y1", "b2": "y2" }, "a3": [{ "arr1": "1" }, { "arr2": "2" }] }'::jsonb)
)

select jsonb_set(json_data, '{a3}', json_data->'a3' || '{"arr3": "3"}')
from my_data;

                                             jsonb_set                                             
---------------------------------------------------------------------------------------------------
 {"a1": "e1", "a2": {"b1": "y1", "b2": "y2"}, "a3": [{"arr1": "1"}, {"arr2": "2"}, {"arr3": "3"}]}
(1 row)

Checking whether the key exists in a nested array is a bit complex. You should unnest the array with jsonb_array_elements() to do that:

with my_data(json_data) as (
values
    ('{ "a1": "e1", "a2": { "b1": "y1", "b2": "y2" }, "a3": [{ "arr1": "1" }, { "arr2": "2" }] }'::jsonb)
)

select 
    case when already_exists then json_data
    else jsonb_set(json_data, '{a3}', json_data->'a3' || '{"arr3": "3"}')
    end as json_data
from (
    select json_data, bool_or(value ? 'arr3') as already_exists
    from my_data
    cross join jsonb_array_elements(json_data->'a3')
    group by 1
    ) s

edited Feb 27, 2019 at 13:32

answered Feb 27, 2019 at 12:48

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Yavuz Over a year ago

if { "arr3": "3" } exists, the command adds new { "arr3": "3" }. Is there any way to make it change arr3 value if arr3 key already exists, or do nothing?

Collectives™ on Stack Overflow

Adding New Element to a Json Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related