jsonpostgresqljsonpath

Using jsonb_path_query_first with a variable path


When the path is given, it works:

with x as
 (select '{"a": {"b": {"c": "d"}}}'::jsonb data),
 y as
 (select '$.a.b.c'::text path)
select *,
 jsonb_path_query_first(x.data, '$.a.b.c')
from x, y;

but using the path from the Common table expression (CTE), it gives an error:

with x as
 (select '{"a": {"b": {"c": "d"}}}'::jsonb data),
 y as
 (select '$.a.b.c'::text path)
select *,
 jsonb_path_query_first(x.data, y.path)
from x, y;

Error message:

SQL Error [42883]: ERROR: function jsonb_path_query_first(jsonb, text) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

What should I do to make this work?

I need this because I store structurally different jsons and the jsonpaths to fetch data from them.

Postgres 16


Solution

  • The path argument of jsonb_path_query_first and related functions is not a text string, it's a jsonpath value:

    The jsonpath type […] provides a binary representation of the parsed SQL/JSON path expression that specifies the items to be retrieved by the path engine from the JSON data for further processing with the SQL/JSON query functions.

    So make sure that your CTE has the correct column type and it'll work:

    with x as
     (select '{"a": {"b": {"c": "d"}}}'::jsonb data),
     y as
     (select '$.a.b.c'::jsonpath path)
    --                  ^^^^^^^^
    select *,
     jsonb_path_query_first(x.data, y.path)
    from x, y;
    

    (online demo)