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

推荐：用 NSDT设计器 快速搭建可编程3D场景。

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 树被证明是一项具有挑战性但有益的练习。 如果你掌握了基础知识，那么构建复杂查询是相当简单的。

原文链接：
http://www.bimant.com/blog/postgres-json-traverse/