Postgres 15

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?.

select jsonb_path_query_array(pl,'$.a.b ? (@ == 2)')
from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
where pl @? '$.a.b ? (@ == 2)';

jsonb_path_query_array

[2]

SELECT 1

select jsonb_path_query_array(pl,'lax $.a.b ? (@ == 2)')
from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
where pl @? 'lax $.a.b ? (@ == 2)';

jsonb_path_query_array

[2]

SELECT 1

select jsonb_path_query_array(pl,'strict $.a.b ? (@ == 2)')
from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
where pl @? 'strict $.a.b ? (@ == 2)';

ERROR:  jsonpath member accessor can only be applied to an object

select jsonb_path_query_array(pl,'strict $.a[*].b ? (@ == 2)')
from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl)
where pl @? 'strict $.a[*].b ? (@ == 2)';

jsonb_path_query_array

[2]

SELECT 1

select jsonb_path_query(pl,'$.**.b?(@==2)')
from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl);

jsonb_path_query

2

2

SELECT 2

select jsonb_path_query(pl,'strict $.**.b?(@==2)')
from(values('{"a": [{"b": 2}, {"b": 3}]}'::jsonb))as jb(pl);

jsonb_path_query

2

SELECT 1