CODEWITHSUNDEEP
jsonpostgresql
GoWithTheFlow
GoWithTheFlow

Reputation: 342

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

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?

Upvotes: 0

Views: 1991

Answers (2)

Edouard
Edouard

Reputation: 7065

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

For a single json update :

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

For a (very) limited set of json updates :

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

For a larger set of json updates of the same json data, you can create the "aggregate version" of the jsonb_set function :

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], e jsonb, b boolean)
RETURNS jsonb LANGUAGE sql AS $$
SELECT jsonb_set(COALESCE(x,y), p, e, b) ; $$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb, p text[], e jsonb, b boolean)
( STYPE = jsonb, SFUNC = jsonb_set) ;

and then use the new aggregate function jsonb_set_agg while iterating on a query result where the path and val fields could be calculated :

SELECT jsonb_set_agg('{"username": "jsmith","location": "United States","posts": [{"id":"1","title":"Welcome"},{"id":"4","title":"What started it all"}]}' :: jsonb
                    , l.path :: text[]
                    , to_jsonb(l.val)
                    , true)
 FROM (VALUES ('{posts,0,active}', 'true'), ('{posts,1,active}', 'true')) AS l(path, val) -- this list could be the result of a subquery

This query could finally be used in order to update some data :

WITH list AS
(
SELECT id
     , jsonb_set_agg(json :: jsonb
                    , l.path :: text[]
                    , to_jsonb(l.val)
                    , true) AS res
 FROM sample_table_json
CROSS JOIN (VALUES ('{posts,0,active}', 'true'), ('{posts,1,active}', 'true')) AS l(path, val) 
GROUP BY id
)
UPDATE sample_table_json AS t
   SET json = l.res
  FROM list AS l
 WHERE t.id = l.id

see the test result in dbfiddle

Upvotes: 3

Stefanov.sm
Stefanov.sm

Reputation: 13049

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;

Upvotes: 1

Related Questions