如何在PostgreSQL中按年龄区间拆分JSON数组至不同列？

假设你的表结构如下（以表名person_data、存储JSON的字段data为例）：

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

先插入包含多年龄区间的示例数据：

INSERT INTO person_data (data) VALUES (
    '{
        "jsonObject":
            [ 
                { "Name" : "XPerson", "Age"  : 18},
                { "Name" : "YPerson", "Age"  : 18},
                { "Name" : "ZPerson", "Age"  : 17},
                { "Name" : "APerson", "Age"  : 26},
                { "Name" : "BPerson", "Age"  : 22}
            ]
    }'
);

核心实现SQL #

通过「拆分JSON数组→按年龄区间筛选聚合→生成目标列」的逻辑完成需求：

SELECT
    -- 年龄小于18的对象数组
    COALESCE(json_agg(CASE WHEN (elem->>'Age')::INT < 18 THEN elem END), '[]'::JSON) AS column_under_18,
    -- 年龄18-25的对象数组
    COALESCE(json_agg(CASE WHEN (elem->>'Age')::INT BETWEEN 18 AND 25 THEN elem END), '[]'::JSON) AS column_18_to_25,
    -- 年龄大于25的对象数组
    COALESCE(json_agg(CASE WHEN (elem->>'Age')::INT > 25 THEN elem END), '[]'::JSON) AS column_over_25
FROM person_data,
     jsonb_array_elements(data->'jsonObject') AS elem;

关键代码解释 #

• jsonb_array_elements(data->'jsonObject') AS elem：将jsonObject对应的JSON数组拆分为单行的JSON对象，每个对象对应一行elem数据。
• (elem->>'Age')::INT：提取每个JSON对象中的Age字段，转换为整数类型用于区间判断。
• CASE ... END：根据年龄区间筛选符合条件的JSON对象。
• json_agg(...)：将筛选后的对象重新聚合为JSON数组，作为对应列的内容；COALESCE用来把空结果转为空数组[]，避免返回NULL。

多数据行适配优化 #

如果表中有多条JSON数据（多行person_data），只需按主键分组即可：

SELECT
    id,
    COALESCE(json_agg(CASE WHEN (elem->>'Age')::INT < 18 THEN elem END), '[]'::JSON) AS column_under_18,
    COALESCE(json_agg(CASE WHEN (elem->>'Age')::INT BETWEEN 18 AND 25 THEN elem END), '[]'::JSON) AS column_18_to_25,
    COALESCE(json_agg(CASE WHEN (elem->>'Age')::INT > 25 THEN elem END), '[]'::JSON) AS column_over_25
FROM person_data,
     jsonb_array_elements(data->'jsonObject') AS elem
GROUP BY id;

注：如果你的JSON字段是JSON类型而非JSONB，只需把jsonb_array_elements替换为json_array_elements即可。

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