PostgreSQL 14中如何将字典列表字段转换为常规列

当然可以，用PostgreSQL自带的JSON函数就能搞定，具体步骤如下：

假设你的表名为goods，存储字典列表的字段名为inventory_data（字段类型为json或jsonb），执行以下查询即可将JSON数组展开为常规列：

SELECT
    (item ->> 'id')::INT AS id,
    item ->> 'stock' AS stock,
    item ->> 'name' AS name,
    item ->> 'store' AS store
FROM
    goods,
    jsonb_array_elements(goods.inventory_data) AS item;

关键函数说明 #

• jsonb_array_elements()：将JSON数组拆分成多行单个JSON对象（如果字段是json类型，替换为json_array_elements()）
• ->>：提取JSON对象中指定键的文本值，再根据实际类型转换（比如id转成INT）

如果需要保留原表的其他字段，可以加上原表字段，示例：

SELECT
    goods.id AS original_table_id,
    (item ->> 'id')::INT AS inventory_id,
    item ->> 'stock' AS stock,
    item ->> 'name' AS name,
    item ->> 'store' AS store
FROM
    goods,
    jsonb_array_elements(goods.inventory_data) AS item;

这样就能把嵌套的JSON数组完全展开成你需要的常规表格格式，不管数组里包含多少个结构一致的字典都能处理。

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