Postgres 11

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 tab( ID INT, jsdata jsonb )
INSERT INTO tab VALUES
(1,'[{"a": 1, "b": 2, "c":3},{"a": 4, "b": 5, "c":6},{"a": 7, "b": 8, "c":9}]'::jsonb),
(2,'[{"a": 4, "b": 2, "c":3},{"a": 9, "b": 5, "c":6},{"a": 7, "b": 8, "c":9}]'::jsonb)
2 rows affected

SELECT ID, jsonb_agg(elm) AS "Result"
  FROM tab t
 CROSS JOIN jsonb_array_elements(jsdata) AS elm
 WHERE (elm ->> 'a')::int != 4
 GROUP BY ID
 


id
Result


2
[{"a": 9, "b": 5, "c": 6}, {"a": 7, "b": 8, "c": 9}]

1
[{"a": 1, "b": 2, "c": 3}, {"a": 7, "b": 8, "c": 9}]

id	Result
2	[{"a": 9, "b": 5, "c": 6}, {"a": 7, "b": 8, "c": 9}]
1	[{"a": 1, "b": 2, "c": 3}, {"a": 7, "b": 8, "c": 9}]