如何用JSON_ARRAYAGG查询订单及订单项，空订单项显示空数组

核心问题原因 #

使用JSON_ARRAYAGG聚合订单项时，当订单没有对应订单项，该函数会返回null而非空数组[]，需要通过函数替换实现预期效果。

方法1：用COALESCE包裹JSON_ARRAYAGG（推荐） #

通过LEFT JOIN保留所有订单，再用COALESCE将JSON_ARRAYAGG返回的null替换为JSON_ARRAY()生成的空数组。

示例SQL：

SELECT
  o.order_id,
  o.order_no,
  o.create_time,
  COALESCE(
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'item_id', oi.item_id,
        'product_name', oi.product_name,
        'quantity', oi.quantity,
        'price', oi.price
      )
    ),
    JSON_ARRAY()
  ) AS order_items
FROM Orders o
LEFT JOIN OrderItems oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_no, o.create_time;

方法2：关联子查询内处理空值 #

针对每个订单单独查询订单项，在子查询中完成null到空数组的替换，适合需要生成完整单条JSON对象的场景。

示例SQL：

SELECT
  JSON_OBJECT(
    'order_id', o.order_id,
    'order_no', o.order_no,
    'create_time', o.create_time,
    'order_items', (
      SELECT COALESCE(
        JSON_ARRAYAGG(
          JSON_OBJECT(
            'item_id', oi.item_id,
            'product_name', oi.product_name,
            'quantity', oi.quantity,
            'price', oi.price
          )
        ),
        JSON_ARRAY()
      )
      FROM OrderItems oi
      WHERE oi.order_id = o.order_id
    )
  ) AS order_json
FROM Orders o;

方法3：兼容旧版本MySQL（5.7.22以下） #

如果你的MySQL版本不支持JSON_ARRAYAGG，可以用COUNT判断是否存在订单项，结合GROUP_CONCAT手动构造JSON数组：

示例SQL：

SELECT
  o.order_id,
  o.order_no,
  o.create_time,
  IF(
    COUNT(oi.item_id) = 0,
    '[]',
    CONCAT('[', GROUP_CONCAT(
      JSON_OBJECT(
        'item_id', oi.item_id,
        'product_name', oi.product_name,
        'quantity', oi.quantity,
        'price', oi.price
      )
    ), ']')
  ) AS order_items
FROM Orders o
LEFT JOIN OrderItems oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_no, o.create_time;

注意：此方法需要确保GROUP_CONCAT的长度足够容纳所有订单项数据，必要时调整group_concat_max_len参数。

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