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

Postgresql – Filter JSON values at least one key is in a given array and has a given value

I want to run a query towards PostgreSQL where any attribute in a JSON body matching a list of attribute names should equal a given value.

The input: names: ["foo", "bar"], value: 'hello'
should render a query such as SELECT ... WHERE data.foo = 'hello' OR data.bar = 'hello'.

However, I want to use a single array parameter for the names, not manually expand it to $1, $2 etc depending on the number of attribute names in the array.

I know how to bind a dynamic attribute name like this:

query('SELECT "mytable.*" FROM "mytable" WHERE "mytable"."data"->>$1 = $2', ['foo', 'hello'])

… and I also know how to pass an array parameter which is used to supply a set of values using WHERE data.foo ANY ($1) etc.

But, is it possible to send a single array parameter denoting a list of attribute names where at least one of those attributes must match the given value? Is there a function within PostgreSQL to say something like (pseudo-query to illustrate): query('SELECT * FROM mytable WHERE data-->any_of($1) = $2', [ ['foo', 'bar'], 'hello' ]

Tags:

2

Answers


  1. 0

    Close to your illustrative pseudo-query. Use this one

    select * from mytable m
where exists
(
 select from json_each_text(m.data) 
 where key = any(string_to_array($1, ',')) 
   and value = $2
)

    with these arguments (the keys’ list shaped as a character-separated string)

    ['foo,bar', 'hello']
    Login or Signup to reply.
  2. 0

    You could make it work with a SQL/JSON path expression:

    SELECT *
FROM   tbl t
WHERE  data @? '$.keyvalue() ? (@."key" like_regex "foo|bar") ? (@."value" == "hello")';

    fiddle

    The "list of attribute names" enters as regex expression with branches into the expression ("foo|bar").

    I find path expressions comparatively hard to formulate. The beauty of it, though: they can use a GIN index, even the kind with jsonb_path_ops operator class, which is more specialized, but also smaller and faster. Should make selective queries on big tables much faster.

    CREATE INDEX tbl_data_pathops_idx ON tbl USING gin (data jsonb_path_ops)

    Related:

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

Back To Top
Search