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

编程入门行业动态更新时间:2024-10-19 05:21:02

本文介绍了如何在postgres中过滤json的任何键的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有一个表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.

推荐答案

没有简单的方法. 每个文档:

GIN索引可用于有效地搜索键或键/值对出现在大量jsonb文档(基准)中

GIN indexes can be used to efficiently search for keys or key/value pairs occurring within a large number of jsonb documents (datums)

强调粗体.没有所有值的索引. (那些数据类型可能具有不兼容的数据！)如果您不知道所有键的名称，则必须检查每一行中的所有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 ...