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?