PostgreSQL中查询Map列表并按年龄分列展示的实现方法

你需要将存储的JSON数组数据按城市拆分后，把人员按<18岁、18-25岁、>25岁三个区间分别聚合到对应列中，以下是具体实现步骤：

1. 准备示例数据（可选） #

假设你有一张存储JSON数据的表city_data，先创建表并插入示例数据：

CREATE TABLE city_data (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO city_data (data) VALUES (
'[
    {
        "City1":
            [ 
                { "Name" : "XPerson",
                "Age"  : 18},
                { "Name" : "YPerson",
                "Age"  : 18}
            ],
        "PinCode":314001
    },
    {
        "City2":
            [ 
                { "Name" : "APerson",
                  "Age"  : 25},
                { "Name" : "ZPerson",
                "Age"  : 26}
            ],
        "PinCode":314002
    }
]'
);

2. 核心SQL实现 #

通过CTE（公共表表达式）分步骤解析和聚合数据：

WITH city_persons AS (
    -- 拆分顶层JSON数组，提取城市名、邮编和人员列表
    SELECT
        (jsonb_each(city_obj)).key AS city_name,
        city_obj->>'PinCode' AS pin_code,
        (jsonb_each(city_obj)).value AS person_list
    FROM city_data,
         jsonb_array_elements(data) AS city_obj
    WHERE (jsonb_each(city_obj)).key != 'PinCode'
),
person_details AS (
    -- 拆分人员列表，提取每个人员的姓名和年龄
    SELECT
        city_name,
        pin_code,
        (person->>'Name') AS person_name,
        (person->>'Age')::INT AS person_age
    FROM city_persons,
         jsonb_array_elements(person_list) AS person
)
-- 按城市分组，聚合不同年龄区间的人员
SELECT
    city_name,
    pin_code,
    -- 聚合<18岁的人员姓名
    array_agg(person_name) FILTER (WHERE person_age < 18) AS under_18,
    -- 聚合18-25岁的人员姓名
    array_agg(person_name) FILTER (WHERE person_age BETWEEN 18 AND 25) AS age_18_to_25,
    -- 聚合>25岁的人员姓名
    array_agg(person_name) FILTER (WHERE person_age > 25) AS over_25
FROM person_details
GROUP BY city_name, pin_code
ORDER BY city_name;

3. 结果说明 #

执行上述SQL后，会得到如下格式的结果：

4. 扩展说明 #

• 如果需要保留人员的完整JSON信息（而非仅姓名），可将array_agg(person_name)替换为json_agg(person)，每个列会变成包含人员完整信息的JSON数组。
• 若你的JSON数据类型是json而非jsonb，只需将SQL中所有jsonb_前缀改为json_即可。
• 该方案支持N个城市对象的扩展，无需修改SQL硬编码城市名称。

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