多表关联生成多层嵌套JSON结构的SQL查询及PHP PDO实现问题

兄弟，完全可以用单SQL搞定！我之前在做类似多层嵌套JSON的需求时，也被Join和JSON聚合函数搞疯过，后来才摸清楚得从内到外分层聚合，不能一股脑把所有表都Join了再乱聚合，那样要么重复数据要么聚合出错。

先理清楚你的表关系：产品和分类是1对1，产品和配置（equipments）是1对多，配置和选项（options）是1对多对吧？那我们就从最内层的options开始，一层一层往外包聚合，最终就能得到你要的结构。

核心思路 #

1. 最内层：先把每个配置对应的选项聚合成你要的键值对形式的JSON对象（用JSON_OBJECTAGG，不是JSON_ARRAYAGG，因为你要的是{"options1": "值1"}这种键值对，不是数组）
2. 中间层：把每个产品对应的所有配置（已经包含聚合后的选项）聚合成JSON数组
3. 最外层：把每个产品的信息（名称、分类、聚合后的配置数组）组合成单个对象，再把所有产品聚合成最终的JSON结构

完整SQL语句（假设你的表结构如下） #

我先假设你的表和字段是常规命名，如果你实际字段不一样，对应替换就行：

• products：id, name, category_id（产品ID、名称、分类ID）
• categories：id, name（分类ID、名称）
• equipments：id, name, product_id（配置ID、名称、所属产品ID）
• options：id, name, descript, equipment_id（选项ID、键名（比如options1）、值、所属配置ID）

SELECT 
  JSON_OBJECTAGG(
    CONCAT('product_', p.id),
    JSON_OBJECT(
      'name', p.name,
      'category', c.name,
      'equipments', IFNULL(eqp_with_options.equipments_json, JSON_ARRAY())
    )
  ) AS final_result
FROM products p
JOIN categories c ON p.category_id = c.id
-- 左连接避免没有配置的产品被过滤
LEFT JOIN (
  -- 聚合每个产品对应的配置（带选项）
  SELECT 
    e.product_id,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'name', e.name,
        'options', IFNULL(opt.options_json, JSON_OBJECT())
      )
    ) AS equipments_json
  FROM equipments e
  -- 左连接避免没有选项的配置被过滤
  LEFT JOIN (
    -- 最内层：聚合每个配置对应的选项为键值对
    SELECT 
      o.equipment_id,
      JSON_OBJECTAGG(o.name, o.descript) AS options_json
    FROM options o
    GROUP BY o.equipment_id
  ) opt ON e.id = opt.equipment_id
  GROUP BY e.product_id
) eqp_with_options ON p.id = eqp_with_options.product_id
GROUP BY p.id;

代码解释 #

• 最内层子查询opt：把每个配置下的选项转成{"options1": "值1", "options2": "值2"}的JSON对象，用JSON_OBJECTAGG把选项的name作为键，descript作为值
• 中间层子查询eqp_with_options：把每个产品下的所有配置，和对应的选项JSON组合成单个对象，再用JSON_ARRAYAGG聚合成数组，就是你要的equipments数组
• 最外层：用JSON_OBJECTAGG把每个产品转成product_1、product_2为键的最终JSON，同时用IFNULL处理空数据（比如没有配置的产品、没有选项的配置）

PHP PDO 调用示例 #

try {
  // 初始化PDO连接（替换成你的数据库信息）
  $pdo = new PDO(
    'mysql:host=localhost;dbname=your_database;charset=utf8mb4',
    'your_username',
    'your_password',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
  );

  // 上面的SQL语句
  $sql = <<<SQL
  SELECT 
    JSON_OBJECTAGG(
      CONCAT('product_', p.id),
      JSON_OBJECT(
        'name', p.name,
        'category', c.name,
        'equipments', IFNULL(eqp_with_options.equipments_json, JSON_ARRAY())
      )
    ) AS final_result
  FROM products p
  JOIN categories c ON p.category_id = c.id
  LEFT JOIN (
    SELECT 
      e.product_id,
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'name', e.name,
          'options', IFNULL(opt.options_json, JSON_OBJECT())
        )
      ) AS equipments_json
    FROM equipments e
    LEFT JOIN (
      SELECT 
        o.equipment_id,
        JSON_OBJECTAGG(o.name, o.descript) AS options_json
      FROM options o
      GROUP BY o.equipment_id
    ) opt ON e.id = opt.equipment_id
    GROUP BY e.product_id
  ) eqp_with_options ON p.id = eqp_with_options.product_id
  GROUP BY p.id;
SQL;

  $stmt = $pdo->prepare($sql);
  $stmt->execute();
  $result = $stmt->fetch(PDO::FETCH_ASSOC);

  // 转成PHP数组或者直接输出JSON
  $final_data = json_decode($result['final_result'], true);
  print_r($final_data);

  // 或者直接输出JSON给前端
  // header('Content-Type: application/json');
  // echo $result['final_result'];
} catch(PDOException $e) {
  die("数据库错误: " . $e->getMessage());
}

注意事项 #

1. 确保你的MySQL版本是8.0及以上，因为JSON_OBJECTAGG是MySQL 8.0才引入的函数，低版本不支持
2. 如果你的实际表字段名、表名和我假设的不一样，一定要对应替换（比如如果产品的分类字段不是category_id，要改成你的实际字段）
3. 如果有些产品完全没有配置，或者有些配置完全没有选项，用LEFT JOIN+IFNULL就能保证这些数据不会被过滤，同时生成空的数组/对象

如果还是有问题，把你的实际表结构和字段名贴出来，我再帮你调整SQL！

内容来源于stack exchange