Postgres 14

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 *
from jsonb_path_query('[{"foo":1},{"foo":2}]'::jsonb,'$'::jsonpath)

jsonb_path_query

[{"foo": 1}, {"foo": 2}]

select *
from jsonb_path_query_array('[{"foo":1},{"foo":2}]'::jsonb,'$'::jsonpath)

jsonb_path_query_array

[[{"foo": 1}, {"foo": 2}]]

select *
from jsonb_array_elements(
    jsonb_path_query(
        '[{"foo":1},{"foo":2}]'::jsonb,
        '$'::jsonpath
    )
)
ERROR:  set-returning functions must appear at top level of FROM
LINE 3:     jsonb_path_query(
            ^

select *
from jsonb_path_query(
        '[{"foo":1},{"foo":1}]'::jsonb,
        '$'::jsonpath
    ) as jpq ( jsn )
left join lateral jsonb_array_elements(
        jpq.jsn
    ) as jae ( obj ) on true

jsn
obj

[{"foo": 1}, {"foo": 1}]
{"foo": 1}

[{"foo": 1}, {"foo": 1}]
{"foo": 1}

select *
from jsonb_path_query('[{"foo":1},{"foo":2}]'::jsonb,'$.*'::jsonpath)

jsonb_path_query

1

2

select *
from jsonb_path_query('[{"foo":1},{"foo":2,"bar":3}]'::jsonb,'$.*'::jsonpath) with ordinality

jsonb_path_query
ordinality

1
1

3
2

2
3

select *
from jsonb_path_query('[{"foo":1},{"foo":2,"bar":3,"foo":4}]'::jsonb,'$.*'::jsonpath) with ordinality

jsonb_path_query
ordinality

1
1

3
2

4
3

select *
from json_path_query('[{"foo":1},{"foo":2,"bar":3}]'::json,'$.*'::jsonpath) with ordinality
ERROR:  function json_path_query(json, jsonpath) does not exist
LINE 2: from json_path_query('[{"foo":1},{"foo":2,"bar":3}]'::json,'...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

jsn	obj
[{"foo": 1}, {"foo": 1}]	{"foo": 1}
[{"foo": 1}, {"foo": 1}]	{"foo": 1}