Description:
The JSON_ARRAYAGG function uses a standard SQL name, but doesn't apply the NULL handling as mandated by the standard.
Note that this is not about the absence of the <JSON constructor null clause> clause in MySQL, but that MySQL uses "NULL ON NULL" instead of the standard-mandated "ABSENT ON NULL" handing in lack of an explicit <JSON constructor null clause>.
Example:
SELECT JSON_ARRAYAGG(c)
FROM (SELECT 1 c
UNION ALL
SELECT null c) t
+------------------+
| JSON_ARRAYAGG(c) |
+------------------+
| [1, null] |
+------------------+
As per the standard, the result should be "[1]" because NULL values are removed.
Whether or not you want to comply with the SQL standard is really up to you, but IMHO you should not diverge from standard behaviour if you are using standard function names.
SQL-2:2016 10.11 SR 5a says for JSON_ARRAYAGG "If <JSON constructor null clause> is not specified, then ABSENT ON NULL is implicit."
SQL-2:2016 10.11 GR 5 g ii 2 removes the NULL values from the result if <JSON constructor null clause> is other than NULL ON NULL.
How to repeat:
Run the above query.
Description: The JSON_ARRAYAGG function uses a standard SQL name, but doesn't apply the NULL handling as mandated by the standard. Note that this is not about the absence of the <JSON constructor null clause> clause in MySQL, but that MySQL uses "NULL ON NULL" instead of the standard-mandated "ABSENT ON NULL" handing in lack of an explicit <JSON constructor null clause>. Example: SELECT JSON_ARRAYAGG(c) FROM (SELECT 1 c UNION ALL SELECT null c) t +------------------+ | JSON_ARRAYAGG(c) | +------------------+ | [1, null] | +------------------+ As per the standard, the result should be "[1]" because NULL values are removed. Whether or not you want to comply with the SQL standard is really up to you, but IMHO you should not diverge from standard behaviour if you are using standard function names. SQL-2:2016 10.11 SR 5a says for JSON_ARRAYAGG "If <JSON constructor null clause> is not specified, then ABSENT ON NULL is implicit." SQL-2:2016 10.11 GR 5 g ii 2 removes the NULL values from the result if <JSON constructor null clause> is other than NULL ON NULL. How to repeat: Run the above query.