递归JSONB Postgres [英] Recursive JSONB postgres

问题描述

我试图在Postgres中构建一个支持数组和对象的递归CTE，以返回键值对的列表，但似乎找不到很好的例子.这是我当前的代码.

I am trying to build a recursive CTE in Postgres that supports both arrays and objects, to return a list of key-value pairs and don't seem to be able to find a good example. This is my current code.

with recursive jsonRecurse as
(
select
j.key as Path
,j.key
,j.value
from jsonb_each(to_jsonb('{
    "key1": {
        "key2": [
            {
                "key3": "test3",
                "key4": "test4"
            }
        ]
    },
    "key5": [
        {
            "key6":
            [
                {
                    "key7": "test7"
                }
            ]
        }
    ]
}'::jsonb)) j

union all

select
jr.path || '.' || jr2.Key
,jr2.key
,jr2.value
from jsonRecurse jr
       left join lateral jsonb_each(jr.value) jr2 on true
where jsonb_typeof(jr.value) = 'object'
)

select
*
from jsonRecurse;

您可以看到，只要我击中一个数组而不是一个对象，代码就会立即停止递归.我曾尝试过使用case语句，然后将函数调用放到case语句中的jsonb_each或jsonb_array_element上，但是我收到一条错误消息，告诉我使用横向连接.

As you can see the code stops recursing as soon as I hit an array instead of an object. I've tried playing around with using a case statement and putting the function call to jsonb_each or jsonb_array_element in the case statement instead but I get an error telling me to use lateral joins instead.

推荐答案

我已使用此示例表使查询更具可读性:

I have used this example table to make the query more readable:

create table my_table(id serial primary key, jdata jsonb);
insert into my_table (jdata) values
('{
    "key1": {
        "key2": [
            {
                "key3": "test3",
                "key4": "test4"
            }
        ]
    },
    "key5": [
        {
            "key6":
            [
                {
                    "key7": "test7"
                }
            ]
        }
    ]
}');

您必须有条件地加入jsonb_each(value)和jsonb_array_elements(value)，具体取决于value的类型:

You have to join both jsonb_each(value) and jsonb_array_elements(value) conditionally, depending on the type of value:

with recursive extract_all as
(
    select 
        key as path, 
        value
    from my_table
    cross join lateral jsonb_each(jdata)
union all
    select
        path || '.' || coalesce(obj_key, (arr_key- 1)::text),
        coalesce(obj_value, arr_value)
    from extract_all
    left join lateral 
        jsonb_each(case jsonb_typeof(value) when 'object' then value end) 
        as o(obj_key, obj_value) 
        on jsonb_typeof(value) = 'object'
    left join lateral 
        jsonb_array_elements(case jsonb_typeof(value) when 'array' then value end) 
        with ordinality as a(arr_value, arr_key)
        on jsonb_typeof(value) = 'array'
    where obj_key is not null or arr_key is not null
)
select *
from extract_all;

输出:

        path        |                     value                      
--------------------+------------------------------------------------
 key1               | {"key2": [{"key3": "test3", "key4": "test4"}]}
 key5               | [{"key6": [{"key7": "test7"}]}]
 key1.key2          | [{"key3": "test3", "key4": "test4"}]
 key5.0             | {"key6": [{"key7": "test7"}]}
 key1.key2.0        | {"key3": "test3", "key4": "test4"}
 key5.0.key6        | [{"key7": "test7"}]
 key1.key2.0.key3   | "test3"
 key1.key2.0.key4   | "test4"
 key5.0.key6.0      | {"key7": "test7"}
 key5.0.key6.0.key7 | "test7"
(10 rows)

json数组的元素没有键，我们应该使用它们的索引来构建路径.因此，函数jsonb_array_elements()应该以常规方式调用.根据文档(请参阅 7.2. 1.4.表函数):

Elements of json arrays have no keys, we should use their indexes to build a path. Therefore the function jsonb_array_elements() should be called with ordinality. Per the documentation (see 7.2.1.4. Table Functions):

如果指定了WITH ORDINALITY子句​​，则将bigint类型的附加列添加到函数结果列中.该列为函数结果集的行编号，从1开始.

If the WITH ORDINALITY clause is specified, an additional column of type bigint will be added to the function result columns. This column numbers the rows of the function result set, starting from 1.

函数调用

jsonb_array_elements(case jsonb_typeof(value) when 'array' then value end) 
with ordinality as a(arr_value, arr_key)

返回别名为(arr_value, arr_key)的对(value, ordinality).

这篇关于递归JSONB Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！