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 level_one_table (id int);
INSERT INTO level_one_table VALUES
(1)
, (2)
;
CREATE TABLE level_two_table (
id int
, fk_level_one_id int
, col text
);
INSERT INTO level_two_table VALUES
(1, 1)
, (2, 1)
;
CREATE TABLE level_three_table (
id int
, fk_level_two_id int
);
INSERT INTO level_three_table VALUES
(1, 1)
, (2, 2)
;
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 2
CREATE TABLE
INSERT 0 2
SELECT json_build_object('id', t1.id /* other t1 cols */
, 'level_two_table', level_two_table) AS result
FROM level_one_table t1
CROSS JOIN LATERAL (
SELECT json_agg(sub) AS level_two_table
FROM (
SELECT t2.id /* other t2 cols */
, json_build_object('id', t3.id /* other t3 cols */) AS level_three_table
FROM level_two_table t2
LEFT JOIN level_three_table t3 ON t3.fk_level_two_id = t2.id
WHERE t2.fk_level_one_id = t1.id
ORDER BY t2.col DESC
LIMIT 5
) sub
) sub;
| result |
|---|
| {"id" : 1, "level_two_table" : [{"id":1,"level_three_table":{"id" : 1}}, {"id":2,"level_three_table":{"id" : 2}}]} |
| {"id" : 2, "level_two_table" : null} |
SELECT 2
-- strip all NULL values
SELECT json_strip_nulls(json_build_object('id', t1.id /* other t1 cols */
, 'level_two_table', level_two_table)) AS result
FROM level_one_table t1
CROSS JOIN LATERAL (
SELECT json_agg(sub) AS level_two_table
FROM (
SELECT t2.id /* other t2 cols */
, json_build_object('id', t3.id /* other t3 cols */) AS level_three_table
FROM level_two_table t2
LEFT JOIN level_three_table t3 ON t3.fk_level_two_id = t2.id
WHERE t2.fk_level_one_id = t1.id
ORDER BY t2.col DESC
LIMIT 5
) sub
) sub;
| result |
|---|
| {"id":1,"level_two_table":[{"id":1,"level_three_table":{"id":1}},{"id":2,"level_three_table":{"id":2}}]} |
| {"id":2} |
SELECT 2
-- strip only level_two_table IS NULL
SELECT CASE WHEN level_two_table IS NULL
THEN json_build_object('id', t1.id)
ELSE json_build_object('id', t1.id
, 'level_two_table', level_two_table)
END AS result
FROM level_one_table t1
CROSS JOIN LATERAL (
SELECT json_agg(sub) AS level_two_table
FROM (
SELECT t2.id /* other t2 cols */
, json_build_object('id', t3.id /* other t3 cols */) AS level_three_table
FROM level_two_table t2
LEFT JOIN level_three_table t3 ON t3.fk_level_two_id = t2.id
WHERE t2.fk_level_one_id = t1.id
ORDER BY t2.col DESC
LIMIT 5
) sub
) sub;
| result |
|---|
| {"id" : 1, "level_two_table" : [{"id":1,"level_three_table":{"id" : 1}}, {"id":2,"level_three_table":{"id" : 2}}]} |
| {"id" : 2} |
SELECT 2