如何从MySQL按Year与Car双列分组返回多维JSON数组？

需要基于数据表的Year和Car字段分组，生成层级为「年份 → 车型 → 具体记录」的多维JSON数组，每条记录包含ID、Year、Color、Date信息。

数据表结构及数据 #

目标JSON结构示例 #

[
  [
    "2025",
    ["Tesla", [[1, 2025, "Red", "06/20/2025"]]],
    ["Cadillac", [[2, 2025, "Black", "06/15/2025"], [3, 2025, "White", "06/01/2025"]]]
  ],
  [
    "2024",
    ["Silverado", [[4, 2024, "Gray", "12/01/2024"]]]
  ],
  [
    "2023",
    ["Cadillac", [[5, 2023, "Red", "06/01/2023"]]],
    ["Tesla", [[6, 2023, "Black", "06/01/2023"], [7, 2023, "Blue", "05/01/2023"]]]
  ]
]

方法一：用MySQL JSON函数直接生成 #

通过嵌套JSON_ARRAYAGG实现层级分组，直接输出目标JSON：

SELECT JSON_ARRAYAGG(year_group) AS result
FROM (
  SELECT JSON_ARRAY(
    Year,
    JSON_ARRAYAGG(car_group)
  ) AS year_group
  FROM (
    SELECT 
      Year,
      JSON_ARRAY(
        Car,
        JSON_ARRAYAGG(
          JSON_ARRAY(ID, Year, Color, Date)
        )
      ) AS car_group
    FROM cars
    GROUP BY Year, Car
    ORDER BY Year DESC, Car
  ) AS car_groups
  GROUP BY Year
  ORDER BY Year DESC
) AS year_groups;

内层先按Year+Car分组，生成对应车型的记录数组；再按Year分组聚合成年份层级的数组；最后整体聚合为外层数组，完全匹配需求结构。

方法二：查询基础数据后用程序处理（以PHP为例） #

如果需要更灵活的结构控制，先查询排序后的数据，再通过代码构建层级：

1. 执行SQL查询：

SELECT ID, Year, Car, Color, Date
FROM cars
ORDER BY Year DESC, Car, ID;

2. PHP代码构建多维数组并转JSON：

// 假设$pdo为已初始化的数据库连接
$stmt = $pdo->query("SELECT ID, Year, Car, Color, Date FROM cars ORDER BY Year DESC, Car, ID");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$result = [];
$currentYear = null;
$currentCar = null;

foreach ($rows as $row) {
    $year = (string)$row['Year'];
    $car = $row['Car'];
    
    // 切换年份时，新增年份层级
    if ($year !== $currentYear) {
        $currentYear = $year;
        $result[] = [$year];
        $currentCar = null;
    }
    
    // 切换车型时，新增车型层级
    if ($car !== $currentCar) {
        $currentCar = $car;
        $yearIdx = count($result) - 1;
        $result[$yearIdx][] = [$car, []];
    }
    
    // 添加当前记录到对应车型下
    $yearIdx = count($result) - 1;
    $carIdx = count($result[$yearIdx]) - 1;
    $result[$yearIdx][$carIdx][1][] = [
        $row['ID'],
        (int)$row['Year'],
        $row['Color'],
        $row['Date']
    ];
}

// 输出格式化后的JSON
echo json_encode($result, JSON_PRETTY_PRINT);

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