Postgresql – how to update PostgreSQL array of jsonb

jsonpostgresqlpostgresql-11

I have the structure like this (json pretty):

[{
        "uid": "comment_1",
        "message": "level 1 - comment 1",
        "comments": [{
            "uid": "subcomment_1",
            "message": "level 2 - comment 1",
            "comments": []
        }, {
            "uid": "subcomment_2",
            "message": "level 1 - comment 2",
            "comments": []
        }]
    },
    {
        "uid": "P7D1hbRq4",
        "message": "level 1 - comment 2",
        "comments": []
    }
]

which is part of a table named 't1' with the structure:

id: integer,
... other stuff...,
comments: array of jsonb

I need to update a particular field: comments[1](with uid = comment_1) -> comments[2] (with uid = subcomment_2) -> message = 'comment edited'.

I'm brand new to postgresql so I can't figure it out how to do this, not even close.
I manage to merge objects and change message for level 1 with

UPDATE tasks
    set comments[1] = comments[1]::jsonb || $$
      {
        "message": "something",
      }$$::jsonb
where id = 20;

but that's as far as I could go.

Any hints towards the right direction?

Best Answer

You can use jsonb_set to replace a deep-in part of a jsonb structure.

...jsonb_set(thing,'{0,comments,1,message}','"something"');

To put that together with setting a specific element of a PostgreSQL array, it would be:

update tasks set comments[1]=jsonb_set(comments[1],'{0,comments,1,message}','"something"')
     where id=1;

Figuring out that '0' and '1' are the required subscripts to use in the path, if you didn't already know that, is another matter.

You can use jsonpath (to be new in version 12) to extract elements of JSON arrays based on conditionals (rather than just based on the array index), but I don't see a way to either update the extracted value, nor to get it to return to you an path at which that value was found so that you can sub the path into jsonb_set.

Related Solutions

PostgreSQL JSONB update array elements query

This is not a trivial task.

Assuming the two nested JSON arrays reliably exist in every row, this works:

UPDATE forms f
SET    form = u.form1
FROM  (
   SELECT f.id, f.form || jsonb_build_object('widgets', jsonb_agg(w.widget1)) AS form1
   FROM   forms f
   CROSS  JOIN LATERAL (
      SELECT w.widget || p.permissions AS widget1
      FROM   jsonb_array_elements(f.form ->'widgets') w(widget)
      LEFT   JOIN LATERAL (
         SELECT jsonb_build_object('permissions', jsonb_agg(p1.permission)) AS permissions
         FROM  (
            SELECT *
            FROM   jsonb_array_elements(w.widget->'permissions') p(permission)
            WHERE  p.permission <> jsonb '"user1"'       -- delete user1
            UNION
            SELECT jsonb '"user5"'                       -- add user5
            ) p1
         ) p ON true
      ) w
   GROUP  BY 1
   ) u
WHERE  u.id = f.id
AND    u.form1 IS DISTINCT FROM f.form;

But it is complex and expensive. I'd rather not use it. Instead, I would aim to normalize the DB schema if at all possible.

db<>fiddle here - with intermediate steps to help understanding.

There is too much to explain here ...

Most importantly:

It works even with empty permissions arrays because we add at least one user. Else we would get NULL instead of an empty array, and might need to do more.
The manual about the concatenation operator ||:

Note

The || operator concatenates the elements at the top level of each of its operands. It does not operate recursively. For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.
Avoid updating rows where nothing changes with the last line added to the WHERE clause. See:
- How do I (or can I) SELECT DISTINCT on multiple columns?
While you only delete a user and most rows don't have that user to begin with, we could make it much faster with a condition:
```
WHERE  f.form @> '{"widgets": [{"permissions":["user1"]}]}'
```
Checks whether any permissions array has a "user1" element, and can use a matching index. If the table is big you'll want a matching index. See:
- Find rows containing a key in a JSONB array of records
To identify rows where not all permissions arrays have "user5", the check is not as simple, and index support even less. Though still possible.

Postgresql SELECT item from JSONB array – array as top-level json object

You need to unnest the array elements to rows in the from clause:

select i.dt -> 'item1' as item1
from test_table t
  cross join jsonb_array_elements(t.data) as i(dt)

Best Answer

Related Solutions

PostgreSQL JSONB update array elements query

Most importantly:

Postgresql SELECT item from JSONB array – array as top-level json object

Related Question