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

Question

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?

Edouard · Accepted Answer · 2021-12-15 11:17:19Z

3

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

edited Dec 15, 2021 at 11:17

answered Dec 15, 2021 at 7:50

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Edouard Over a year ago

Agree with you, thanks for the comment. Answer updated.

Stefanov.sm Over a year ago

This solution is better than mine. The first true must be single-quoted.

Stefanov.sm Over a year ago

But what about {post,1,active}, {post,2,active} and so on ?

GoWithTheFlow Over a year ago

Thank you, that looks good. I changed the first true to to_jsonb(true), but with the change to text it also works, thank you. The only aspect that would be missing is the iteration, since the number of objects varies. If I change the index to a number that doesn't exist, for example, '{post,100,active}', wouldn't that create an object with just that key?

GoWithTheFlow Over a year ago

Ok, don't mind my previous question. It only modifies existing objects, so I can just use {post,1,active}, {post,2,active}. For larger arrays it would be difficult but in my case it works, because there are only up to a handful of objects in the array

|

Stefanov.sm · Accepted Answer · 2021-12-15 08:04:15Z

1

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;

answered Dec 15, 2021 at 8:04

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

2 Comments

GoWithTheFlow Over a year ago

Thank you for your approach to iterate. As you noticed, that makes it quite a bit complicated I think. Your sample would work, but the problem is that the values change for every object

Stefanov.sm Over a year ago

Well, you can replace the select statement with an update one over the actual table and not the t(j) mock-up. If you mean that some values shall be {"active":true} and other - {"active":false} then replace the literal with a proper expression, maybe a scalar subquery. Yet even more complicated though.

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related