MySQL 8中JSON_ARRAYAGG的排序过滤及视图函数优化技术咨询

嘿，针对你在MySQL 8.0.11里对JSON_ARRAYAGG做排序和过滤的需求，我结合你提供的PublicHoliday表，整理了适配当前版本的具体实现方案：

一、先过滤数据再生成JSON数组 #

你的MySQL版本是8.0.11，还不支持FILTER子句（该特性在8.0.13才引入），咱们可以通过两种方式实现过滤：

1. 全局过滤（WHERE子句） #

如果要对整个数据集先过滤再分组聚合，直接在WHERE中添加条件即可。比如只统计State_ID=1的假期，按CompanyGroup_ID分组生成假期名称的JSON数组：

SELECT 
  CompanyGroup_ID,
  JSON_ARRAYAGG(PublicHoliday_Name) AS holiday_names
FROM PublicHoliday
WHERE State_ID = 1
GROUP BY CompanyGroup_ID;

2. 分组内过滤（CASE表达式） #

如果需要在分组内只聚合符合特定条件的数据，用CASE把不符合条件的字段转为NULL——JSON_ARRAYAGG会自动忽略NULL值。比如每个分组里只保留名称包含"春节"的假期：

SELECT 
  CompanyGroup_ID,
  JSON_ARRAYAGG(
    CASE WHEN PublicHoliday_Name LIKE '%春节%' THEN PublicHoliday_Name ELSE NULL END
  ) AS spring_festival_holidays
FROM PublicHoliday
GROUP BY CompanyGroup_ID;

二、对JSON_ARRAYAGG的结果排序 #

MySQL 8.0开始，JSON_ARRAYAGG支持两种排序方式，都能满足需求：

1. 函数内直接排序 #

直接在JSON_ARRAYAGG内部添加ORDER BY子句，指定聚合时的排序规则。比如按CompanyGroup_ID分组，生成按Holiday日期倒序排列的假期名称数组：

SELECT 
  CompanyGroup_ID,
  JSON_ARRAYAGG(PublicHoliday_Name ORDER BY Holiday DESC) AS sorted_holiday_names
FROM PublicHoliday
GROUP BY CompanyGroup_ID;

2. 子查询预排序后聚合 #

如果逻辑更复杂，可先在子查询里完成数据排序，再用JSON_ARRAYAGG聚合：

SELECT 
  CompanyGroup_ID,
  JSON_ARRAYAGG(PublicHoliday_Name) AS sorted_holiday_names
FROM (
  SELECT CompanyGroup_ID, PublicHoliday_Name
  FROM PublicHoliday
  ORDER BY CompanyGroup_ID, Holiday DESC
) AS sorted_data
GROUP BY CompanyGroup_ID;

三、过滤+排序结合使用 #

把两种方法结合，就能实现先过滤再排序的聚合效果。比如每个分组只取State_ID=2的假期，且按Holiday正序排列：

SELECT 
  CompanyGroup_ID,
  JSON_ARRAYAGG(
    CASE WHEN State_ID = 2 THEN PublicHoliday_Name ELSE NULL END
    ORDER BY Holiday ASC
  ) AS filtered_sorted_holidays
FROM PublicHoliday
GROUP BY CompanyGroup_ID;

或者用全局过滤+函数内排序的简洁写法：

SELECT 
  CompanyGroup_ID,
  JSON_ARRAYAGG(PublicHoliday_Name ORDER BY Holiday ASC) AS filtered_sorted_holidays
FROM PublicHoliday
WHERE State_ID = 2
GROUP BY CompanyGroup_ID;

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