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

Postgresql – parameter inside a jsonpath expression in postgres

How can I use a parameter within a JSONPath expression in a PostgreSQL SQL query?

I want to query something like this:

SELECT * FROM table WHERE jsondata @@ '$.pathto.array[*].** ? (@.someproperty == ":VALUE").anotherproperty != null';

I tried:

SELECT * FROM table WHERE jsondata @@ '$.pathto.array[*].** ? (@.someproperty == "1").anotherproperty != null';

This query worked, but I can’t use it with a bind parameter ("1").

Tags:

2

Answers


  1. 0

    Use jsonb_path_match(target jsonb, path jsonpath, vars jsonb) and pass your variables in the third parameter, vars, as a jsonb literal or in jsonb_build_object(): demo

    SELECT * FROM your_table WHERE 
jsonb_path_match(
      jsondata
    , '$.pathto.array[*].** ? (@.someproperty == $var).anotherproperty != null'
    , jsonb_build_object('var',your_variable_here) );
    Login or Signup to reply.
  2. 0

    Try using this approach , it will let you use the parameter within jsonpath expression.

    -- Defining a parameterized query with a placeholder for the value
PREPARE my_query (jsonb) AS
SELECT *
FROM table
WHERE jsonb_path_query(
    jsondata,
    '$.pathto.array[*].** ? (@.someproperty == $1).anotherproperty != null',
    'true'
);

-- Executing the prepared query with the actual value for :VALUE
EXECUTE my_query('1'); -- Replace '1' with the actual value you want to use

-- Deallocating the prepared query when done
DEALLOCATE my_query;

    Hope it works 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search