1

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' ]

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
JHH
  • 8,567
  • 8
  • 47
  • 91

2 Answers2

1

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']
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228