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

elems
jsonb_build_object

{"pid": 2, "stock": 1000}
{"product_name": "def"}

{"pid": 1, "stock": 500}
{"product_name": "abc"}

SELECT 2

SELECT
   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

?column?

{"pid": 2, "stock": 1000, "product_name": "def"}

{"pid": 1, "stock": 500, "product_name": "abc"}

SELECT 2

SELECT
    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

jsonb_agg

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

SELECT 1

SELECT
    jsonb_build_object(
        '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

jsonb_build_object

{"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}

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