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_first('{"-3":"aaxxxaa", "1":"bbcc", "2":"ddxxxee"}' :: jsonb,
'$.keyvalue() ? (@.value like_regex "xxx").value')
| jsonb_path_query_first |
|---|
| "ddxxxee" |
SELECT 1
with cte(val) as (
values ('{"-3":"aaxxxaa", "1":"bbcc", "2":"ddxxxee"}' :: jsonb)
)
select row_number()over(order by (k#>>'{}')::int asc) as custom_order_position, *
from cte
cross join lateral
jsonb_path_query(val,'$.keyvalue() ? (@.value like_regex "xxx").key')
with ordinality as keys(k,jsonpath_result_position)
left join lateral
jsonb_path_query(val,'$.* ? (@ like_regex "xxx")')
with ordinality as vals(v,jsonpath_result_position)
using(jsonpath_result_position)
order by 1 limit 1;
| custom_order_position | jsonpath_result_position | val | k | v |
|---|---|---|---|---|
| 1 | 2 | {"1": "bbcc", "2": "ddxxxee", "-3": "aaxxxaa"} | "-3" | "aaxxxaa" |
SELECT 1