Add a new key/value pair into a nested array inside a PostgreSQL JSON column-CodePudding

Using PostgreSQL 13.4 I have a table with a JSON column in a structure like the following sample:

{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1",
      "title":"Welcome",
      "newKey":true <----------- insert new key/value pair here
    },
    {
      "id":"4",
      "title":"What started it all",
      "newKey":true <----------- insert new key/value pair here
    }
  ]
}

For changing keys on the first level, I used a simple query like this

UPDATE
    sample_table_json
SET
    json = json::jsonb || '{"active": true}';

But this doesn't work for nested objects and objects in an array like in the sample. How would I insert a key/value pair into a JSON column with nested objects in an array?

CodePudding user response：

You have to use the jsonb_set function while specifying the right path see the manual :

UPDATE sample_table_json
  SET json = jsonb_set( json::jsonb
                      , '{post,0,active}'
                      , 'true'
                      , true
                      )

CodePudding user response：

It became a bit complicated. Loop through the array, add the new key/value pair to each array element and re-aggregate the array, then rebuild the whole object.

with t(j) as 
(
 values ('{
  "username": "jsmith",
  "location": "United States",
  "posts": [
    {
      "id":"1", "title":"Welcome", "newKey":true
    },
    {
      "id":"4", "title":"What started it all", "newKey":true
    }]
 }'::jsonb)
)
select j || 
 jsonb_build_object
 (
  'posts', 
  (select jsonb_agg(je||'{"active":true}') from jsonb_array_elements(j->'posts') je)
 )
from t;