Postgres 11

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

CREATE TABLE mytable (
    mydata jsonb
);

INSERT INTO mytable VALUES
('{"my_data":[{"pid":1,"stock":500},{"pid":2,"stock":1000}]}');

CREATE TABLE my_other_table (
    pid int,
    product_name text
);

INSERT INTO my_other_table VALUES
(1, 'abc'), (2, 'def');

SELECT * FROM mytable

CREATE TABLE



INSERT 0 1



CREATE TABLE



INSERT 0 2




mydata


{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}



SELECT 1


SELECT
   *
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems


mydata
value


{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 1, "stock": 500}

{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 2, "stock": 1000}



SELECT 2


SELECT
   *
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems
JOIN
    my_other_table mot ON (elems ->> 'pid')::int = mot.pid


mydata
value
pid
product_name


{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 2, "stock": 1000}
2
def

{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 1, "stock": 500}
1
abc



SELECT 2


SELECT
   mydata,
   elems,
   jsonb_build_object('product_name', mot.product_name)
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems
JOIN
    my_other_table mot ON (elems ->> 'pid')::int = mot.pid


mydata
elems
jsonb_build_object


{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 2, "stock": 1000}
{"product_name": "def"}

{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 1, "stock": 500}
{"product_name": "abc"}



SELECT 2


SELECT
   mydata,
   elems || jsonb_build_object('product_name', mot.product_name)
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems
JOIN
    my_other_table mot ON (elems ->> 'pid')::int = mot.pid


mydata
?column?


{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 2, "stock": 1000, "product_name": "def"}

{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
{"pid": 1, "stock": 500, "product_name": "abc"}



SELECT 2


SELECT
   mydata,
   jsonb_agg(
       elems || jsonb_build_object('product_name', mot.product_name)
   )
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems
JOIN
    my_other_table mot ON (elems ->> 'pid')::int = mot.pid
GROUP BY mydata


mydata
jsonb_agg


{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}
[{"pid": 2, "stock": 1000, "product_name": "def"}, {"pid": 1, "stock": 500, "product_name": "abc"}]



SELECT 1


SELECT
   jsonb_set(
       mydata,
       '{my_data}',
       jsonb_agg(
            elems || jsonb_build_object('product_name', mot.product_name)
       )
   )
FROM
    mytable,
    jsonb_array_elements(mydata -> 'my_data') as elems
JOIN
    my_other_table mot ON (elems ->> 'pid')::int = mot.pid
GROUP BY mydata


jsonb_set


{"my_data": [{"pid": 2, "stock": 1000, "product_name": "def"}, {"pid": 1, "stock": 500, "product_name": "abc"}]}



SELECT 1

mydata	value
{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}	{"pid": 1, "stock": 500}
{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}	{"pid": 2, "stock": 1000}

mydata	elems	jsonb_build_object
{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}	{"pid": 2, "stock": 1000}	{"product_name": "def"}
{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}	{"pid": 1, "stock": 500}	{"product_name": "abc"}

mydata	?column?
{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}	{"pid": 2, "stock": 1000, "product_name": "def"}
{"my_data": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 1000}]}	{"pid": 1, "stock": 500, "product_name": "abc"}