查询使用jsonb_each和横向的Postgres的内部JSONB IDS

问题描述：

想象一个超级简单的表，例如：查询使用jsonb_each和横向的Postgres的内部JSONB IDS

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小提琴，如果它有帮助：http://sqlfiddle.com/#!17/50fb2/9

编辑：

这里是一个例子输出：再次任何洞察到这一

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

谢谢！

请编辑的问题，并添加一个示例输出。 – klin

@klin我刚刚在上面添加了输出示例，谢谢！ – pyramation

答

该查询提取从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;

感谢您的回应！我认为这是正确的，但我必须错过一些非常小的东西，我拿出你的答案，并提出一个SQL小提琴显示提取是正确的，但是，查询返回0行：http://sqlfiddle.com/#!17/ 6f5a3/8 - 介意看看？它可能是一个SQL小提琴中的错误？ – pyramation

根据我的想法，我已经在问题中更改了id = 6的插入数据。请参阅：http://sqlfiddle.com/#!17/39440/1 – klin

谢谢，就是这样！感谢帮助:) – pyramation

答

不知道，但可能是你需要这样的：

SELECT object.id, key, value 
FROM object 
JOIN LATERAL jsonb_each(data->'nodes') 
ON (value->>'id')::int = object.id 
WHERE 
object.id = 6

查询使用jsonb_each和横向的Postgres的内部JSONB IDS

相关推荐