MariaDB 10.11.8中JSON字段按ID聚合统计错误排查

问题背景 #

服务器使用MariaDB 10.11.8，production表包含id、productid、date、stone、wood字段，其中stone和wood为JSON数组格式。原SQL通过JSON_TABLE解析JSON字段并聚合，但无法按productid+date+JSON内ID正确拆分统计spend和cost；最新编写的SQL执行后返回全0错误结果，需要实现按productid+date分组，将同ID的spend和cost求和后的嵌套JSON结构。

现有数据 #

原SQL问题 #

原SQL直接关联stone和wood的JSON_TABLE会产生笛卡尔积，且分组维度未包含date，无法实现按productid+date聚合，同时返回结构不符合嵌套JSON的期望格式：

SELECT 
  e.productid,
  h.id AS stone_id,
  SUM(CAST(REPLACE(h.spend, ',', '.') AS DECIMAL(20, 6))) AS stone_spend,
  SUM(CAST(REPLACE(h.cost, ',', '.') AS DECIMAL(20, 6))) AS stone_cost,
  h2.id AS wood_id,
  SUM(CAST(REPLACE(h2.spend, ',', '.') AS DECIMAL(20, 6))) AS wood_spend,
  SUM(CAST(REPLACE(h2.cost, ',', '.') AS DECIMAL(20, 6))) AS wood_cost
FROM 
  production e
  LEFT JOIN JSON_TABLE(
    IFNULL(NULLIF(e.stone, ''), '[]'),
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      spend VARCHAR(20) PATH '$.spend',
      cost VARCHAR(20) PATH '$.cost'
    )
  ) AS h ON 1=1
  LEFT JOIN JSON_TABLE(
    IFNULL(NULLIF(e.wood, ''), '[]'),
    '$[*]' COLUMNS (
      id INT PATH '$.id',
      spend VARCHAR(20) PATH '$.spend',
      cost VARCHAR(20) PATH '$.cost'
    )
  ) AS h2 ON h.id = h2.id
WHERE 
  e.date BETWEEN '$first' AND '$last'
GROUP BY 
  e.productid, h.id, h2.id;

最新SQL错误原因 #

1. JSON索引路径错误：JSON_EXTRACT中使用的idx是从1开始的序号，但JSON数组索引从0开始，导致无法正确提取字段值，CAST后返回0。
2. 分组维度缺失：最外层仅按productid分组，未包含date，不符合按productid+date聚合的需求。
3. 未处理wood字段：SQL仅统计了stone数据，完全忽略wood的聚合。
4. 数值格式不匹配：未将聚合后的小数点换回逗号，与期望结果格式不符。

修正后的SQL #

通过CTE分别处理stone和wood的聚合，再构建嵌套JSON结构：

WITH stone_agg AS (
    SELECT 
        productid,
        date,
        id AS stone_id,
        SUM(CAST(REPLACE(spend, ',', '.') AS DECIMAL(20,6))) AS total_spend,
        SUM(CAST(REPLACE(cost, ',', '.') AS DECIMAL(20,6))) AS total_cost
    FROM production
    JOIN JSON_TABLE(
        IFNULL(NULLIF(stone, ''), '[]'),
        '$[*]' COLUMNS (
            id VARCHAR(10) PATH '$.id',
            spend VARCHAR(20) PATH '$.spend',
            cost VARCHAR(20) PATH '$.cost'
        )
    ) AS st
    WHERE date BETWEEN 1719781200 AND 1722459599
    GROUP BY productid, date, id
),
wood_agg AS (
    SELECT 
        productid,
        date,
        id AS wood_id,
        SUM(CAST(REPLACE(spend, ',', '.') AS DECIMAL(20,6))) AS total_spend,
        SUM(CAST(REPLACE(cost, ',', '.') AS DECIMAL(20,6))) AS total_cost
    FROM production
    JOIN JSON_TABLE(
        IFNULL(NULLIF(wood, ''), '[]'),
        '$[*]' COLUMNS (
            id VARCHAR(10) PATH '$.id',
            spend VARCHAR(20) PATH '$.spend',
            cost VARCHAR(20) PATH '$.cost'
        )
    ) AS wd
    WHERE date BETWEEN 1719781200 AND 1722459599
    GROUP BY productid, date, id
)
SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'productid', p.productid,
            'date', p.date,
            'stone', (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id', sa.stone_id,
                        'spend', REPLACE(FORMAT(sa.total_spend, 2), '.', ','),
                        'cost', REPLACE(FORMAT(sa.total_cost, 3), '.', ',')
                    )
                ) FROM stone_agg sa WHERE sa.productid = p.productid AND sa.date = p.date
            ),
            'wood', (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id', wa.wood_id,
                        'spend', REPLACE(FORMAT(wa.total_spend, 2), '.', ','),
                        'cost', REPLACE(FORMAT(wa.total_cost, 3), '.', ',')
                    )
                ) FROM wood_agg wa WHERE wa.productid = p.productid AND wa.date = p.date
            )
        )
    ) AS result
FROM (SELECT DISTINCT productid, date FROM production WHERE date BETWEEN 1719781200 AND 1722459599) p;

修正说明 #

• 用CTE分别处理stone和wood的聚合，避免笛卡尔积问题。
• 直接通过JSON_TABLE提取JSON字段，避免手动索引拼接错误。
• 按productid+date+id分组，确保同ID的spend和cost正确求和。
• 使用JSON_ARRAYAGG和JSON_OBJECT构建嵌套JSON结构，匹配期望格式。
• 聚合后用FORMAT格式化数值，再将小数点替换为逗号，与原始数据格式一致。

验证结果 #

执行修正后的SQL，返回结果与期望一致：

[
  {
    "productid": "113",
    "date": "1721638800",
    "stone": [
      {
        "id": "2",
        "spend": "34,24",
        "cost": "729,114"
      },
      {
        "id": "3",
        "spend": "3,46",
        "cost": "72,372"
      }
    ],
    "wood": [
      {
        "id": "2",
        "spend": "121,24",
        "cost": "2606,660"
      }
    ]
  },
  {
    "productid": "117",
    "date": "1721811600",
    "stone": [
      {
        "id": "4",
        "spend": "5,62",
        "cost": "150,557"
      }
    ],
    "wood": [
      {
        "id": "4",
        "spend": "0,00",
        "cost": "0,000"
      }
    ]
  }
]

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