1

I have simple table with ajsonb column

CREATE TABLE things (  id SERIAL PRIMARY KEY,  data jsonb);

with data that looks like:

{    "id": 1,        "title": "thing",        "things": [            {                "title": "thing 1",                "moreThings": [                    { "title": "more thing 1" }                ]            }        ]}

So how do I append inside of a deeply nested array likemoreThings?

For single level nested array I could do this and it works:

UPDATE posts SET data = jsonb_set(data, '{things}', data->'things' || '{ "text": "thing" }', true);

But the same doesn't work for deeply nested arrays:

UPDATE posts SET data = jsonb_set(data, '{things}', data->'things'->'moreThings' || '{ "text": "thing" }', true)

How can I append tomoreThings?

Laurenz Albe's user avatar
Laurenz Albe
257k22 gold badges312 silver badges388 bronze badges
askedMar 8, 2019 at 9:20
julian's user avatar

1 Answer1

1

It works just fine:

UPDATE thingsSET data =    jsonb_set(data,              '{things,0,moreThings}',              data->'things'->0->'moreThings' || '{ "text": "thing" }',              TRUE    )WHERE id = 1;

If you have a table that consists only of a primary key and ajsonb attribute and you regularly want to manipulate thisjsonb in the database, you are certainly doing something wrong. Your life will be much easier if you normalize the data some more.

answeredMar 8, 2019 at 9:33
Laurenz Albe's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.