Postgres将jsonb值作为jsonb数组返回？

Question

tl;dr --有什么方法从postgres中的jsonb对象获取作为jsonb_array的值吗？

试图在postgres中使用递归的cte来扁平任意深的树结构，如下所示：

{
  "type": "foo",
  "properties": {...},
  "children": [
    "type": "bar",
    "properties": {...},
    "children": [
      "type": "multivariate",
      "variants": {
        "arbitrary-name": {
          properties: {...},
          children: [...],
        },
        "some-other-name": {
          properties: {...},
          children: [...],
        },
        "another": {
          properties: {...},
          children: [...],
        }
      }
    ]
  ]
}

通常遵循这个职位，但是我不得不处理type: "multivariate"节点，我真正想要的基本上是jsonb_agg(jsonb_object_values(json_object -> 'variants'))

更新：

对不起，我显然应该包括我尝试过的查询：

WITH RECURSIVE tree_nodes (id, json_element) AS (
  -- non recursive term
  SELECT
    id, node
  FROM trees

  UNION

  -- recursive term
  SELECT
    id,
    CASE
    WHEN jsonb_typeof(json_element) = 'array'
      THEN jsonb_array_elements(json_element)
    WHEN jsonb_exists(json_element, 'children')
      THEN jsonb_array_elements(json_element -> 'children')
    WHEN jsonb_exists(json_element, 'variants')
      THEN (select jsonb_agg(element.value) from jsonb_each(json_element -> 'variants') as element)
    END AS json_element
  FROM
    tree_nodes
  WHERE
    jsonb_typeof(json_element) = 'array' OR jsonb_typeof(json_element) = 'object'
)

select * from tree_nodes;

这个模式只是一个id &一个jsonb node列。

此查询提供一个错误：

ERROR:  set-returning functions are not allowed in CASE
LINE 16:       THEN jsonb_array_elements(json_element -> 'children')
                    ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

我只想要Object.values(json_element -> 'variants')

更新2：

在再次阅读了这些之后，我意识到这是一个问题，因为我使用了最近版本的PostgreSQL (10.3)，该版本显然不再允许从CASE语句返回集合，这是使这种树平坦的方法工作的关键。在PostgreSQL的最新版本中，可能有一些方法可以实现同样的目标，但我不知道该如何去做。

Answer 1

例如，在FROM子句中使用FROM，在SELECT中使用jsonb_agg(<jsonb_each_alias>.value)：

select
    id,
    jsonb_agg(child.value)
from
    (values
      (101, '{"child":{"a":1,"b":2}}'::jsonb),
      (102, '{"child":{"c":3,"d":4,"e":5}}'::jsonb
    )) as t(id, json_object), -- example table, replace values block with actual tablespec
    jsonb_each(t.json_object->'child') as child
group by t.id

如果需要在jsonb之前迭代更高级别的数组，则始终可以链接返回FROM中的setof jsonb (例如jsonb_array_elements)的其他jsonb_each函数；例如：

select
    id,
    jsonb_agg(sets.value)
from
    (values
      (101, '{"children":[{"a_key":{"a":1}},{"a_key":{"b":2}}]}'::jsonb),
      (102, '{"children":[{"a_key":{"c":3,"d":4,"e":5}},{"a_key":{"f":6}}]}'::jsonb
    )) as t(id, json_object), -- example table, replace values block with actual tablespec
    jsonb_array_elements(t.json_object->'children') elem,
    jsonb_each(elem->'a_key') as sets
group by t.id;

更新答案

在回答您的评论和问题时，编辑有关需要遍历每个树节点的'children'并提取'variants'；我将通过将CTE划分为多个阶段来实现这一点：

with recursive
  -- Constant table for demonstration purposes only; remove this and replace below references to "objects" with table name
  objects(id, object) as (values
    (101, '{"children":[{"children":[{"variants":{"aa":11}},{"variants":{"ab":12}}],"variants":{"a":1}},{"variants":{"b":2}}]}'::jsonb),
    (102, '{"children":[{"children":[{"variants":{"cc":33,"cd":34,"ce":35}},{"variants":{"f":36}}],"variants":{"c":3,"d":4,"e":5}},{"variants":{"f":6}}]}'::jsonb)
  ),
  tree_nodes as ( -- Flatten the tree by walking all 'children' and creating a separate record for each root
    -- non-recursive term: get root element
    select
      o.id, o.object as value
    from
      objects o
    union all
    -- recursive term - get JSON object node for each child
    select
      n.id,
      e.value
    from
      tree_nodes n,
      jsonb_array_elements(n.value->'children') e
    where
      jsonb_typeof(n.value->'children') = 'array'
  ),
  variants as (
    select
      n.id,
      v.value
    from
      tree_nodes n,
      jsonb_each(n.value->'variants') v -- expand variants
    where
      jsonb_typeof(n.value->'variants') = 'object'
  )
select
  id,
  jsonb_agg(value)
from
  variants
group by
  id
;

这种将查询分解成“管道”操作的能力是CTE中我最喜欢的事情之一--它使查询更易于理解、维护和调试。

Answer 2

db<>fiddle

使用更多子元素和更深层次的结构(更多嵌套元素)扩展测试数据：

