0

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?

  • You say it is an array of jsonb, and your query shows you updating one element of a jsonb[], but the value you show is of single jsonb object containing an array at the top level. which is different. – jjanes Aug 31 at 16:43
0

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.

  • Indeed. To get the path it's a question of knowing the index. So I wrote this code that I may place in a function: select elem, pos - 1 as elem_index from test, jsonb_array_elements(test.comments->0->'comments'->0->'comments') with ordinality arr(elem, pos) where test.id = 2 and elem ->> 'uid' = '221'; And will return the index for the next level, searching for 'uid'. That part I can json_set with || operator to merge it with the new object. Am I right? – SharpBCD Sep 2 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.