PostgreSQL中含JSON数组与NULL的列转结构化表方法咨询

针对你遇到的PostgreSQL中JSON数组列转结构化表的需求，可以通过过滤空值 + 横向关联JSON数组解析函数实现，具体步骤和SQL如下：

核心SQL语句 #

SELECT 
    rec.city,
    rec.state,
    rec.country
FROM 
    Information
CROSS JOIN LATERAL 
    json_to_recordset(Location) AS rec(city text, state text, country text)
WHERE 
    Location IS NOT NULL;

关键部分说明 #

1. 过滤空值：WHERE Location IS NOT NULL直接剔除无有效数据的NULL行，避免无效解析。
2. 解析JSON数组：json_to_recordset(Location)负责将每行的JSON数组拆分成多个JSON对象；AS rec(city text, state text, country text)定义输出列的名称和数据类型——这里只是定义结构而非硬编码数据，因为你的所有JSON对象键结构完全一致，只需指定一次即可。
3. 横向关联：CROSS JOIN LATERAL确保每行的JSON数组被展开后，与原表行正确关联，生成结构化的行数据。

适配JSONB类型 #

如果你的Location列是jsonb类型，只需将json_to_recordset替换为jsonb_to_recordset即可：

SELECT 
    rec.city,
    rec.state,
    rec.country
FROM 
    Information
CROSS JOIN LATERAL 
    jsonb_to_recordset(Location) AS rec(city text, state text, country text)
WHERE 
    Location IS NOT NULL;

为什么之前的方法无效？ #

• unnest是用于处理PostgreSQL原生数组（如text[]）的函数，无法解析JSON格式的数组，因此无效。
• json_to_recordset需要定义输出结构是SQL强类型特性的要求，并非硬编码数据，只需匹配JSON对象的键名和类型即可。

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