PostgreSQL中的JSONB在第二层级包含键值对

Question

PostgreSQL中的JSONB在第二层级包含键值对

3

在postgresql中，是否有可能查询第二层的键值对？

例如，一行的jsonb字段如下所示：

{
   "something": {
      "v_id": "5544d28431f19", 
      "value": "xyz"
   }, 
   "something_else": {
      "v_id": "5544d28431feb", 
      "value": "abc"
   }
}

我想使用v_id的值查询此行，例如：

  SELECT id, jsonb_field
  FROM table_1
  WHERE jsonb_field @> '{{"v_id": "5544d28431feb"}}'
  ;

然而，这个查询是无效的。如何实现这样的查询？

编辑:

根据@CraigRinger的评论:

关键在于我不知道顶层键，我想说“对于任何对象，是否存在一个内部对象具有以下键和以下值”。

- Robin

1

所以这里的重点是你不知道顶层键，你想说“对于任何对象，是否存在一个内部对象具有以下键和以下值”？ - Craig Ringer

@CraigRinger 这正是重点所在。 - Robin

谢谢，我想澄清一下。我认为这个查询可能无法使用jsonb索引；除非你为此查询创建一个表达式索引，否则它很可能只能进行顺序扫描。 - Craig Ringer

@CraigRinger 感谢您的评论。我还没有考虑将 v_id 指定为 jsonb 索引。您是否建议我这样做，为什么在这里不可能呢？ - Robin

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Andomar · Accepted Answer

您可以使用 lateral join 来为表中的每一行调用 jsonb_each。函数 jsonb_each 将每个节点转换为具有两个列名为 key 和 value 的行：

select  value
from    table_1
cross join lateral
        jsonb_each(jsonb_field) sub
where   value @> '{"v_id": "5544d28431feb"}';

完整示例（目前 SQL Fiddle 不支持 Postgres 9.4）：

create table table_1 (id int primary key, jsonb_field jsonb);
insert into table_1 (id, jsonb_field) values (42, '{
   "something": {
      "v_id": "5544d28431f19", 
      "value": "xyz"          
   },                                       
   "something_else": {
      "v_id": "5544d28431feb", 
      "value": "abc"
   }
}');

select  value
from    table_1
cross join lateral
        jsonb_each(jsonb_field) t2
where   value @> '{"v_id": "5544d28431feb"}';

这将打印：

                   value                   
-------------------------------------------
 {"v_id": "5544d28431feb", "value": "abc"}