Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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)
$$;

select jsonb_insert_into_elements('[{"a":1, "b":2}, {"c":3, "d":4}]', '{"x":0}');

CREATE FUNCTION




jsonb_insert_into_elements


[{"a": 1, "b": 2, "x": 0}, {"c": 3, "d": 4, "x": 0}]



SELECT 1


drop table if exists orders;
create table orders (
    id   serial primary key,
    data jsonb
);

insert into orders(data)
select
'{
  "Number": "555",
  "UserId": "1",
  "Items": [
    {
      "ProductId": "1", 
      "Name": "TV",
      "Price": "300.00"
    }, 
    {
      "ProductId": "2", 
      "Name": "Mechanical Keyboard",
      "Price": "120.00"
    }
  ]
}'
from generate_series(1, 3);

DROP TABLE



CREATE TABLE



INSERT 0 3


update orders
set data = jsonb_set(
    data, 
    '{Items}', 
    jsonb_insert_into_elements(data->'Items', '{"Quantity": "1"}'))
returning *;


id
data


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

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

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



UPDATE 3

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