MySQL 8.0: implementing JSON_UNQUOTE and JSON arrays returning NULL unexpectedly
I'm a bit lost with I'm experiencing an scenario when trying to extract values from a JSON array in MySQL 8.0. Specifically, I'm using the `JSON_UNQUOTE` function to retrieve individual elements from a JSON array, but it seems to return NULL for certain cases. Here’s a simplified version of my table and data: ```sql CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), preferences JSON ); INSERT INTO users (id, name, preferences) VALUES (1, 'Alice', '["dark_mode", "notifications"]'), (2, 'Bob', 'null'), (3, 'Charlie', '[]'); ``` Now, I want to get the first preference of each user. I wrote the following query: ```sql SELECT name, JSON_UNQUOTE(JSON_EXTRACT(preferences, '$[0]')) AS first_preference FROM users; ``` When I execute this, I get the following result: | name | first_preference | |---------|------------------| | Alice | dark_mode | | Bob | NULL | | Charlie | NULL | I expected `first_preference` for Bob and Charlie to return NULL since Bob's preferences are `null` and Charlie's are an empty array. However, I’m surprised to see that JSON_UNQUOTE is not just returning NULL but also not even throwing an behavior. I’ve checked the JSON data types and they seem correct. I also tried using `COALESCE` to handle potential NULL values, but it didn’t change the outcome: ```sql SELECT name, COALESCE(JSON_UNQUOTE(JSON_EXTRACT(preferences, '$[0]')), 'no preference') AS first_preference FROM users; ``` This still returns NULL for Bob and Charlie. Am I misusing `JSON_UNQUOTE` or `JSON_EXTRACT`, or is there something else going on with how MySQL handles JSON types? I’ve combed through the official MySQL documentation, but I’m still at a loss. Any insights would be greatly appreciated! I'm working on a desktop app that needs to handle this.