如何在PostgreSQL函数中展平嵌套JSON字典并聚合数据

云原生阿岚

2026-7-26

PostgreSQL 展平test-table的JSONB数据方案

核心思路

先通过jsonb_array_elements将tickers数组拆分为独立行，再从每个数组元素中提取目标字段，最后与原表的city、store字段组合，缺失字段自动返回null。

基础SQL查询（先验证逻辑）

SELECT
    t.city,
    t.store,
    -- 从每个ticker元素提取字段，无值则返回null
    ticker->>'pudding' AS pudding,
    ticker->>'cake' AS cake,
    ticker->>'soda' AS soda,
    ticker->>'chips' AS chips,
    ticker->>'soda_machine' AS soda_machine,
    -- 假设day来自tickers元素，若来自paging则改为t.jsonb_data->'paging'->>'day'
    ticker->>'day' AS day
FROM
    test_table t,
    jsonb_array_elements(t.jsonb_data->'tickers') AS ticker;

封装为PostgreSQL函数

如果需要重复调用，可封装成返回表类型的函数：

1. 定义返回表类型

CREATE TYPE flattened_store_data AS (
    city TEXT,
    store TEXT,
    pudding TEXT,
    cake TEXT,
    soda TEXT,
    chips TEXT,
    soda_machine TEXT,
    day TEXT
);

2. 创建函数

CREATE OR REPLACE FUNCTION flatten_test_table()
RETURNS SETOF flattened_store_data AS $$
BEGIN
    RETURN QUERY
    SELECT
        t.city,
        t.store,
        ticker->>'pudding' AS pudding,
        ticker->>'cake' AS cake,
        ticker->>'soda' AS soda,
        ticker->>'chips' AS chips,
        ticker->>'soda_machine' AS soda_machine,
        ticker->>'day' AS day
    FROM
        test_table t,
        jsonb_array_elements(t.jsonb_data->'tickers') AS ticker;
END;
$$ LANGUAGE plpgsql;

调用函数

SELECT * FROM flatten_test_table();

补充说明

若day字段来自jsonb_data的paging节点而非tickers数组元素，将day的提取逻辑改为t.jsonb_data->'paging'->>'day'即可。
若字段是数字/布尔类型，可将->>改为->后结合类型转换，例如(ticker->'pudding')::INT。
若需保留tickers为空数组的行（所有JSON字段为null），可将关联逻辑改为LEFT JOIN LATERAL jsonb_array_elements(t.jsonb_data->'tickers') AS ticker ON true。