如何在postgres中过滤json的任意键的值

我有一张桌子users with a jsonb字段称为data。我必须检索所有拥有value在那里面data与给定字符串匹配的列。例如：

user1 = data: {"property_a": "a1", "property_b": "b1"}
user2 = data: {"property_a": "a2", "property_b": "b2"}

我想检索任何具有值的用户data匹配'b2'，在这种情况下将是'user2'.

知道如何以优雅的方式做到这一点吗？我可以从中检索所有密钥data所有用户并手动创建查询，但这既不快速也不优雅。

此外，我必须检索匹配的键和值，但首先要做的事情。

没有简单的方法。根据文档：

GIN索引可用于高效搜索对于键或键/值 对出现在大量 jsonb 文档（数据）中

大胆强调我的。总体没有索引values。 （它们可能具有不兼容的数据类型！）如果您不知道所有键的名称，则必须检查每一行中的所有 JSON 值。

If就像您演示的那样，只有两个键（或只是一些众所周知的键），它仍然很简单：

SELECT *
FROM   users
WHERE  data->>'property_a' = 'b2' OR
       data->>'property_b' = 'b2';

可以通过简单的支持表达指数:

CREATE INDEX foo_idx ON users ((data->>'property_a'), (data->>'property_b'))

Or带有 GIN 索引：

SELECT *
FROM   users
WHERE  data @> '{"property_a": "b2"}' OR
       data @> '{"property_b": "b2"}'

CREATE INDEX bar_idx ON users USING gin (data jsonb_path_ops);

如果您不知道所有键名，事情就会变得更加复杂......

你可以使用jsonb_each() or jsonb_each_text()将所有值取消嵌套到一个集合中，然后检查ANY构造：

SELECT *
FROM   users
WHERE  jsonb '"b2"' = ANY (SELECT (jsonb_each(data)).value);

Or

...
WHERE  'b2' = ANY (SELECT (jsonb_each_text(data)).value);

数据库小提琴here

但最后一项没有索引支持。您可以将所有值提取到数组中，并在其上创建表达式索引，然后使用数组运算符在查询中匹配该表达式...

Related:

• 如何使用新 PostgreSQL JSON 数据类型中的字段进行查询？
• 用于在 JSON 数组中查找元素的索引
• PostgreSQL 可以索引数组列吗？