【译】PG15关于JSON_TABLE关系视图

Andrew Dunstan提交了 SQL_JSON - SQL/JSON 标准剩余部分中最受期待的部分之一，我们的 Postgres Professional 团队自 2017 年初以来一直在为此进行研究。第一个版本我们在2017年的tPGConf.eu上已经进行了发布，Kudos, Andrew帮助我们提交 jsonb 并随时提供帮助！

JSON_TABLE 可以生成 jsonb 的关系视图，因此可以在所有例如连接中的普通关系运算符中使用 JSONB_TABLE 的结果。

JSON_TABLE 有几个参数：

• 要操作的 JSON 值。
• 用于指定零个或多个行的 SQL/JSON 路径表达式。 此行模式路径表达式旨在生成 SQL/JSON 序列，输出表的每一行都有一个 SQL/JSON 项。
• 用于指定输出表模式的 COLUMNS 子句。 COLUMNS 可以嵌套。 COLUMNS 规范中使用的路径表达式分解列上的 SQL/JSON 项。
• JSON_TABLE 可以有指定如何连接嵌套列的 PLAN 子句

我们来看一个 JSON_TABLE 的例子。

这张图说明了下面的 jsonb。

CREATE TABLE house(js) AS SELECT jsonb ' -- two-floors house
{
  "lift": false,
  "floor": [
    {
      "level": 1,
      "apt": [
        {"no": 1, "area": 40, "rooms": 1},
        {"no": 2, "area": 80, "rooms": 3},
        {"no": 3, "area": null, "rooms": 2}
      ]
    },
    {
      "level": 2,
      "apt": [
        {"no": 4, "area": 100, "rooms": 3},
        {"no": 5, "area": 60, "rooms": 2}
      ]
    }
  ]
}

';

复制

现在，让我们创建这个 jsonb 的关系视图。

SELECT
  jt.*
FROM
  house,
  JSON_TABLE(js, '$.floor[*]' COLUMNS (
    level int,
    NESTED PATH '$.apt[*]' COLUMNS (
      no int,
      area float,
      rooms int
    )
  )) jt;

 level | no |  area  | rooms
-------+----+--------+-------
     1 |  1 |     40 |     1
     1 |  2 |     80 |     3
     1 |  3 | (null) |     2
     2 |  4 |    100 |     3
     2 |  5 |     60 |     2
(5 rows)
复制

我们可以使用 JSON_TABLE 的结果来连接表级别：

CREATE TABLE levels ( level, name ) AS
VALUES (1,'first floor'),(2,'second floor');

SELECT
 levels.name,  jt.*
FROM
  house,
  JSON_TABLE(js, '$.floor[*]' COLUMNS (
    level int,
    NESTED PATH '$.apt[*]' COLUMNS (
      no int,
      area float,
      rooms int
    )
  )) jt, levels 
WHERE  jt.level = levels.level;
     name     | level | no |  area  | rooms
--------------+-------+----+--------+-------
 second floor |     2 |  5 |     60 |     2
 second floor |     2 |  4 |    100 |     3
 first floor  |     1 |  3 | (null) |     2
 first floor  |     1 |  2 |     80 |     3
 first floor  |     1 |  1 |     40 |     1
(5 rows)
复制