MySQL 8.4中如何按原JSON数组顺序排序聚合结果？

表结构 #

• media表：

  media_id	media_url	etc.
  3	image3.jpg	...
  6	image6.jpg	...
  1	image1.jpg	...

• products表：

  product_id	product_title	product_media_ids	etc.
  ...	...	[3,6,1]	...

product_media_ids是存储ID数组的JSON字段，需求是查询时获取与该数组顺序完全一致的media_url数组（示例期望结果：['image3.jpg', 'image6.jpg', 'image1.jpg']）。

现有查询能获取关联数据，但无法保留原数组顺序：

SELECT
P.*,
  (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', M2.media_id, 'url', M2.media_url))
  FROM media AS M2
  WHERE JSON_CONTAINS(P.product_media_ids, CAST(M2.media_id AS JSON), '$')
  ) AS image_array
FROM products AS P;

解决方案（MySQL 8.4.4适用） #

利用JSON_TABLE将JSON数组拆分为带原始索引位置的行数据，关联media表后按索引排序再聚合，即可保留原顺序：

SELECT
  P.*,
  (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', M.media_id, 'url', M.media_url))
   FROM JSON_TABLE(
     P.product_media_ids,
     '$[*]' COLUMNS(
       idx FOR ORDINALITY,
       media_id INT PATH '$'
     )
   ) AS JM
   JOIN media M ON JM.media_id = M.media_id
   ORDER BY JM.idx
  ) AS image_array
FROM products AS P;

核心逻辑说明 #

• FOR ORDINALITY会生成数组元素的原始位置索引（从1开始），这是维持顺序的关键。
• 按索引排序后再用JSON_ARRAYAGG聚合，最终生成的JSON数组顺序将与product_media_ids完全一致。
• 写法简洁，适配MySQL 8.0及以上版本，完全匹配需求。

内容的提问来源于stack exchange，提问作者Ben in CA