[SOLVED] MySQL 8 - sorting and filtering in JSON

MySQL 8 - sorting and filtering in JSON_ARRAYAGG

Recently I installed MySQL 8.0.11 GA and wanted to optimize some views, functions,... Usually I have something like this:

CREATE TABLE `PublicHoliday` (
  `PublicHoliday_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `PublicHoliday_Name` varchar(45) NOT NULL,
  `CompanyGroup_ID` int(2) unsigned NOT NULL,
  `Holiday` date NOT NULL,
  `State_ID` int(2) unsigned NOT NULL,
  PRIMARY KEY (`PublicHoliday_ID`),
  UNIQUE KEY `PublicHoliday_Comb` (`CompanyGroup_ID`,`Holiday`,`PublicHoliday_Name`,`State_ID`),
  CONSTRAINT `CompanyGroup_PublicHoliday` FOREIGN KEY (`CompanyGroup_ID`) REFERENCES `CompanyGroup` (`CompanyGroup_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

WIth MySQL 5.7 I had to do something like

SELECT      CompanyGroup_ID,
            CAST(CONCAT('[',(
                GROUP_CONCAT(
                    DISTINCT JSON_OBJECT(
                        'Holiday', Holiday,
                        'PublicHoliday_Name', PublicHoliday_Name,
                        'PublicHoliday_ID', PublicHoliday_ID
                    )
                    ORDER BY Holiday ASC
                )
            ),']') AS JSON) AS HolidayArr
FROM        CompanyGroup
LEFT JOIN   Holiday
    USING(CompanyGroup_ID)
GROUP BY    CompanyGroup_ID

And now with MySQL 8.0.11 GA released I thought about using JSON_ARRAYAGG() instead of the entire cast + concat + group_concat but I can't figure out how to set an order or how to ignore duplicate items. Does anyone know if there is a way to achieve any sorting or filtering within the JSON_ARRAYAGG()?

BTW: I know it's possible to achieve that in rust, php, ... but I want to achieve this in SQL to be able to use this within procedures, functions, events, triggers, views,...

Solution

Since MySQL 8.0.14, JSON_ARRAYAGG() and other JSON-functions are usable in window-functions, where an order may be specified.