NSDT工具推荐： Three.js AI纹理开发包 - YOLO合成数据生成器 - GLTF/GLB在线编辑 - 3D模型格式在线转换 - 可编程3D场景编辑器 - REVIT导出3D模型插件 - 3D模型语义搜索引擎 - Three.js虚拟轴心开发包 - 3D模型在线减面 - STL模型在线切割

我最近遇到了一个有趣的挑战，试图使用 Postgres 来处理 JSON 树。 具体来说，我必须弄清楚如何在 Postgres 表中导航存储为 JSONB 的树结构。 我是这样做的。

1、实验环境搭建

让我们从定义模式开始。 我们将创建一个表，将组织结构图存储为 JSONB 对象：

CREATE TABLE org_charts (
  id    BIGSERIAL PRIMARY KEY,
  chart JSONB
);

接下来，让我们定义 JSON。 它将是员工节点的递归结构，其中包含包含员工直接下属的报告集合。 由于可以有多个级别的报告，我们的组织结构图可以任意深：

{
    "id": 1,
    "name": "Charles Montgomery Burns",
    "title": "Owner",
    "reports": [
        {
            "id": 2,
            "name": "Waylon Smithers, Jr."
        },
        {
            "id": 3,
            "name": "Inanimate carbon rod",
            "reports": [
                {
                    "id": 4,
                    "name": "Homer Simpson",
                    "title": "Safety Engineer"
                }
            ]
        },
        {
            "id": 5,
            "name": "Lenny Leonard"
        },
        {
            "id": 6,
            "name": "Carl Carlson"
        },
        {
            "id": 7,
            "name": "Frank Grimes",
            "status": "deceased"
        }
    ]
}

2、递归查询

我们想编写一个查询来遍历这棵树，例如，打印出所有员工的姓名。 为此，我们需要使用 Postgres 强大的递归公用表表达式 (CTE)。 递归 CTE 听起来很吓人，但实际上写起来相当简单。 这是我为此写的：

WITH RECURSIVE reports (id, json_element) AS (
  -- non recursive term
  SELECT
    id,
    chart
  FROM org_charts
 
  UNION
 
  -- recursive term
  SELECT
    id,
    CASE
    WHEN jsonb_typeof(json_element) = 'array'
      THEN jsonb_array_elements(json_element)
    WHEN jsonb_exists(json_element, 'reports')
      THEN jsonb_array_elements(json_element -> 'reports')
    END AS json_element
  FROM
    reports
  WHERE
    jsonb_typeof(json_element) = 'array' OR jsonb_typeof(json_element) = 'object'
)

让我们仔细分析一下。 查询开始于：

WITH RECURSIVE reports (id, json_element) AS (

在这里，我们声明了一个名为 reports 的递归 CTE，它有两个参数 id 和 json_element。 请注意，参数表示由整个查询以及内部子查询返回的列。

接下来，我们定义非递归项：

-- non recursive term
  SELECT
    id,
    chart
  FROM org_charts

这个查询告诉 Postgres 如何开始评估 CTE。 它首先被评估（并且只有一次），其结果驱动其余的查询。 请注意，此查询必须返回与 CTE 定义的列相匹配的两列（在本例中为 id 和 json_element）。

在非递归项之后，我们需要定义递归项：

-- recursive term
  SELECT
    id,
    CASE
    WHEN jsonb_typeof(json_element) = 'array'
      THEN jsonb_array_elements(json_element)
    WHEN jsonb_exists(json_element, 'reports')
      THEN jsonb_array_elements(json_element -> 'reports')
    END AS json_element
  FROM
    reports
  WHERE
    jsonb_typeof(json_element) = 'array' OR jsonb_typeof(json_element) = 'object'

这里有几点需要注意：

  • 此查询引用 CTE 本身（FROM 报告），这使得它可以递归。
  • 此查询还必须返回代表 id 和 json_element 的两列。
  • 必须使用 UNION 或 UNION ALL 组合非递归项和递归项的结果。

现在，让我们谈谈这个查询在做什么。 本质上，它是将嵌套的 JSON 结构展开成行。 如果遇到 JSON 数组，它会使用 jsonb_array_elements() 函数为每个成员创建一行。 如果它遇到包含报告集合的元素，它也会使用 jsonb_array_elements() 展开该元素。 最后，它只查看 JSON 数组或对象（而不是值或 NULL）。

3、查询结果

那么，我们实际上如何运行这个 CTE？ 通过简单地将报告视为任何其他表：

SELECT * FROM reports;

运行上面的查询结果如下：

{
    "id": 1,
    "name": "Charles Montgomery Burns",
    "title": "Owner",
    "reports": [
        {
            "id": 2,
            "name": "Waylon Smithers, Jr."
        },
        {
            "id": 3,
            "name": "Inanimate carbon rod",
            "reports": [
                {
                    "id": 4,
                    "name": "Homer Simpson",
                    "title": "Safety Engineer"
                }
            ]
        },
        {
            "id": 5,
            "name": "Lenny Leonard"
        },
        {
            "id": 6,
            "name": "Carl Carlson"
        },
        {
            "id": 7,
            "name": "Frank Grimes",
            "status": "deceased"
        }
    ]
}
{
    "id": 2, 
    "name": "Waylon Smithers, Jr."
}
{
    "id": 3, 
    "name": "Inanimate carbon rod", 
    "reports": [
        {
            "id": 4, 
            "name": "Homer Simpson", 
            "title": "Safety Engineer"
        }
    ]
}
{
    "id": 5, 
    "name": "Lenny Leonard"
}
{
    "id": 6, 
    "name": "Carl Carlson"
}
{
    "id": 7, 
    "name": "Frank Grimes", 
    "status": "deceased"
}
NULL
{    
    "id": 4, 
    "name": "Homer Simpson", 
    "title": "Safety Engineer"
}

正如您在这里看到的，我们的查询为每个员工创建了一行加上一个 NULL。 老实说，我不确定为什么会返回 NULL 行（我尝试过但未能摆脱它）。 也就是说，很容易过滤掉 NULL 行。

要获取名称列表（这是我们最初的目标），我们可以这样做：

SELECT json_element -> 'name' AS employee_name
FROM reports
WHERE jsonb_exists(json_element, 'name');

这里我们只查看那些包含元素名称的行，然后返回名称的值：

employee_name
“Charles Montgomery Burns”
“Waylon Smithers, Jr.”
“Inanimate carbon rod”
“Lenny Leonard”
“Carl Carlson”
“Frank Grimes”
“Homer Simpson”

总的来说，使用 Postgres 递归 CTE 来导航 JSON 树被证明是一项具有挑战性但有益的练习。 如果你掌握了基础知识，那么构建复杂查询是相当简单的。

原文链接：HOW TO NAVIGATE JSON TREES IN POSTGRES USING RECURSIVE CTES

BimAnt翻译整理，转载请标明出处