MySQL 8.0 - Difficulty with JSON_ARRAY and NULL values in grouped results
I'm working with MySQL 8.0 and I'm trying to create a JSON array from aggregated results using `JSON_ARRAY()`, but I'm running into issues when there are `NULL` values present in the dataset. My goal is to group the data by a `category` field and aggregate the `items` into a JSON array, while ensuring that `NULL` values in the `items` don't affect the final output. Here's a simplified version of my query: ```sql SELECT category, JSON_ARRAYAGG(item) AS items FROM my_table GROUP BY category; ``` However, when I run this, I get unexpected results where categories with `NULL` items are included in the result, but the JSON array for those categories is `NULL`. The following is the output I get: ``` +----------+----------------+ | category | items | +----------+----------------+ | A | ["item1"] | | B | NULL | | C | ["item2", NULL] | +----------+----------------+ ``` To handle the `NULL` values, I tried filtering them out using a `WHERE` clause like below: ```sql SELECT category, JSON_ARRAYAGG(item) AS items FROM my_table WHERE item IS NOT NULL GROUP BY category; ``` While this works for removing `NULL` items from the JSON array, it also excludes categories that only have `NULL` items altogether, which is not what I want. Is there a way to modify my query so that it retains categories even if they have only `NULL` items, but still omits `NULL` values from the JSON array itself? Any help would be greatly appreciated! My development environment is Ubuntu. Any ideas what could be causing this?