问题描述
我有一个表users
和一个名为data
的jsonb
字段.我必须检索在与给定字符串匹配的data
列中具有 value 的所有用户.例如:
I have a table users
with a jsonb
field called data
. I have to retrieve all the users that have a value in that data
column matching a given string. For example:
user1 = data: {"property_a": "a1", "property_b": "b1"}
user2 = data: {"property_a": "a2", "property_b": "b2"}
我想检索具有与'b2'
匹配的值data
的任何用户,在这种情况下,该用户将为'user2'
.
I want to retrieve any user that has a value data
matching 'b2'
, in this case that will be 'user2'
.
有人知道如何以一种优雅的方式做到这一点吗?我可以从所有用户的data
中检索所有密钥,并手动创建查询,但这既不方便也不美观.
Any idea how to do this in an elegant way? I can retrieve all keys from data
of all users and create a query manually but that will be neither fast nor elegant.
此外,我必须检索匹配的键和值,但首先要先做
In addition, I have to retrieve the key and value matched, but first things first.
推荐答案
没有简单的方法. 每个文档:
强调粗体.没有所有值的索引. (那些数据类型可能具有不兼容的数据!)如果您不知道所有键的名称,则必须检查每一行中的所有JSON值.
Bold emphasis mine. There is no index over all values. (Those can have non-compatible data types!) If you do not know the name(s) of all key(s) you have to inspect all JSON values in every row.
如果像您演示的那样只有两个键(或者只有几个众所周知的键),那还是很容易的:
If there are just two keys like you demonstrate (or just a few well-kown keys), it's still easy enough:
SELECT *
FROM users
WHERE data->>'property_a' = 'b2' OR
data->>'property_b' = 'b2';
可以通过简单的表达式索引来支持:
Can be supported with a simple expression index:
CREATE INDEX foo_idx ON users ((data->>'property_a'), (data->>'property_b'))
或具有GIN索引:
SELECT *
FROM v
WHERE data @> '{"property_a": "b2"}' OR
data @> '{"property_b": "b2"}'
CREATE INDEX bar_idx ON users USING gin (data jsonb_path_ops);
如果您不知道所有的键名,事情就会变得很复杂...
If you don't know all key names, things get complicated ...
相关:
- How do I query using fields inside the new PostgreSQL JSON datatype?
- Index for finding an element in a JSON array
这篇关于如何在postgres中过滤json的任何键的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!