MariaDB如何实现JSON对象内嵌JSON数组的汇总明细查询

直接用MySQL原生JSON函数完成嵌套结构组装，单条SQL直接输出符合要求的JSON，无需PHP层循环拼接，大数据量下性能远高于双层循环实现。

你之前的SQL问题在于仅做了外层汇总对象的聚合，没有先按产品分组聚合出对应的明细数组，因此无法完成嵌套关联。核心实现逻辑分两层：

• 第一层按Prd_Code分组，计算产品维度的汇总字段，同时将同组下所有明细聚合为famproductdesc数组
• 第二层将所有产品汇总对象聚合为famproduct数组，最终包裹到最外层的Data对象中

MySQL 8.0+ 版本实现（推荐） #

支持窗口函数、聚合内排序，写法简洁性能好：

SELECT 
  JSON_OBJECT(
    'Data',
    JSON_OBJECT(
      'famproduct',
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'Prd_Code', Prd_Code,
          'Prd_Name', Prd_Name,
          'Max_SrNo', max_srno,
          'Max_Sr_Rate', max_sr_rate,
          'Total_Stock', total_stock,
          'famproductdesc', desc_arr
        )
      )
    )
  ) AS FinalJson
FROM (
  SELECT 
    Prd_Code,
    Prd_Name,
    MAX(Prd_SrNo) AS max_srno,
    -- 提取最大序列号对应的单价
    MAX(CASE WHEN rn = 1 THEN Prd_Rate END) AS max_sr_rate,
    SUM(Stock) AS total_stock,
    -- 聚合当前产品下所有明细，按序列号升序排列
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'Pid', Pid,
        'Prd_SrNo', Prd_SrNo,
        'Prd_Rate', Prd_Rate,
        'Stock', Stock
      ) ORDER BY Prd_SrNo ASC
    ) AS desc_arr
  FROM (
    SELECT 
      *,
      -- 按产品分组，组内按序列号倒序打标，行号1即为最大序列号记录
      ROW_NUMBER() OVER (PARTITION BY Prd_Code ORDER BY Prd_SrNo DESC) AS rn
    FROM FAMPRODUCT
    -- 替换为实际业务过滤条件
    WHERE Comp_Year = '2022-2023' 
      AND Comp_No = '1' 
      AND P_Code = 'OMSU0095'
  ) t
  GROUP BY Prd_Code, Prd_Name
) res;

MySQL 5.7 兼容版本 #

5.7版本不支持窗口函数和聚合内排序，通过子查询排序+关联取最大序列号对应单价实现：

SELECT 
  JSON_OBJECT(
    'Data',
    JSON_OBJECT(
      'famproduct',
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'Prd_Code', t.Prd_Code,
          'Prd_Name', t.Prd_Name,
          'Max_SrNo', t.max_srno,
          'Max_Sr_Rate', main.Prd_Rate,
          'Total_Stock', t.total_stock,
          'famproductdesc', t.desc_arr
        )
      )
    )
  ) AS FinalJson
FROM (
  SELECT 
    Prd_Code,
    Prd_Name,
    MAX(Prd_SrNo) AS max_srno,
    SUM(Stock) AS total_stock,
    -- 先排序再聚合，保证明细按序列号升序
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'Pid', Pid,
        'Prd_SrNo', Prd_SrNo,
        'Prd_Rate', Prd_Rate,
        'Stock', Stock
      )
    ) AS desc_arr
  FROM (
    SELECT * FROM FAMPRODUCT
    WHERE Comp_Year = '2022-2023' 
      AND Comp_No = '1' 
      AND P_Code = 'OMSU0095'
    ORDER BY Prd_Code, Prd_SrNo ASC
  ) sorted_t
  GROUP BY Prd_Code, Prd_Name
) t
-- 关联查询最大序列号对应的单价
JOIN FAMPRODUCT main 
  ON main.Prd_Code = t.Prd_Code 
  AND main.Prd_SrNo = t.max_srno
WHERE main.Comp_Year = '2022-2023' 
  AND main.Comp_No = '1' 
  AND main.P_Code = 'OMSU0095';

注意：需保证Prd_Code + Prd_SrNo联合唯一，避免关联出现重复数据。

优化建议 #

• 给过滤条件涉及字段加联合索引idx_query(Comp_Year, Comp_No, P_Code, Prd_Code, Prd_SrNo)，可直接走索引完成查询、分组，无需回表，十万级以上数据量性能提升可达数倍
• 查询返回的结果直接就是标准JSON格式，PHP端无需额外处理，可直接输出给前端

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