How to append to a nested array in a JSONB column

Question

Accepted answers elsewhere (How to push a JSON object to a nested array in a JSONB column) aren't working in my situation.

I want to append a string to a nested array in a not null JSONB column. If the array doesn't exist, I want to create it (and have my string added). The content of the column before the update happens will be an object {} (ie not an array).

The following just results in a null value in column "data" violates not-null constraint constraints error:

update md_ticker
SET data = jsonb_set(data, '{labels}', data -> 'labels' || '"some string"', true)
where id = 74650534

klin · Accepted Answer · 2020-04-11 13:15:24Z

0

Use case to check whether the labels array exists:

update md_ticker
set data = case 
    when data ? 'labels' then 
        jsonb_set(data, '{labels}', data -> 'labels' || '"some string"', true)
    else
        data || '{"labels": ["some string"]}'
    end
where id = 74650534

answered Apr 11, 2020 at 13:15

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

How to append to a nested array in a JSONB column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related