I'm relatively new to postgres and I have a problem manipulating nested JSON arrays.
My JSON structure:
{
"rows":[
{
"columns":[
[
{
"text":"Some text",
"type":"report"
},
{
"type":"feedback"
}
],
[
{
"key":"p0",
"type":"publications",
"title":""
}
]
]
},
{
"columns":[
[
{
"key":"p1",
"type":"publication",
"title":""
}
]
]
}
]
}
As you can see, the json structure is a bit redundant, which makes it even more difficult. My goal is just to add a new title element to each nested column of type="report" (as is already present for the type="publication").
I am currently looping over the arrays and trying to reconstruct the modified json structure again via concatentation, but this is a tedious and annoying process for this simple task.
FOR currentColumn IN SELECT * FROM json_array_elements(extractedRows)
-- more for loops to get to the type="publication" elements
-- set the new title element
SELECT jsonb_set(...) into result;
-- finally reconstruct the json and persist it
END LOOP;
Is there a clever way to do this more efficiently? I've read through the documentation but I have a hard time coming up with a better way.