By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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 },{"pid": 2, "stock" : 600 }] }, { "heading": "two", "pItems": [{ "pid" : 1, "stock" : 500 },{"pid": 2, "stock" : 600 }] } ]') 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}, {"pid": 2, "stock": 600}], "heading": "one"}, {"pItems": [{"pid": 1, "stock": 500}, {"pid": 2, "stock": 600}], "heading": "two"}] |
INSERT 0 1
SELECT test,
jsonb_build_object (
'myData_updated',jsonb_agg(
jsonb_build_object(
'heading', heading_elems -> 'heading',
'subheading', heading_elems -> 'subheading',
'pItems', 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
GROUP BY test
ERROR: aggregate function calls cannot be nested LINE 8: 'pItems', json_agg(elems || jsonb_build_object('p... ^