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
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;