Postgres 14

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

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