Manipulate Nested JSON in Postgres

Viewed 60

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.

0 Answers
Related