{
    "type": "foo", 
    "children": [
        {
            "type" : "bar1", 
            "children" : [{
                "type" : "blubb",
                "children" : [{
                    "type" : "multivariate",
                    "variants" : {
                        "blubb_variant1": {
                            "properties" : {
                                "blubb_v1_a" : 100
                            },
                            "children" : ["z", "y"]
                        },
                        "blubb_variant2": {
                            "properties" : {
                                "blubb_v2_a" : 300,
                                "blubb_v2_b" : 4200
                            },
                            "children" : []
                        }
                    }
                }]
            }]
        },
        {
            "type" : "bar2", 
            "children" : [{
                "type" : "multivariate",
                "variants" : {
                    "multivariate_variant1": {
                        "properties" : {
                            "multivariate_v1_a" : 1,
                            "multivariate_v1_b" : 2
                        },
                        "children" : [1,2,3]
                    },
                    "multivariate_variant2": {
                        "properties" : {
                            "multivariate_v2_a" : 3,
                            "multivariate_v2_b" : 42,
                            "multivariate_v2_d" : "fgh"
                        },
                        "children" : [4,5,6]
                    },
                    "multivariate_variant3": {
                        "properties" : {
                            "multivariate_v3_a" : "abc",
                            "multivariate_v3_b" : "def"
                        },
                        "children" : [7,8,9]
                    }
                }
            },
            {
                "type" : "blah",
                "variants" : {
                    "blah_variant1": {
                        "properties" : {
                            "blah_v1_a" : 1,
                            "blah_v1_b" : 2
                        },
                        "children" : [{
                            "type" : "blah_sub1",
                            "variants" : {
                                "blah_sub1_variant1" : {
                                    "properties" : {
                                        "blah_s1_v1_a" : 12345,
                                        "children" : ["a",1, "bn"]
                                    }
                                }
                            }
                        }]
                    },
                    "blah_variant2": {
                        "properties" : {
                            "blah_v2_a" : 3,
                            "blah_v2_b" : 42,
                            "blah_v2_c" : "fgh"
                        },
                        "children" : [4,5,6]
                    }
                }
            }]
        }
    ]
}

结果：

variants                 json                                                                                                                                                                                            
-----------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
"multivariate_variant1"  {"children": [1, 2, 3], "properties": {"multivariate_v1_a": 1, "multivariate_v1_b": 2}}                                                                                                         
"multivariate_variant2"  {"children": [4, 5, 6], "properties": {"multivariate_v2_a": 3, "multivariate_v2_b": 42, "multivariate_v2_d": "fgh"}}                                                                            
"multivariate_variant3"  {"children": [7, 8, 9], "properties": {"multivariate_v3_a": "abc", "multivariate_v3_b": "def"}}                                                                                                 
"blah_variant1"          {"children": [{"type": "blah_sub1", "variants": {"blah_sub1_variant1": {"properties": {"children": ["a", 1, "bn"], "blah_s1_v1_a": 12345}}}}], "properties": {"blah_v1_a": 1, "blah_v1_b": 2}}  
"blah_variant2"          {"children": [4, 5, 6], "properties": {"blah_v2_a": 3, "blah_v2_b": 42, "blah_v2_c": "fgh"}}                                                                                                    
"blubb_variant1"         {"children": ["z", "y"], "properties": {"blubb_v1_a": 100}}                                                                                                                                     
"blubb_variant2"         {"children": [], "properties": {"blubb_v2_a": 300, "blubb_v2_b": 4200}}                                                                                                                         
"blah_sub1_variant1"     {"properties": {"children": ["a", 1, "bn"], "blah_s1_v1_a": 12345}}   

查询：

WITH RECURSIVE json_cte(variants, json) AS (
    SELECT NULL::jsonb, json FROM (
        SELECT '{/*FOR TEST DATA SEE ABOVE*/}'::jsonb as json
    )s
    
    UNION
    
    SELECT  
        row_to_json(v)::jsonb -> 'key',                                -- D        
        CASE WHEN v IS NOT NULL THEN row_to_json(v)::jsonb -> 'value' ELSE c END  -- C
    FROM json_cte
         LEFT JOIN LATERAL jsonb_array_elements(json -> 'children') as c ON TRUE  -- A
         LEFT JOIN LATERAL jsonb_each(json -> 'variants') as v ON TRUE -- B
)
SELECT * FROM json_cte WHERE variants IS NOT NULL

WITH RECURSIVE结构以递归方式检查元素。第一个UNION部件是起点。第二部分是递归部分，为下一步进行最后一次计算。

答:如果在当前的JSON中存在一个children元素，那么每个子元素都会展开成一行

B:如果当前的JSON有一个元素variants，那么所有的元素都将被展开成一个记录。注意，在示例中，一个JSON元素可以包含variants或children元素。

C:如果有一个变体元素，那么扩展的记录将被转换回一个json。得到的结构是{"key" : "name_of_variant", "value" : "json_of_variant"}。value将是下一次递归的JSON ( variants的JSON可以有自己的children元素)。这就是它起作用的原因)。否则，展开的children元素将成为下一个数据

D:如果有variants元素，则打印key