As stated in the manual :
jsonb_path_query_first returns the first JSON item returned by the JSON path for the specified JSON value
OK, but in which order are the json items evaluated ? As an example, why does the following SELECT statement return "baz" instead of any other possible value :
SELECT jsonb_path_query_first('{"balance": 7.77, "active":false, "bar": "baz"}'::jsonb, '$.*')
Is there any way to order the list of evaluated json items or the list of resulting values so that to control the first json item returned by jsonb_path_query_first
, like an ORDER BY
clause in a SELECT DISTINCT
sql statement ?
In the following example, I would like to select the first json object whose value matches with the regular expression 'xxx' according to the order ORDERED BY key :: integer ASC
, ie getting the 'aaxxxaa' value as result, whereas the returned value by default is 'ddxxxee' :
select jsonb_path_query_first('{"-3":"aaxxxaa", "1":"bbcc", "2":"ddxxxee"}' :: jsonb, '$.keyvalue() ? (@.value like_regex "xxx").value')
Quoting 8.14.4. jsonb
Indexing:
object keys are compared in their storage order; (...) shorter keys are stored before longer keys
Which is why "bar"
comes first, as the shortest matching key.
To use the alternative order you described, you'd have to extract both the key and the value for each match, as a set, then apply your regular order by
to that. Demo
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" |