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 test(id serial, doc jsonb);
INSERT INTO test(doc) values ('{"name": "name1", "custom":{ "role": "admin", "valid": true}, "tags": ["tag1", "tag2"]}');
INSERT INTO test(doc) values ('{"name": "name2", "custom":{ "role": "admin", "valid": true}, "tags": ["tag1", "tag2"]}');
INSERT INTO test(doc) values ('{"name": "name3", "custom":{ "role": "user", "valid": true}, "tags": ["tag1", "tag2"]}');
INSERT INTO test(doc) values ('{"custom":{ "role": "app", "valid": "on_tuesdays"}}');
INSERT INTO test(doc) values ('{"name": "name4", "custom":{ "role": "admin", "valid": true}, "tags": ["tag1", "tag2"]}');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
SELECT jsonb_agg(jsonb_build_object('doc', doc - 'tags') ORDER BY id ASC)
FROM test
| jsonb_agg |
|---|
| [{"doc": {"name": "name1", "custom": {"role": "admin", "valid": true}}}, {"doc": {"name": "name2", "custom": {"role": "admin", "valid": true}}}, {"doc": {"name": "name3", "custom": {"role": "user", "valid": true}}}, {"doc": {"custom": {"role": "app", "valid": "on_tuesdays"}}}, {"doc": {"name": "name4", "custom": {"role": "admin", "valid": true}}}] |
SELECT jsonb_agg(jsonb_path_query(doc, '$ ? (exists(@.name) || exists(@.custom))') ORDER BY id ASC)
FROM test
ERROR: aggregate function calls cannot contain set-returning function calls
LINE 1: SELECT jsonb_agg(jsonb_path_query(doc, '$ ? (exists(@.name) ...
^
HINT: You might be able to move the set-returning function into a LATERAL FROM item.