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 ?

Answer 1

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.

Answer 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.