MySQL 8.0: how to to filter results with JSON_CONTAINS and WHERE clause in complex query
I'm working through a tutorial and I'm updating my dependencies and I've looked through the documentation and I'm still confused about I'm having trouble using `JSON_CONTAINS` in my MySQL 8.0 query. I have a table named `orders` that contains a column `order_details`, which is of type JSON. The `order_details` column may contain multiple items, and I want to filter results based on whether a specific item exists in this JSON array. However, my query doesn't return the expected results, and I'm not sure why. I tried the following query: ```sql SELECT order_id, order_details FROM orders WHERE JSON_CONTAINS(order_details, '{"item":"widget"}', '$.items'); ``` The structure of the JSON in `order_details` looks something like this: ```json { "items": [ { "item": "widget", "quantity": 2 }, { "item": "gadget", "quantity": 1 } ], "total": 100 } ``` I expected this query to return all orders that contain the item 'widget', but it's returning an empty result set. I also checked the JSON structure and confirmed it matches the structure I'm querying against. Additionally, if I run this simpler query: ```sql SELECT order_id, order_details FROM orders WHERE JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.items[0].item')) = 'widget'; ``` I get results, but it only checks the first item in the array, which isn't what I want. I've looked at the documentation and it appears that `JSON_CONTAINS` should work. Could there be something wrong with the path expression `$.items`? Or is there something else I'm missing in my query? Any help in debugging this would be greatly appreciated! How would you solve this? I recently upgraded to Sql 3.9. Any feedback is welcome! I'm working on a CLI tool that needs to handle this.