sql - 在 postgres 中使用 jsonb_each 和 LATERAL 查询 JSONB 中的 id

Question

想象一个 super 简单的表格，例如:

create table object (
    id INT,
    data jsonb
);

一些数据:

INSERT INTO object (id, data) VALUES
  (4, '{"nodes":{"f":{"id":1}}}'), -- C
  (5, '{"nodes":{"d":{"id":2}, "e":{"id":3}}}'), -- B
  (6, '{"nodes":{"b":{"id":4}, "c":{"id":5}}}') -- A
;

我想解构 JSON 并查询子项。

例如，如果我这样做

SELECT * FROM jsonb_each('{"a":{"id":1},"b":{"id":2}}'::JSONB) as obj

我会回来的:

a   {"id":1}
b   {"id":2}

我试图将其结合起来以从嵌套对象中获取 id 属性并查询子对象(运气不好):

SELECT
      jsonb_each(data->'nodes')
      FROM objects as objs
      WHERE id=6
      LATERAL (SELECT * FROM objects as ref WHERE ref.id = objs->'id');

如果有帮助，我提供了一个 SQL fiddle :http://sqlfiddle.com/#!17/50fb2/9

编辑:

这是一个示例输出:

id  data
4   '{"nodes":{"f":{"id":1}}}'
5   '{"nodes":{"d":{"id":2}, "e":{"id":3}}}'

再次感谢您对此的任何见解!

Answer 1

此查询从 jsonb 对象中提取 ids:

select (value->>'id')::int as nested_id
from object,
jsonb_each(data->'nodes')
where id = 6;

 nested_id 
-----------
         4
         5
(2 rows)    

将其用作连接中的派生表:

select o.*
from object o
join (
    select (value->>'id')::int as nested_id
    from object,
    jsonb_each(data->'nodes')
    where id = 6
    ) s
on id = nested_id;

 id |                    data                     
----+---------------------------------------------
  4 | {"nodes": {"f": {"id": 1}}}
  5 | {"nodes": {"d": {"id": 2}, "e": {"id": 3}}}
(2 rows)

或作为带有运算符in 的子查询:

select o.*
from object o
where id in (
    select (value->>'id')::int as nested_id
    from object,
    jsonb_each(data->'nodes')
    where id = 6
    );

但是，这也可以通过横向查询来完成(如您所愿):

select s.*
from object o,
jsonb_each(data->'nodes'),
lateral (select * from object where id = (value->>'id')::int) s
where o.id = 6;