PostgreSQL中JSON数据转表格格式的查询语法求助

你的问题出在对JSON数组的展开方式错误，原SQL里用unnest(array[data->'attr2'])是不对的——data->'attr2'本身就是JSON数组类型，不能直接转成PostgreSQL数组来unnest，应该用PostgreSQL专门提供的JSON数组展开函数。另外你的SQL还有语法错误：action'多了一个单引号，且data->'attr2'前面漏了逗号。

以下是正确的查询语句，分两种字段类型情况：

情况1：data字段是JSON类型 #

SELECT
    data->>'attr1' AS attr1,
    attr2_item->>'subattr' AS subattr,
    attr2_item->>'subattr2' AS subattr2,
    data->>'attr3' AS attr3
FROM my_table,
     json_array_elements(data->'attr2') AS attr2_item;

情况2：data字段是JSONB类型（推荐使用JSONB，性能更好） #

SELECT
    data->>'attr1' AS attr1,
    attr2_item->>'subattr' AS subattr,
    attr2_item->>'subattr2' AS subattr2,
    data->>'attr3' AS attr3
FROM my_table,
     jsonb_array_elements(data->'attr2') AS attr2_item;

关键说明： #

• json_array_elements（或jsonb_array_elements）：专门用于将JSON数组拆分为多行，每一行对应数组中的一个JSON对象，这里我们将拆分后的对象命名为attr2_item。
• 使用->>而非->：->返回的是JSON类型的值（会带引号），->>直接返回文本类型的值，正好匹配你期望的表格结果格式。
• 这种写法是PostgreSQL中的隐式连接（LATERAL JOIN的简化形式），会自动将原表的每一行与拆分后的数组行进行关联，保证attr1、attr3和对应的数组元素正确匹配。

执行以上语句后，就能得到你想要的扁平表格结果。

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