How do you build a jsonpath query referencing a column in Postgres?-CodePudding

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"

CodePudding user response：

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

CodePudding user response：

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)