CODEWITHSUNDEEP
postgresqljsonb
julian
julian

Reputation: 33

Postgres jsonb nested array append

I have simple table with a jsonb 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 like moreThings?

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 to moreThings?

Upvotes: 1

Views: 1190

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

It works just fine:

UPDATE things
SET 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 a jsonb attribute and you regularly want to manipulate this jsonb in the database, you are certainly doing something wrong. Your life will be much easier if you normalize the data some more.

Upvotes: 1

Related Questions