title: MySQL 8 - sorting and filtering in JSON_ARRAYAGG
body: <p>Recently I installed MySQL 8.0.11 GA and wanted to optimize some views, functions,...
  Usually I have something like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>WIth MySQL 5.7 I had to do something like</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And now with MySQL 8.0.11 GA released I thought about using <a href="https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_json-arrayagg" rel="noreferrer">JSON_ARRAYAGG()</a> 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()?</p>
  
  <p>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,...</p>