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, Feb 07 '20 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, Feb 07 '20 at 13:50

score 3 · Answer 1 · answered Feb 07 '20 at 13:31

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 07 '20 at 13:31

klin

112,967
15
204
232

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"` ? – AshwinK Feb 07 '20 at 14:25
1

Use `jsonb_build_object()`, see [Db<>Fiddle.](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=c2affa57bbf200cf13ee395b103b7b8d) – klin Feb 07 '20 at 14:37

Bergi · Accepted Answer · 2020-02-07T14:55:33.647

1

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 07 '20 at 14:55

answered Feb 07 '20 at 14:23

Bergi

630,263
148
957
1,375

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"` ? – AshwinK Feb 07 '20 at 14:26
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](https://stackoverflow.com/a/12505220/1048572). Then just cast that to a string and that string to json(b). – Bergi Feb 07 '20 at 14:31
want them to be distinct values. – AshwinK Feb 07 '20 at 14:43
Yeah, then just replace the `'"1"'` expression by the `to_jsonb(…::text)` expression. – Bergi Feb 07 '20 at 14:46
Also what is `item` in inner `select jsonb_agg` ? As we haven't defined `item` as alias anywhere. Just curious to know. – AshwinK Feb 07 '20 at 14:51
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207430/discussion-between-ashwink-and-bergi). – AshwinK Feb 07 '20 at 17:12

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

2 Answers2

Linked