postgresql: How to update JSONB to add new key in nested array

Question

How to update the JSONB to add new key into nested array (for all items of array) for all the records.

I'm referring to the link The table structure is:

CREATE TABLE orders (
    id   serial PRIMARY KEY,
    data jsonb
);

The given json is:

{
  "Number": "555",
  "UserId": "1",
  "Items": [
    {
      "ProductId": "1", 
      "Name": "TV",
      "Price": "300.00"
    }, 
    {
      "ProductId": "2", 
      "Name": "Mechanical Keyboard",
      "Price": "120.00"
    }
  ]
}

To add new element into each array item the following query is given:

UPDATE orders
SET data = jsonb_set(
    data, 
    '{Items}',      -- the array in which we operate
    to_jsonb(
    (WITH ar AS(
      WITH temp AS(
        SELECT data->'Items' AS items   -- the array in which we operate
        FROM orders
        WHERE id = 1    -- the filtered order we are updating
      )
      SELECT jsonb_set(
        jsonb_array_elements(items),
        '{Quantity}',   -- the new field we are adding
        '"1"',          -- the value of the new field
        true)
      FROM temp)
     SELECT (array_agg(ar.jsonb_set))
     FROM ar)),
  false)
WHERE id = 1;

Output after executing above query:

{
  "Number": "555",
  "UserId": "1",
  "Items": [
    {
      "ProductId": "1", 
      "Name": "TV",
      "Price": "300.00",
      "Quantity": "1"
    }, 
    {
      "ProductId": "2", 
      "Name": "Mechanical Keyboard",
      "Price": "120.00",
      "Quantity": "1"
    }
  ]
}

But above will update the json only where id=1. What changes are required to update JSON same as above for all rows in orders ?

Use a WITH clause to convert the JSON into a table, and use UPDATE ... FROM to perform a join with that table. — Laurenz Albe
– Laurenz Albe, Commented Feb 7, 2020 at 11:36
I am too lazy to write it out, but the CTE I had in mind would not be in a subselect, but at the very top, and it would use jsonb_populate_recordset or similar to builld a table. — Laurenz Albe
– Laurenz Albe, Commented Feb 7, 2020 at 13:50

klin · Accepted Answer · 2020-02-07 13:31:12Z

3

A general tip, if you have to modify nested JSON elements it is a serious sign that the data model could have been designed better. But if you have no choice, use an auxiliary function. It makes things much simpler and the code more readable and debuggable.

create or replace function jsonb_insert_into_elements(jsonb, jsonb)
returns jsonb language sql immutable as $$
    select jsonb_agg(value || $2)
    from jsonb_array_elements($1)
$$;

Now the update is really simple and elegant:

update orders
set data = jsonb_set(
    data, 
    '{Items}', 
    jsonb_insert_into_elements(data->'Items', '{"Quantity": "1"}'))
where id = 1 -- just skip this if you want to update all rows

Db<>Fiddle.

answered Feb 7, 2020 at 13:31

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

AshwinK Over a year ago

Thank you. This worked as expected. Just one more doubt I've in the value of quantity '{"Quantity": "1"}' how can I add the output of SELECT md5(random()::text || clock_timestamp()::text)::uuid; instead of "1" ?

klin Over a year ago

Use jsonb_build_object(), see Db<>Fiddle.

Bergi · Accepted Answer · 2020-02-07 14:55:33Z

2

You don't need to do that SELECT data->'Items' AS items FROM orders WHERE id = 1 CTE inside the SET statement - you can just refer to data->'Items' directly and it will take the currently updated row, just like you already do in data = jsonb_set(data, …). So you can simplify to

UPDATE orders
SET data = jsonb_set(
    data, 
    '{Items}',      -- the array in which we operate
    (SELECT jsonb_agg(jsonb_set(
        item,
        '{Quantity}',   -- the new field we are adding
        '"1"',          -- the value of the new field
        true))
     FROM jsonb_array_elements(data->'Items')) AS item, -- the array in which we operate
  false)
WHERE id = 1;

(I also got rid of the other CTE and replaced to_jsonb(array_agg(…)) with jsonb_agg)

Now all you need to do for updating all rows is omitting the WHERE clause.

edited Feb 7, 2020 at 14:55

answered Feb 7, 2020 at 14:23

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

6 Comments

AshwinK Over a year ago

Thanks for the cleaner approach. I've one more doubt: in the value of quantity '"1"' how can I add the output of SELECT md5(random()::text || clock_timestamp()::text)::uuid; instead of "1" ?

Bergi Over a year ago

Do you want the same value in each array element in all rows, or want them to have distinct values? Also I recommend using the proper generate_uuid function from the uuid extension. Then just cast that to a string and that string to json(b).

AshwinK Over a year ago

want them to be distinct values.

Bergi Over a year ago

Yeah, then just replace the '"1"' expression by the to_jsonb(…::text) expression.

AshwinK Over a year ago

Also what is item in inner select jsonb_agg ? As we haven't defined item as alias anywhere. Just curious to know.

|

Collectives™ on Stack Overflow

postgresql: How to update JSONB to add new key in nested array

2 Answers 2

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related