MySQL如何关联两张匹配表并将对应数据分组聚合为数组

问题背景 #

需要关联MySQL中的products商品表与orders订单表，以订单ID为关联匹配字段，将同一订单下匹配到的所有商品ID聚合为数组格式。
初始使用基础JOIN编写的查询未做分组聚合，返回结果为订单与商品关联的平铺行，未达到预期结构。

初始查询语句 #

SELECT Order.id, Order.userId, Product.id AS productsIds
FROM Orders AS Order
JOIN Products AS Product
ON Order.id = Product.orderId

当前查询返回结果 #

[
  {
    "id": 1,
    "userId": 1,
    "productsIds": 2
  },
  {
    "id": 1,
    "userId": 1,
    "productsIds": 5
  },
  {
    "id": 1,
    "userId": 1,
    "productsIds": 6
  },
  {
    "id": 3,
    "userId": 2,
    "productsIds": 4
  },
  {
    "id": 2,
    "userId": 3,
    "productsIds": 3
  }
]

预期返回结构 #

[
  {
    "id": 1,
    "userId": 1,
    "productsIds": [2, 5, 6]
  },
  {
    "id": 3,
    "userId": 2,
    "productsIds": 4
  },
  {
    "id": 2,
    "userId": 3,
    "productsIds": 3
  }
]

实现方法 #

按订单维度分组，使用MySQL内置聚合函数将关联的商品ID合并为数组即可。

MySQL 5.7及以上版本（支持JSON函数） #

直接使用JSON_ARRAYAGG()函数聚合商品ID，查询结果直接返回标准JSON数组，完全匹配预期结构：

SELECT 
  Order.id, 
  Order.userId, 
  JSON_ARRAYAGG(Product.id) AS productsIds
FROM Orders AS Order
JOIN Products AS Product
  ON Order.id = Product.orderId
GROUP BY Order.id, Order.userId

MySQL 5.7以下版本（无JSON函数支持） #

使用GROUP_CONCAT()将商品ID拼接为逗号分隔的字符串，查询完成后在业务代码层将字符串切割转换为数组即可：

SELECT 
  Order.id, 
  Order.userId, 
  GROUP_CONCAT(Product.id) AS productsIds
FROM Orders AS Order
JOIN Products AS Product
  ON Order.id = Product.orderId
GROUP BY Order.id, Order.userId

该写法返回的productsIds字段值为类似2,5,6的字符串，业务侧按逗号分割后转为数字类型数组即可。

使用说明 #

• 分组字段必须覆盖SELECT语句中所有未做聚合的字段（即示例中的Order.id、Order.userId），如果MySQL开启了ONLY_FULL_GROUP_BY模式，漏写分组字段会触发语法报错。
• 如果需要排除重复的商品ID，可在聚合函数内添加DISTINCT关键字，例如JSON_ARRAYAGG(DISTINCT Product.id)。
• 如果需要对聚合数组内的商品ID排序，可在聚合函数内加ORDER BY子句，例如JSON_ARRAYAGG(Product.id ORDER BY Product.id ASC)。

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