PostgreSQL如何将包含数组的普通值转换为规范结构化数据表？

解决方法 #

你可以通过括号包裹RECORD字段+访问默认字段名的方式拆分匿名行类型数据，调整后可直接运行的SQL如下：

WITH "raw_data" ("author", "articles") AS  (
    VALUES
        ('author1', ARRAY[('title1', 'content1'), ('title2', 'content2')]::RECORD[]),
        ('author2', ARRAY[('title3', 'content3'), ('title4', 'content4')]::RECORD[])
),
"articles" AS (
    SELECT "author", unnest("articles") AS "article" FROM "raw_data"
)
SELECT 
  author,
  (article).f1 AS title,
  (article).f2 AS content 
FROM "articles";

逻辑说明 #

• 你当前得到的article字段属于未预先定义结构的匿名RECORD类型，PostgreSQL对这类行类型的字段会按定义顺序默认分配f1、f2、f3……的命名规则
• 访问RECORD类型的子字段时，必须先用括号将行字段完整包裹，再加.运算符访问子字段，省略括号会触发语法错误
• 你也可以用更简洁的隐式连接写法，省去一层CTE嵌套：

SELECT 
  author,
  (article).f1 AS title,
  (article).f2 AS content
FROM (
  VALUES
    ('author1', ARRAY[('title1', 'content1'), ('title2', 'content2')]),
    ('author2', ARRAY[('title3', 'content3'), ('title4', 'content4')])
) AS raw_data(author, articles),
unnest(articles) AS article;

如果需要更高的可读性，你也可以预先定义复合类型替代匿名RECORD，就可以用自定义的字段名替代默认的f1/f2命名。

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