如何在Node.js中从MySQL一对多关联获取嵌套数组结构

在Node.js中使用mysql2包查询MySQL的一对多关联数据时，当前返回的是多个包含订单信息和对应单个商品的对象数组，期望得到订单对象中嵌套商品数组的结构。要求尽量通过MySQL语句实现，而非JavaScript函数处理。

现有代码 #

export const getOrderProducts = async (req, res) => {
  const { user_id, order_id } = req.params;

  const opt = {
    sql: `select orders.id , orders.order_status ,products.id as product_id, products.name,products.quantity  from users INNER JOIN orders on orders.user_id=users.unique_id INNER JOIN delivery_details on delivery_details.order_id = orders.id INNER JOIN delivery_types on delivery_types.id=orders.delivery_type_id INNER JOIN order_products on order_products.order_id = orders.id INNER JOIN products on products.id = order_products.product_id where users.unique_id='${user_id}' and orders.id='${order_id}'`,
    nestTables: true,
  };

  try {
    const data = await query(opt);
    return res.json(Success("Orders", data));
  } catch (error) {
    return res.status(500).json(Error("Internal Server Error try again", error));
  }
};

实际输出 #

[
    {
        "orders": {
            "id": 12,
            "order_status": "PENDING"
        },
        "products": {
            "product_id": 4,
            "name": "Sugarcane Juice",
            "quantity": "1"
        }
    },
    {
        "orders": {
            "id": 12,
            "order_status": "PENDING"
        },
        "products": {
            "product_id": 5,
            "name": "Milo ice",
            "quantity": "1"
        }
    }
]

期望输出 #

[
    {            
        "id": 12,
        "order_status": "PENDING",
        "products": [{
            "product_id": 4,
            "name": "Sugarcane Juice",
            "quantity": "1"
        },{
            "product_id": 5,
            "name": "Milo ice",
            "quantity": "1"
        }]     
    }
]

可以利用MySQL的JSON聚合函数（MySQL 8.0+支持JSON_ARRAYAGG，5.7版本可通过GROUP_CONCAT+JSON_ARRAY实现），直接在SQL中将同一订单下的商品聚合为JSON数组，无需后续JS处理。

修改后的SQL与代码 #

export const getOrderProducts = async (req, res) => {
  const { user_id, order_id } = req.params;

  // 使用参数化查询避免SQL注入，同时用JSON_ARRAYAGG聚合商品数据
  const opt = {
    sql: `
      SELECT 
        orders.id, 
        orders.order_status,
        JSON_ARRAYAGG(
          JSON_OBJECT(
            'product_id', products.id,
            'name', products.name,
            'quantity', products.quantity
          )
        ) AS products
      FROM users 
      INNER JOIN orders ON orders.user_id = users.unique_id 
      INNER JOIN delivery_details ON delivery_details.order_id = orders.id 
      INNER JOIN delivery_types ON delivery_types.id = orders.delivery_type_id 
      INNER JOIN order_products ON order_products.order_id = orders.id 
      INNER JOIN products ON products.id = order_products.product_id 
      WHERE users.unique_id = ? AND orders.id = ?
      GROUP BY orders.id, orders.order_status;
    `,
    // 无需nestTables，直接获取扁平化结构
    values: [user_id, order_id]
  };

  try {
    const data = await query(opt);
    return res.json(Success("Orders", data));
  } catch (error) {
    return res.status(500).json(Error("Internal Server Error try again", error));
  }
};

关键说明 #

1. JSON聚合逻辑：

   • JSON_OBJECT：将单个商品的字段封装为JSON对象
   • JSON_ARRAYAGG：将同一订单下的所有商品JSON对象聚合为一个数组
   • GROUP BY orders.id, orders.order_status：按订单分组，确保每个订单只返回一条数据

2. 安全优化：

   • 替换原SQL的字符串拼接为参数化查询（?占位符），避免SQL注入风险

3. 兼容性处理（MySQL 5.7）：
   如果使用MySQL 5.7（不支持JSON_ARRAYAGG），可以用GROUP_CONCAT替代：

   SELECT 
     orders.id, 
     orders.order_status,
     JSON_ARRAY(GROUP_CONCAT(
       JSON_OBJECT(
         'product_id', products.id,
         'name', products.name,
         'quantity', products.quantity
       ) SEPARATOR ','
     )) AS products
   -- 其余JOIN和WHERE语句不变
   GROUP BY orders.id, orders.order_status;
   

内容的提问来源于stack exchange，提问作者B Thiru Yogeshwaran