Question 114324 in PostgreSQL Question posted in
The official documentation can be found here.

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

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"

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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) )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search