jsonpostgresqljsonpath

ordering logic of the returned json items list in the jsonb_path_query_first function?


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')

Solution

  • 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"