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 resume (
resume_id UUID PRIMARY KEY,
data JSONB
);
INSERT INTO resume VALUES('7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8a', '{"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]}');
INSERT INTO resume VALUES('7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8b', '{"educations": [{"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]}');
1 rows affected
1 rows affected
CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], z jsonb, b boolean)
RETURNS jsonb LANGUAGE sql IMMUTABLE AS
$$ SELECT jsonb_set(COALESCE(x, y), p, z, b) ; $$ ;
CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb, p text[], z jsonb, b boolean)
( SFUNC = jsonb_set
, STYPE = jsonb
) ;
SELECT jsonb_set_agg(r.data, array['educations', (a.id - 1) :: text, b.key], to_jsonb(array[b.value]), True)
FROM resume AS r
CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
GROUP BY resume_id
| jsonb_set_agg |
|---|
| {"educations": [{"major": ["MAJOR-1"], "minor": ["MINOR-1"]}, {"major": ["MAJOR-2"], "minor": ["MINOR-2"]}]} |
| {"educations": [{"major": ["ANOTHER-MAJOR-1"], "minor": ["ANOTHER-MINOR-1"]}, {"major": ["ANOTHER-MAJOR-2"], "minor": ["ANOTHER-MINOR-2"]}]} |
SELECT *
FROM resume AS r
CROSS JOIN LATERAL jsonb_array_elements(r.data->'educations') WITH ORDINALITY AS a(data, id)
CROSS JOIN LATERAL jsonb_each_text(a.data) AS b
| resume_id | data | data | id | key | value |
|---|---|---|---|---|---|
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8a | {"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]} | {"major": "MAJOR-1", "minor": "MINOR-1"} | 1 | major | MAJOR-1 |
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8a | {"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]} | {"major": "MAJOR-1", "minor": "MINOR-1"} | 1 | minor | MINOR-1 |
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8a | {"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]} | {"major": "MAJOR-2", "minor": "MINOR-2"} | 2 | major | MAJOR-2 |
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8a | {"educations": [{"major": "MAJOR-1", "minor": "MINOR-1"}, {"major": "MAJOR-2", "minor": "MINOR-2"}]} | {"major": "MAJOR-2", "minor": "MINOR-2"} | 2 | minor | MINOR-2 |
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8b | {"educations": [{"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]} | {"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"} | 1 | major | ANOTHER-MAJOR-1 |
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8b | {"educations": [{"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]} | {"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"} | 1 | minor | ANOTHER-MINOR-1 |
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8b | {"educations": [{"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]} | {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"} | 2 | major | ANOTHER-MAJOR-2 |
| 7e29d793-a4ba-4bfb-a93a-c2d34b7a5c8b | {"educations": [{"major": "ANOTHER-MAJOR-1", "minor": "ANOTHER-MINOR-1"}, {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"}]} | {"major": "ANOTHER-MAJOR-2", "minor": "ANOTHER-MINOR-2"} | 2 | minor | ANOTHER-MINOR-2 |