PostgreSQL如何解析嵌套JSON生成含squad、name等字段的结构化表格

问题原因 #

你之前的查询返回NULL且只有单行的原因是：info ->'members'取到的是完整的成员JSON数组，数组类型本身没有name顶层键，所以无法取出值；同时没有对数组做展开操作，所以只会返回原表的一行数据。

PostgreSQL中对应SQL Server CROSS APPLY OPENJSON的等价语法是CROSS JOIN LATERAL + JSON数组展开函数，具体实现如下：

实现每个英雄一行（powers保留数组格式） #

SELECT
  info ->> 'squadName' AS squad,
  mem ->> 'name' AS name,
  (mem ->> 'age')::INTEGER AS age,
  mem -> 'powers' AS powers
FROM heroes
CROSS JOIN LATERAL json_array_elements(info -> 'members') AS mem;

语法说明：

• CROSS JOIN LATERAL 可以对左侧表每行返回的子查询/函数结果做关联，功能等价于SQL Server的CROSS APPLY
• json_array_elements 函数会将输入的JSON数组拆分为多行，每行对应数组中的一个JSON对象
• ->> 运算符直接返回文本类型值，数值类字段可以通过强转得到对应类型
• 如果你存储的JSON类型为jsonb，将函数替换为jsonb_array_elements即可

可选：powers数组拆分为单行单个能力 #

如果你需要将每个能力单独拆分为一行，再加一层数组展开即可：

SELECT
  info ->> 'squadName' AS squad,
  mem ->> 'name' AS name,
  (mem ->> 'age')::INTEGER AS age,
  power AS power
FROM heroes
CROSS JOIN LATERAL json_array_elements(info -> 'members') AS mem
CROSS JOIN LATERAL json_array_elements_text(mem -> 'powers') AS power;

内容的提问来源于stack exchange，提问作者acircleda