json - PostgreSQL 中的递归 JSON 生成

Question

我在 PostgreSQL 9.5 服务器中有以下表格:

值得注意的结构是 location 在理论上是无限递归的。我需要从根 location 生成 JSON 消息，递归到所有子位置；每个 location 都有一些属性，一个 inventory 项目数组和一个子 locations 数组。

如何为此创建高性能查询？我正在查看各种 PostgreSQL JSON 函数、LATERAL 关键字、CTE，并且有点困惑。我用非递归查询完成了 JSON 输出，但不确定如何干净地处理递归。

这是一个示例输出:

{
  "id": 1000,
  "name": "By Location",
  "type": "SITE",
  "locations": [
    {
      "id": 1005,
      "name": "Storage A",
      "type": "STOR",
      "locations": [ ...(same schema as parent)... ],
      "inventories": [ ...(see below for schema)... ]
    },
    {
      "id": 1017,
      "name": "Storage B",
      "name": "COLD",
      "locations": [ ...(same schema as parent)... ],
      "inventories": [...(see below for schema)... ]
    }
  ],
  "inventories": [
    {
      "id": 5340,
      "product_id": 9120,
      "name": "Product X",
      "thumb": "https://example.com/api/images/nnnn.jpg",
      "sort_order": 1,
      "par_level": 3.5,
      "created": 1452898800,
      "updated": 1453071600,
      "measures": [
        {"id": 3498, "quantity": 2.25, "created": 1453071600, "updated": 1453071600},
        {"id": 3456, "quantity": 3.25, "created": 1452898800, "updated": 1452898800}
      ]
    }
  ]
}

Answer 1

让我们把它分成几 block 。首先，您将使用嵌套子查询来创建嵌套数组。公用表表达式可能会有所帮助。

其他技巧是 row_to_json 和 json_agg。

第一个陷阱是 row_to_json 需要将表作为参数来返回正确的标签。

select json_agg(locations) from locations

将为每一行返回一个 json 对象。要仅使用某些字段，您需要创建一个类型并对其进行转换，或者使用 CTE 和上述语法。在大多数情况下，我会使用 CTE。

所以你最终会得到这样的东西:

WITH lowlevel1 AS 
( 
       SELECT a, 
          b, 
          c 
       FROM   tab1) ,lowlevel2 AS 
( 
       SELECT b, 
          c, 
          d 
       FROM   tab2) ,midlevel1 AS 
( 
        SELECT          e, 
                f, 
                g, 
                json_agg(lowlevel1) AS lab1, 
                json_agg(lowlevel2) AS lab2 
        FROM            tab3 
        LEFT OUTER JOIN lowlevel1 
        ON              tab3.id = lowlevel1.parent 
        LEFT OUTER JOIN lowlevel2 
        ON              tab3.id = lovlevel2.parent)
SELECT row_to_json(midlevel1) from midlevel1

或在最后一行使用 json_agg(midlevel1)相反 row_to_json(midlevel1)返回一个包含所有行的数组。

CTE 还支持使用 RECURSIVE 进行回归修饰符。但是，这会返回一个包含回归结果的表，而不是嵌套的 JSON 结构。因此，您可能需要明确编码所需的嵌套级别。

如果一个元素不存在，Postgres 将返回 null。例如，没有子位置的列表将返回 "locations":[null] .要用更有意义的结果替换它 case when <> then <> else '[]' end或 if <> then <> else '[]' end可以使用。第一个是“搜索案例”，其中每个测试都是一个 bool 表达式。