MySQL: Strange Behavior with GROUP BY on JSON Columns in MySQL 8.0
I've been researching this but I've been banging my head against this for hours... I'm running into an scenario when trying to group results by a JSON column in MySQL 8.0. I have a table that stores user information, including a JSON column that contains various attributes. When I attempt to aggregate based on a specific attribute within the JSON, I get unexpected results. Here's the query I'm using: ```sql SELECT JSON_UNQUOTE(user_info->'$.role') AS user_role, COUNT(*) AS user_count FROM users GROUP BY user_role; ``` However, I noticed that I'm getting a count of users that seems to be incorrect. For instance, if I expect 10 users with a 'admin' role, it's returning 8. I checked the raw data and there are indeed 10 records with 'admin' in the JSON, but I'm not sure if the scenario is with how MySQL is interpreting the JSON data or if there's something wrong with my aggregation. I've also tried different JSON functions like `JSON_EXTRACT` and `JSON_VALUE`, but the results remain the same. Additionally, I've verified that there are no leading or trailing spaces in the JSON values. When I run a simple select without the `GROUP BY`, it returns the correct values: ```sql SELECT JSON_UNQUOTE(user_info->'$.role') AS user_role FROM users; ``` This returns all 10 records with 'admin'. I'm at a loss regarding why the grouping would yield fewer results. Any insights would be appreciated! I'm working on a CLI tool that needs to handle this. I'm on Windows 11 using the latest version of Sql. Has anyone else encountered this?