Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products( pid serial PRIMARY KEY, name varchar(64) );
CREATE TABLE clients( client_id serial PRIMARY KEY, name varchar(64) );
CREATE TABLE projects( project_id serial PRIMARY KEY, name varchar(64) );

CREATE TABLE mainTable( main_id serial PRIMARY KEY,client_id integer REFERENCES clients(client_id),project_id integer REFERENCES projects(project_id), test varchar(64), date Date , myData jsonb );



INSERT INTO products(name) VALUES('Random Product');
INSERT INTO clients(name) VALUES('Random Client');
INSERT INTO projects(name) VALUES('Random Project');

INSERT INTO mainTable(client_id,project_id,myData) VALUES(1,1,'[ { "heading": "one", "pItems": [{ "pid" : 1, "stock" : 500 }]  } ]') RETURNING *;



1 rows affected

1 rows affected

1 rows affected



main_id
client_id
project_id
test
date
mydata


1
1
1
null
null
[{"pItems": [{"pid": 1, "stock": 500}], "heading": "one"}]


SELECT test,jsonb_build_object(
            'myData_updated',
            json_agg(elems || jsonb_build_object('product_name', po.name))
        )
        
        FROM mainTable mt,
        jsonb_array_elements(mt.myData) AS heading_elems,
        jsonb_array_elements(heading_elems -> 'pItems') AS elems
        
        JOIN products po ON (elems ->> 'pid' )::int = po.pid
        
       INNER JOIN clients client ON client.client_id = mt.client_id
       INNER JOIN projects project on project.project_id = mt.project_id
      
       
        
ERROR:  invalid reference to FROM-clause entry for table "mt"
LINE 12: ...  INNER JOIN clients client ON client.client_id = mt.client_...
                                                              ^
HINT:  There is an entry for table "mt", but it cannot be referenced from this part of the query.