how to update PostgreSQL array of jsonb

Question

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?

Answer 1

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.