logo标签列表

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

postgresqlrecursion
3

简而言之，是否有一种方法可以从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中可能有一些达成相同目的的方法，但我不知道如何去做。

如果可以的话，请发布一些模式和您尝试过的查询。 - Linas Valiukas
你想要将“variants”的三个子元素放入一个数组中吗？还是想要将“property”/“children”成对放入一个数组中？ - S-Man
天啊，现在晚饭过后我再看它，我意识到问题不是 变量 引起的，而是 children 数组。我列出的文章中的示例在 Postgres 10 中根本不可能实现吗？ - steve
@ŁukaszKamiński 我明白提示可能会有帮助，但是SQL不是我的专业领域，我不知道如何将提示应用到我的数据集中。 - steve
@steve 我在下面的答案中添加了一个CTE查询，演示了如何使用多阶段的CTE“管道”来遍历“子代”的树；然后获取每个“变体”的值。将查询拆分为链接的CTE查询比包含setof jsonb函数的CASE语句更清晰、更易于维护，这是我个人的看法。 - Timshel
显示剩余6条评论
2个回答
1

例如，在FROM子句中使用jsonb_each()，并在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

您可以始终将返回 setof jsonb 的其他 jsonb 函数链接在一起（例如 jsonb_array_elements ），如果需要在 jsonb_each 之前迭代更高级别的数组，则可以在 FROM 中链式使用它们; 例如：
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的原因之一 - 它让查询更易于理解、维护和调试。
这真的很酷！有点让我觉得惊艳...我没意识到你可以在FROM子句中像这样链接 jsonb 函数。然而，我不太清楚如何将其应用于我的问题。我的 JSON 对象中有一个名为 children 的数组或以 variants 哈希中命名的 children。我想我想要做的是（伪代码）SELECT id, (json.children || json.variants.values)。 - steve
1

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部分是起点。第二个部分是递归部分，其中最后的计算结果用于下一步。

A：如果当前的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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接