How to update PostgreSQL array of jsonb

Question

I have a table like:

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

where the comments column has the following structure:

[{
        "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": []
    }
]

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

I'm brand new to postgresql and 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?

LE: I got this far:

UPDATE tasks
set comments[1] = jsonb_set(comments[1], '{comments,1, message}', '"test_new"')
where id = 20;

Sure, I can get this path from javascript but it's that a best practice? Not feeling comfortable using indexes from javascript arrays. Should I try to write a sql function to get the array and use the 'uid' as key? Any other simpler way to search/select using the 'uid' ?

LLE

I can't get it to work using suggestion at:this question (which I read and tried) Code bellow returns nothing:

-- get index for level 2
select pos as elem_index
from tasks,
     jsonb_array_elements(comments[0]->'comments') with ordinality arr(elem, pos)
where tasks.id = 20 and
      elem ->>'uid'='subcomment_1';

and I need it for several levels so it's not quite a duplicate.

https://www.postgresql.org/docs/11/functions-json.html, jsonb_set may help you — cske, Aug 30 '19 at 19:33

score 1 · Accepted Answer · answered Aug 30 '19 at 20:54

1

First, you cannot update a part of a column (an element of an array) but only a column as a whole.

Next, you should understand what the path (the second argument of the jsonb_set() function) means.

Last, the third argument of the function is a valid json, so a simple text value must be enclosed in both single and double quotes.

update tasks
set comments = jsonb_set(comments, '{0, comments, 1, message}', '"comment edited"')
where id = 1;

Path:

0 - the first element of the outer array (elements are indexed from 0)
comments - an object with key comments
1 - the second element of the comments array
message - an object message in the above element.

See Db<>fiddle.

answered Aug 30 '19 at 20:54

klin

112,967
15
204
232

I believe I started to understand the concept of path. Your code I already tried and it did not work until I modify it like this: `set comments[1] = jsonb_set(comments[1], '{comments, 1, message}', '"comment edited"')` As my column is array of jsonb (maybe?) so if I use like that, it works. – SharpBCD Aug 30 '19 at 20:59
1

Well, I didn't notice that this is the incomprehensible `jsonb[]`. Please read [jsonb vs jsonb array for multiple addresses for a customer](https://stackoverflow.com/a/46015221/1995738) and [postgres change jsonb array to jsonb](https://stackoverflow.com/a/37209754/1995738). Unfortunately, [the linked answer](https://stackoverflow.com/a/38996799/1995738) is the only answer to the question. The structure is too complex to find a simpler solution, and well, I know that it is not an out-of-the-box remedy and requires further working. Postgres is not Python... – klin Aug 30 '19 at 21:16
I read it. So your suggestion would be to switch from jsonb[] to jsonb? ironically I believed it will be easier to have arrays of json as the subsequent data structure also has a recursive property "comments" as array of json. – SharpBCD Aug 30 '19 at 21:27
1

In the database world, simplicity is a real virtue. Data structures should be reflected in related tables and primitive columns. Of course, json can make life easier here too, but it's not its natural habitat. – klin Aug 30 '19 at 21:35
So, if you need to update a deeply nested json object, it's a sign that you should translate the json structure into the database language (tables and columns). – klin Aug 30 '19 at 21:44
I know what you mean. It was a long debate and I finally choice the json for the flexibility of nesting and not running long queries over a potential very long table. – SharpBCD Aug 30 '19 at 21:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/198744/discussion-between-sharpbcd-and-klin). – SharpBCD Aug 30 '19 at 21:51

How to update PostgreSQL array of jsonb

1 Answers1