sql - 在 postgres 中递归提取 JSON 值

问题描述

我有一些 JSON 数据存储在列中。我想解析 json 数据并针对特定键提取所有值。

这是我的示例数据：

    {
  "fragments": [
    {
      "fragments": [
        {
          "fragments": [
            {
              "fragments": [],
              "fragmentName": "D"
            },
            {
              "fragments": [],
              "fragmentName": "E"
            },
            {
              "fragments": [],
              "fragmentName": "F"
            }
          ],
          "fragmentName": "C"
        }
      ],
      "fragmentName": "B"
    }
  ],
  "fragmentName": "A"
}

预期输出：

D, E, F, C, B, A

我想从上面的 JSON 中提取所有 fragmentName 值。

我已经浏览了以下堆栈，但没有发现任何有用的东西： Collect Recursive JSON Keys In Postgres Postgres recursive query with row_to_json

编辑：

这是我在上述堆栈上尝试过的一种方法：

WITH RECURSIVE key_and_value_recursive(key, value) AS (
  SELECT
    t.key,
    t.value
  FROM temp_frg_mapping, json_each(temp_frg_mapping.info::json) AS t
  WHERE id=2

  UNION ALL

  SELECT
    t.key,
    t.value
  FROM key_and_value_recursive,
    json_each(CASE 
      WHEN json_typeof(key_and_value_recursive.value) <> 'object' THEN '{}' :: JSON
      ELSE key_and_value_recursive.value
    END) AS t
    
)
SELECT *
FROM key_and_value_recursive;

输出：

仅获得 0 级嵌套。

标签： sqlarraysjsonpostgresqlrecursive-query

我会使用递归查询，但使用jsonb_array_elements()：

with recursive cte as (
    select id, info ->> 'fragmentName' as val, info -> 'fragments' as info, 1 lvl 
    from mytable 
    where id = 2
    union all
    select c.id, x.info ->> 'fragmentName', x.info -> 'fragments', c.lvl + 1
    from cte c
    cross join lateral jsonb_array_elements(c.info) as x(info)
    where c.info is not null
)
select id, val, lvl
from cte
where val is not null

查询深度优先遍历对象；在每个步骤中，我们取消嵌套 json 数组并检查片段名称是否可用。我们不需要检查返回值的类型：我们只使用标准函数，直到数据耗尽。

DB Fiddle 上的演示

样本数据：

{
    "fragments": [
        {
            "fragments": [
                {
                    "fragments": [
                        {
                            "fragments": [
                            ],
                            "fragmentName": "D"
                        },
                        {
                            "fragments": [
                            ],
                            "fragmentName": "E"
                        },
                        {
                            "fragments": [
                            ],
                            "fragmentName": "F"
                        }
                    ],
                    "fragmentName": "C"
                }
            ],
            "fragmentName": "B"
        }
    ],
    "fragmentName": "A"
}

结果：

编号 | 值 | 等级
-: | :-- | --:
 2 | 一个 | 1
 2 | 乙| 2
 2 | C | 3
 2 | D | 4
 2 | E | 4
 2 | F | 4

sql - 在 postgres 中递归提取 JSON 值

问题描述

解决方案

推荐阅读