Postgres 12

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 examples(object) AS VALUES
  ('{"a":1, "b":2, "c":3}'::jsonb),
  ('{"b":2, "c":3}'::jsonb),
  ('{"a":1, "b":2}'::jsonb),
  ('{"c":3}'::jsonb),
  ('{"a":1}'::jsonb);
5 rows affected

SELECT object, array(SELECT jsonb_object_keys(object)) <@ array['a', 'b'] FROM examples;


object
?column?


{"a": 1, "b": 2, "c": 3}
f

{"b": 2, "c": 3}
f

{"a": 1, "b": 2}
t

{"c": 3}
f

{"a": 1}
t


SELECT object, (SELECT bool_and(k = ANY(array['a', 'b'])) FROM jsonb_object_keys(object) as k) FROM examples;


object
bool_and


{"a": 1, "b": 2, "c": 3}
f

{"b": 2, "c": 3}
f

{"a": 1, "b": 2}
t

{"c": 3}
f

{"a": 1}
t

object	?column?
{"a": 1, "b": 2, "c": 3}	f
{"b": 2, "c": 3}	f
{"a": 1, "b": 2}	t
{"c": 3}	f
{"a": 1}	t