MySQL: Unexpected behavior with JSON_ARRAY and JSON_EXTRACT in complex queries
I'm following best practices but I've been struggling with this for a few days now and could really use some help... I'm sure I'm missing something obvious here, but I'm encountering an unexpected behavior when using `JSON_ARRAY` and `JSON_EXTRACT` in MySQL 8.0... My query is designed to insert records with a JSON structure, but the resulting JSON doesn't reflect what I expect. I have a table defined as: ```sql CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_data JSON ); ``` Then, I attempt to insert data using a JSON array: ```sql INSERT INTO orders (customer_id, order_data) VALUES ( 1, JSON_ARRAY( JSON_OBJECT('item', 'Widget', 'quantity', 3), JSON_OBJECT('item', 'Gadget', 'quantity', 5) ) ); ``` When I run a query to extract the `quantity` of each item from `order_data`: ```sql SELECT JSON_EXTRACT(order_data, '$[*].quantity') AS quantities FROM orders; ``` I expect to get an array `[3, 5]`, but instead, I'm seeing a result like this: ```json "[3, 5]" ``` It seems that the output is a string rather than an actual JSON array. I've tried using `CAST` to convert it, but it doesn't help. I also verified that the `order_data` is stored correctly by querying it directly: ```sql SELECT order_data FROM orders; ``` This returns the correct JSON structure, so I'm puzzled about why the extraction doesn't yield the intended format. I've also looked into potential issues with MySQL configurations or compatibility modes, but everything seems to be set correctly for JSON handling. Any insights on why my query results in a string instead of a JSON array? Is this a known limitation or an issue with my query syntax? I'm developing on Ubuntu 22.04 with Sql. Is there a simpler solution I'm overlooking? I'd love to hear your thoughts on this.