json - postgresql jsonb 包含 key :value on second level

Question

在postgresql中是否有可能在二级查询key:value

例如一行的 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 的评论:

这里的重点是我不知道顶级键，我想说“对于任何对象，是否有一个内部对象具有以下键和以下值”。

Answer 1

您可以使用横向连接 为表中的每一行调用jsonb_each。函数 jsonb_each 将每个节点变成一行，其中包含两列，分别称为 key 和 value:

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

完整示例(还没有对 Postgres 9.4 的 SQL Fiddle 支持):

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"}