This works fine:
jsonb_path_query(p.blah::jsonb, '$[*] ? (@.fruit == "banana") ')
but if I want to compare the JSON "fruit" property to a value from a column, I cannot get Postgres to parse it, like so:
jsonb_path_query(p.blah::jsonb, '$[*] ? (@.fruit == c.fruit) ')
I get hard-to-understand errors like "syntax error, unexpected IDENT_P at end of jsonpath input"
All JSONpath functions also accept an additional JSON parameter that contains key/value pairs that can be referenced inside the JSON path expression:
jsonb_path_query(jsonb_column,
'$[*] ? (@.fruit == $fp)',
jsonb_build_object('fp', c.fruit) )
jsonb_set() and jsonb_insert() being examples.Use format() to build the second parameter, e.g.:
with my_table (fruit, json_col) as (
values
('banana', '[{"fruit": "banana"}, {"fruit": "apple"}]'::jsonb),
('apple', '[{"fruit": "pear"}]'),
('plum', '[{"fruit": "pear"}, {"fruit": "plum"}]')
)
select
fruit,
jsonb_path_query(json_col, format('$[*] ? (@.fruit == "%s")', fruit)::jsonpath)
from my_table;
fruit | jsonb_path_query
--------+---------------------
banana | {"fruit": "banana"}
plum | {"fruit": "plum"}
(2 rows)
