MySQL5.6/PHP替代JSON_OBJECT/JSON_ARRAYAGG实现嵌套JSON方案

1. MySQL 5.6 原生SQL实现可行性 #

MySQL 5.6没有内置JSON_OBJECT()、JSON_ARRAYAGG()这类JSON构造函数，理论上可以通过字符串拼接硬生成符合规范的JSON结果，但生产环境极不推荐。
实现逻辑是通过CONCAT()拼接JSON结构的括号、键值对，用GROUP_CONCAT()聚合多行记录为数组，使用前必须先调大group_concat_max_len参数避免内容截断，同时要手动转义所有字符串字段里的双引号、反斜杠、换行等特殊字符，否则极易生成无效JSON。
参考写法：

-- 临时调大GROUP_CONCAT长度上限，根据实际数据量调整数值
SET SESSION group_concat_max_len = 102400;
SELECT 
  CONCAT(
    '[',
    GROUP_CONCAT(
      CONCAT(
        '{"receipt_id":', r.receipt_id,
        ',"phone":"', REPLACE(REPLACE(r.phone, '\\', '\\\\'), '"', '\\"'),
        '","amount":"', r.amount,
        '","status":"', REPLACE(REPLACE(r.status, '\\', '\\\\'), '"', '\\"'),
        '","receipt_item":[', IFNULL(items.item_arr, '[]'),
        ']}'
      )
    ),
    ']'
  ) AS final_json
FROM Receipts r
LEFT JOIN (
  SELECT
    receipt_id,
    GROUP_CONCAT(
      CONCAT(
        '{"receipt_id":', receipt_id,
        ',"item_id":', item_id,
        ',"quantity":', quantity,
        ',"price":', price,
        '}'
      )
    ) AS item_arr
  FROM Receipt_Item
  GROUP BY receipt_id
) items ON r.receipt_id = items.receipt_id
GROUP BY NULL;

这种方案只适合临时小批量数据导出，线上业务用维护成本极高：只要有一个特殊字符没转义、或者内容超过长度限制被截断，返回的JSON就是完全不可用的格式，排查问题非常麻烦。

2. PHP后处理实现（生产环境推荐） #

这是最稳定、兼容性最好的方案，不需要在SQL层做复杂字符串处理，先通过普通左连接查询出平铺的关联结果，再单次遍历重组结构即可，性能远高于SQL拼接，也不存在JSON格式错误的问题。

第一步：查询平铺数据 #

用最基础的左连接SQL即可，不需要任何特殊语法：

SELECT
  r.receipt_id,
  r.phone,
  r.amount,
  r.status,
  ri.item_id,
  ri.quantity,
  ri.price
FROM Receipts r
LEFT JOIN Receipt_Item ri ON r.receipt_id = ri.receipt_id;

第二步：PHP重组数组 #

PHP没有专门针对这种父子层级分组的内置快捷函数，用单次循环+临时索引的写法效率最高，时间复杂度为O(n)，比组合调用array_unique()、array_column()、array_filter()等多函数嵌套的方案性能更好、可读性更强：

// $flatList为上述SQL查询返回的二维数组结果
$groupedResult = [];
foreach ($flatList as $row) {
    $receiptId = $row['receipt_id'];
    // 首次遍历到某张收据时，初始化主表信息
    if (!isset($groupedResult[$receiptId])) {
        $groupedResult[$receiptId] = [
            'receipt_id' => $row['receipt_id'],
            'phone' => $row['phone'],
            'amount' => $row['amount'],
            'status' => $row['status'],
            'receipt_item' => []
        ];
    }
    // 关联明细存在时，追加到明细数组（左连接无匹配明细时item_id为null，直接跳过）
    if (!is_null($row['item_id'])) {
        $groupedResult[$receiptId]['receipt_item'][] = [
            'item_id' => $row['item_id'],
            'quantity' => $row['quantity'],
            'price' => $row['price']
        ];
    }
}
// 去除临时用的receipt_id键名，返回从0开始的标准索引数组
$finalResult = array_values($groupedResult);
// 直接json_encode即可输出符合要求的JSON，加JSON_UNESCAPED_UNICODE避免中文被转义
$jsonOutput = json_encode($finalResult, JSON_UNESCAPED_UNICODE);

如果偏好函数式写法，也可以用array_reduce实现相同逻辑，底层性能和循环写法一致：

$finalResult = array_values(array_reduce($flatList, function($carry, $row) {
    $receiptId = $row['receipt_id'];
    if (!isset($carry[$receiptId])) {
        $carry[$receiptId] = [
            'receipt_id' => $row['receipt_id'],
            'phone' => $row['phone'],
            'amount' => $row['amount'],
            'status' => $row['status'],
            'receipt_item' => []
        ];
    }
    if (!is_null($row['item_id'])) {
        $carry[$receiptId]['receipt_item'][] = [
            'item_id' => $row['item_id'],
            'quantity' => $row['quantity'],
            'price' => $row['price']
        ];
    }
    return $carry;
}, []));

如果处理的数据量超过10万条，建议分批次查询主表记录，再按批次批量查询关联明细组装，避免一次性加载全量数据导致内存溢出。

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