MySQL 8.0: implementing JSON_UNQUOTE and nested JSON arrays when filtering
I'm maintaining legacy code that I'm experiencing unexpected behavior when trying to filter records based on values within a nested JSON array using `JSON_UNQUOTE` in MySQL 8.0... I have a table called `orders` that includes a column named `order_details`, which stores JSON data. The structure of the JSON is as follows: ```json { "items": [ { "product_id": 1, "quantity": 2 }, { "product_id": 2, "quantity": 1 } ], "total": 100 } ``` I want to retrieve all orders where one of the items has a `product_id` of 2. My query looks like this: ```sql SELECT * FROM orders WHERE JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.items[*].product_id')) = '2'; ``` However, this query returns no results, even though I have orders that should match. I've also tried using `JSON_CONTAINS` like this: ```sql SELECT * FROM orders WHERE JSON_CONTAINS(order_details, '{"product_id": 2}', '$.items'); ``` This query also returns no rows. I've verified that my JSON data is valid and contains the expected values. I've also checked for data type mismatches and ensured that there are no trailing spaces in the `product_id`. Additionally, I'm aware that `JSON_UNQUOTE` is used for extracting string values, but I am unsure how to properly handle nested arrays within a JSON structure. Is there a more effective way to filter through nested JSON arrays in MySQL, or am I missing something in my current approach? Any guidance or suggestions would be greatly appreciated. For reference, this is a production desktop app. Cheers for any assistance!