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

Question

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"

Answer 1

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