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

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

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

INSERT 0 1

INSERT 0 1

INSERT 0 1

main_id
client_id
project_id
test
date
mydata

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

INSERT 0 1

SELECT jsonb_build_object(
            'myData_updated',
            json_agg(elems || jsonb_build_object('product_name', po.name))
        )
        
        FROM mainTable mt CROSS JOIN LATERAL
        jsonb_array_elements(mt.myData) AS heading_elems CROSS JOIN LATERAL
        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
      
jsonb_build_object

{"myData_updated": [{"pid": 1, "stock": 500, "product_name": "Random Product"}]}

SELECT 1