基于PostgreSQL的JSON动态扩展与增量计数实现需求问询

嘿，我完全懂你想要的效果——直接在PostgreSQL里搞定JSON字段的品牌动态计数，不用先把数据拉到Python里处理对吧？这事儿其实用PostgreSQL自带的jsonb类型和相关函数就能完美实现，我给你一步步讲清楚怎么做。

首先推荐你用jsonb类型存储品牌计数（而不是json），因为jsonb支持更高效的修改、合并操作，性能更优。核心逻辑是：把要新增的品牌列表和现有JSON里的品牌计数做聚合，相同品牌的计数累加，新品牌则初始化为1，最后重新合并成新的JSONB对象。

假设你的表叫products，有id（主键）和brand_counts jsonb字段（初始值设为'{}'::jsonb或者允许为空）。下面是针对不同插入场景的SQL语句：

示例1：第一次插入多个品牌（toyota,honda,nissan） #

WITH new_brands AS (
  -- 定义要插入的品牌列表
  SELECT unnest(array['toyota', 'honda', 'nissan']) AS brand
)
UPDATE products
SET brand_counts = COALESCE(brand_counts, '{}'::jsonb) || (
  SELECT jsonb_object_agg(brand, COALESCE(brand_counts->>brand, '0')::int + 1)
  FROM new_brands
)
WHERE id = 1; -- 替换成你要更新的记录ID

执行后，brand_counts会变成：{"toyota":1, "honda":1, "nissan":1}

示例2：插入已存在的品牌（toyota） #

WITH new_brands AS (
  SELECT unnest(array['toyota']) AS brand
)
UPDATE products
SET brand_counts = COALESCE(brand_counts, '{}'::jsonb) || (
  SELECT jsonb_object_agg(brand, COALESCE(brand_counts->>brand, '0')::int + 1)
  FROM new_brands
)
WHERE id = 1;

执行后，brand_counts更新为：{"toyota":2, "honda":1, "nissan":1}

示例3：混合插入新旧品牌（honda,mitsubishi） #

WITH new_brands AS (
  SELECT unnest(array['honda', 'mitsubishi']) AS brand
)
UPDATE products
SET brand_counts = COALESCE(brand_counts, '{}'::jsonb) || (
  SELECT jsonb_object_agg(brand, COALESCE(brand_counts->>brand, '0')::int + 1)
  FROM new_brands
)
WHERE id = 1;

执行后，brand_counts最终变成：{"toyota":2, "honda":2, "nissan":1, "mitsubishi":1}

如果需要频繁执行这类操作，可以把逻辑封装成PL/pgSQL函数，调用起来更简洁：

CREATE OR REPLACE FUNCTION update_brand_counts(record_id INT, new_brands TEXT[])
RETURNS VOID AS $$
BEGIN
  WITH brands AS (
    SELECT unnest(new_brands) AS brand
  )
  UPDATE products
  SET brand_counts = COALESCE(brand_counts, '{}'::jsonb) || (
    SELECT jsonb_object_agg(brand, COALESCE(brand_counts->>brand, '0')::int + 1)
    FROM brands
  )
  WHERE id = record_id;
END;
$$ LANGUAGE plpgsql;

调用的时候只需要执行：

SELECT update_brand_counts(1, ARRAY['toyota', 'honda']);

• COALESCE(brand_counts, '{}'::jsonb)：处理初始时brand_counts为空的情况，确保合并操作不会出错。
• brand_counts->>brand：从现有JSONB中取出对应品牌的计数字符串，转成整数后加1；如果品牌不存在，COALESCE会把默认值设为0，加1后就是1。
• jsonb_object_agg：把聚合后的品牌和计数重新组合成JSONB对象，再用||和现有JSONB合并（新的计数会覆盖旧的，实现累加效果）。

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