英文:
Filter JSON values at least one key is in a given array and has a given value
问题
I can help with the translation. Here's the translated content:
要向 PostgreSQL 运行查询，其中 JSON 主体中的任何属性匹配属性名称列表中的属性应等于给定值。
输入：names: ["foo", "bar"], value: 'hello' 应生成查询，如 SELECT ... WHERE data.foo = 'hello' OR data.bar = 'hello'。
然而，我想要使用单个数组参数来表示这些名称，而不是根据数组中属性名称的数量手动展开为 $1, $2 等。
我知道如何绑定动态属性名称，就像这样：
query('SELECT "mytable.*" FROM "mytable" WHERE "mytable"."data"->$1 = $2', ['foo', 'hello'])
... 我还知道如何传递一个数组参数，用于提供一组值，使用类似 WHERE data.foo ANY ($1) 的方式。
但是，是否可能发送单个数组参数，表示属性名称列表，其中至少一个属性必须匹配给定值？在 PostgreSQL 中是否有一种函数可以做到类似以下伪查询的操作来说明：query('SELECT * FROM mytable WHERE data-->any_of($1) = $2', [['foo', 'bar'], 'hello'])
英文:
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' ]
答案1
得分: 1
以下是您要翻译的内容：
接近您的示例伪查询。使用以下查询语句：
select * from mytable m
where exists
(
select from json_each_text(m.data)
where key = any(string_to_array($1, ','))
and value = $2
)
使用这些参数（键的列表以字符分隔的字符串形式）：
['foo,bar', 'hello']
英文:
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']
</details>
# 答案2
**得分**: 0
你可以使用[SQL/JSON路径表达式][1]来实现：
```pgsql
SELECT *
FROM tbl t
WHERE data @? '$.keyvalue() ? (@."key" like_regex "foo|bar") ? (@."value" == "hello")';
"属性名称列表"作为正则表达式输入，带有分支进入表达式("foo|bar")。
我发现路径表达式相对难以构造。不过，它们的美妙之处在于：它们可以使用GIN 索引，甚至是具有jsonb_path_ops操作符类的索引，后者更为专业，但也更小更快。应该能够大大加快对大表的选择性查询速度。
CREATE INDEX tbl_data_pathops_idx ON tbl USING gin (data jsonb_path_ops)
相关链接：
英文:
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")';
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论