👀 Views: 87 💬 Answers: 1 📅 Created: 2025-06-11

mariadb json database SQL

I'm migrating some code and I'm working with an unexpected behavior with JSON data in MariaDB 10.5.8. I have a table that stores various settings in a JSON column, and I'm trying to extract values from this column using the `JSON_UNQUOTE` function. However, when I run the following query, I get a NULL value instead of the expected result: ```sql SELECT JSON_UNQUOTE(settings->'$.theme') AS theme FROM user_preferences WHERE user_id = 123; ``` The `settings` column contains valid JSON data, but when I check the contents directly with: ```sql SELECT settings FROM user_preferences WHERE user_id = 123; ``` I can see that the JSON structure is correct, for example: ```json {"theme": "dark", "notifications": true} ``` I've also tried using `JSON_EXTRACT` instead: ```sql SELECT JSON_EXTRACT(settings, '$.theme') AS theme FROM user_preferences WHERE user_id = 123; ``` This returns the value as a JSON string, but the moment I add `JSON_UNQUOTE`, it returns NULL. I've verified that the column does not have NULL values, and the user ID definitely exists in the table. Is there an scenario with how `JSON_UNQUOTE` interacts with JSON data types in MariaDB? Could it be related to how the data is stored or an encoding scenario? Any insights or workarounds would be greatly appreciated! What's the best practice here?