递归展平postgres中的嵌套jsonb，而没有未知的深度和未知的关键字段 [英] recursively flatten a nested jsonb in postgres without unknown depth and unknown key fields

问题描述

如何在我不知道深度和每个深度的字段的递归方式中将嵌套的jsonb递归展平? (请参见下面的示例)

How can I recursively flatten a nested jsonb in postgres which I don't know the depth and the field at each depth? (see example below)

使用postgressql查询进行展平会很容易

A postgressql query to do the flattening would be much apreciated

    {
       "xx": "",
       "xx": "",
       "form": "xxx",
       "type": "",
       "content_type": "xxx",
       "reported_date": ,
       "contact": {
           "imported_date": "",
           "name": "",
           "phone": "",
           "alternate_phone": "",
           "specialization": "",
           "type": "",
           "reported_date": ,
           "parent": {
               "_id": "xxx",
               "_rev": "xxx",
               "parent": "",
               "type": "xxx" 
               } 
        }
    }

我已经在堆栈溢出中进行了搜索，但是他们只考虑具有单个深度的jsonb，并且之前已经知道密钥了

I have searched in stack overflow but they only consider jsonb's which have a single depth and the keys are already known before

推荐答案

示例设置:

create table my_table(id int, data jsonb);
insert into my_table values
(1,
$${
   "type": "a type",
   "form": "a form",
   "contact": {
       "name": "a name",
       "phone": "123-456-78",
       "type": "contact type",
       "parent": {
           "id": "444",
           "type": "parent type" 
           } 
    }
}$$);

递归查询对在任何级别上找到的每个json对象执行jsonb_each().新的密钥名称包含从根开始的完整路径:

The recursive query executes jsonb_each() for every json object found on any level. New key names contain full path from the root:

with recursive flat (id, key, value) as (
    select id, key, value
    from my_table,
    jsonb_each(data)
union
    select f.id, concat(f.key, '.', j.key), j.value
    from flat f,
    jsonb_each(f.value) j
    where jsonb_typeof(f.value) = 'object'
)
select id, jsonb_pretty(jsonb_object_agg(key, value)) as data
from flat
where jsonb_typeof(value) <> 'object'
group by id;

 id |                   data                   
----+------------------------------------------
  1 | {                                       +
    |     "form": "a form",                   +
    |     "type": "a type",                   +
    |     "contact.name": "a name",           +
    |     "contact.type": "contact type",     +
    |     "contact.phone": "123-456-78",      +
    |     "contact.parent.id": "444",         +
    |     "contact.parent.type": "parent type"+
    | }
(1 row)

如果您想获得此数据的平面视图，则可以使用此答案中所述的函数create_jsonb_flat_view() 平坦的聚合键/来自JSONB字段的值对吗?

If you want to get a flat view of this data you can use the function create_jsonb_flat_view() described in this answer Flatten aggregated key/value pairs from a JSONB field?

您需要使用扁平化的jsonb创建一个表(或视图):

You need to create a table (or view) with flattened jsonb:

create table my_table_flat as 
-- create view my_table_flat as 
with recursive flat (id, key, value) as (
-- etc as above
-- but without jsonb_pretty()

现在您可以在表格上使用该功能了:

Now you can use the function on the table:

select create_jsonb_flat_view('my_table_flat', 'id', 'data');

select * from my_table_flat_view;


 id | contact.name | contact.parent.id | contact.parent.type | contact.phone | contact.type |  form  |  type  
----+--------------+-------------------+---------------------+---------------+--------------+--------+--------
  1 | a name       | 444               | parent type         | 123-456-78    | contact type | a form | a type
(1 row)

该解决方案在Postgres 9.5+中有效，因为它使用了此版本中引入的jsonb函数.如果您的服务器版本较旧，则强烈建议无论如何都要升级Postgres以便有效地使用jsonb.

The solution works in Postgres 9.5+, as it uses jsonb function introduced in this version. If your server version is older it is highly recommended to upgrade Postgres anyway to use jsonb efficiently.

这篇关于递归展平postgres中的嵌套jsonb，而没有未知的深度和未知的关键字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！