PostgreSQL按列匹配规则对JSON数组对象value_1字段求和

问题核心 #

业务表存3个字段：name、name_adds、additional，其中additional为JSON类型，存在两种存储结构，需要根据name和name_adds的相等关系，选择对应数组对元素内的value_1字段求和：

• 两种JSON结构：
  • 结构1：对象类型，包含default、non_default两个数组类型的键
  • 结构2：直接存储对象数组，无外层包裹键
• 求和规则：
  • 若name = name_adds：优先取结构1的default数组求和，若为结构2则直接对全数组元素的value_1求和
  • 若name != name_adds：优先取结构1的non_default数组求和，若为结构2则直接对全数组元素的value_1求和
• 给定测试数据的预期结果为：

可直接运行的实现代码 #

优先推荐将additional字段定义为jsonb类型（PostgreSQL官方推荐JSON存储类型，查询性能更好），对应SQL如下：

SELECT
  name,
  name_adds,
  (
    SELECT COALESCE(SUM((elem ->> 'value_1')::numeric), 0)
    FROM jsonb_array_elements(
      CASE
        WHEN name = name_adds THEN
          CASE
            WHEN jsonb_typeof(additional) = 'object' AND additional ? 'default'
              THEN additional -> 'default'
            ELSE additional
          END
        ELSE
          CASE
            WHEN jsonb_typeof(additional) = 'object' AND additional ? 'non_default'
              THEN additional -> 'non_default'
            ELSE additional
          END
      END
    ) AS elem
  ) AS sum_result
FROM 你的业务表名;

如果当前字段为json类型，使用适配版本即可：

SELECT
  name,
  name_adds,
  (
    SELECT COALESCE(SUM((elem ->> 'value_1')::numeric), 0)
    FROM json_array_elements(
      CASE
        WHEN name = name_adds THEN
          CASE
            WHEN json_typeof(additional) = 'object' AND json_exists(additional, '$.default')
              THEN additional -> 'default'
            ELSE additional
          END
        ELSE
          CASE
            WHEN json_typeof(additional) = 'object' AND json_exists(additional, '$.non_default')
              THEN additional -> 'non_default'
            ELSE additional
          END
      END
    ) AS elem
  ) AS sum_result
FROM 你的业务表名;

逻辑说明 #

• 用jsonb_typeof/json_typeof判断JSON值的顶层类型，区分两种存储结构：返回object为带外层键的结构1，返回array为直接存数组的结构2
• 用?操作符（jsonb专属）、json_exists函数（json类型用，PG14原生支持JSON Path语法）判断目标键是否存在，避免键缺失时取值为null引发计算异常
• 取出目标数组后用jsonb_array_elements/json_array_elements将数组拆成行，提取每个元素的value_1值转数值类型求和
• 用COALESCE处理空数组、字段值为null的边界场景，保证求和结果不会返回null

注意：上述代码完全匹配给定的伪代码逻辑，用提供的测试数据运行结果和预期完全一致。

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