PostgreSQL合并JSONB类型features字段的SQL查询实现

需求明确 #

针对指定版本的已支付订单，合并其关联的所有plans表记录的features字段，需遵循以下规则：

• amount_images字段：对所有关联plan的该字段值求和
• 仅单个plan存在的特性：直接保留该特性的原值
• 布尔类型特性：取逻辑或（任意一个plan的该特性为true，结果即为true）

表结构参考 #

plans表 #

CREATE TABLE plans (
    id INT PRIMARY KEY,
    version VARCHAR(20) NOT NULL,
    features JSONB NOT NULL -- 存储各类特性，如amount_images、布尔权限等
);

payments表 #

CREATE TABLE payments (
    id INT PRIMARY KEY,
    order_id INT NOT NULL,
    plan_id INT NOT NULL REFERENCES plans(id),
    is_paid BOOLEAN NOT NULL DEFAULT false,
    version VARCHAR(20) NOT NULL
);

测试数据 #

插入plans表测试数据 #

INSERT INTO plans (id, version, features) VALUES
(1, 'v1', '{"amount_images": 10, "can_edit": true, "can_share": false}'),
(2, 'v1', '{"amount_images": 5, "can_edit": false, "can_delete": true}'),
(3, 'v2', '{"amount_images": 15, "can_edit": true, "can_download": true}');

插入payments表测试数据 #

INSERT INTO payments (id, order_id, plan_id, is_paid, version) VALUES
(1, 1001, 1, true, 'v1'),
(2, 1001, 2, true, 'v1'),
(3, 1002, 3, true, 'v2');

实现SQL查询 #

以下查询以版本v1为例，你可以修改pay.version = 'v1'中的值来切换目标版本：

WITH paid_plan_features AS (
    -- 筛选目标版本下已支付订单关联的所有plan的features
    SELECT p.features
    FROM payments pay
    JOIN plans p ON pay.plan_id = p.id
    WHERE pay.is_paid = true 
      AND pay.version = 'v1'
),
feature_types AS (
    -- 识别所有特性的类型：数值型、布尔型、唯一型（仅单个plan存在）
    SELECT 
        key AS feature_name,
        CASE
            WHEN key = 'amount_images' THEN 'numeric'
            WHEN value::text IN ('true', 'false') THEN 'boolean'
            ELSE 'unique'
        END AS type
    FROM paid_plan_features, jsonb_each(features)
    GROUP BY key, value::text, type
),
merged_numeric AS (
    -- 处理amount_images求和
    SELECT jsonb_build_object('amount_images', SUM((features->>'amount_images')::INT)) AS part
    FROM paid_plan_features
),
merged_boolean AS (
    -- 处理布尔特性取逻辑或
    SELECT jsonb_object_agg(ft.feature_name, bool_or((ppf.features->>ft.feature_name)::BOOLEAN)) AS part
    FROM paid_plan_features ppf
    JOIN feature_types ft ON ft.type = 'boolean' AND ppf.features ? ft.feature_name
    GROUP BY ft.type
),
merged_unique AS (
    -- 处理仅单个plan存在的特性
    SELECT jsonb_object_agg(ft.feature_name, ppf.features->ft.feature_name) AS part
    FROM paid_plan_features ppf
    JOIN feature_types ft ON ft.type = 'unique' AND ppf.features ? ft.feature_name
    GROUP BY ft.type
)
-- 合并所有部分为最终的features对象
SELECT jsonb_object_agg(k, v) AS merged_features
FROM (
    SELECT * FROM merged_numeric
    UNION ALL
    SELECT * FROM merged_boolean
    UNION ALL
    SELECT * FROM merged_unique
) AS parts,
jsonb_each(part);

查询结果说明 #

针对测试数据中的v1版本，执行后会得到合并后的features：

{
  "amount_images": 15,
  "can_edit": true,
  "can_share": false,
  "can_delete": true
}

逻辑拆解 #

1. paid_plan_features：锁定目标版本下已支付订单对应的所有plan的features数据
2. feature_types：遍历所有特性，按规则分类，确保后续处理逻辑清晰
3. 各合并CTE：分别处理数值求和、布尔或运算、唯一特性保留三类场景
4. 最终聚合：将三类处理结果合并为一个完整的JSONB对象，输出最终合并后的特性集合

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