👀 Views: 93 💬 Answers: 1 📅 Created: 2025-06-07

mysql json json-search sql

I'm relatively new to this, so bear with me... I'm reviewing some code and 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 working with an scenario when trying to use `JSON_SEARCH` on a JSON column in MySQL 8.0. I have a table `orders` that contains a JSON column `order_details` which stores the order items. The JSON structure looks something like this: ```json { "items": [ { "product_id": 1, "quantity": 2 }, { "product_id": 2, "quantity": 1 } ], "total": 300 } ``` I want to find orders that contain a specific `product_id`, say `1`. I wrote the following query: ```sql SELECT * FROM orders WHERE JSON_SEARCH(order_details, 'one', '1', NULL, '$.items[*].product_id') IS NOT NULL; ``` However, I'm getting an behavior message: `Invalid JSON path expression`. I've double-checked the JSON structure and I'm confident that `product_id` exists in the data. I've also tried modifying the path expression to see if that resolves the scenario, but nothing seems to work. In addition, I attempted to use `JSON_CONTAINS` as follows: ```sql SELECT * FROM orders WHERE JSON_CONTAINS(order_details, '1', '$.items[*].product_id'); ``` But that returned an empty result set. I verified that those orders indeed contain a product with `product_id` 1. Any advice on how I can correctly search the JSON data and why I'm getting that invalid path behavior? My development environment is macOS. Is there a better approach? For context: I'm using Sql on Windows. Any help would be greatly appreciated! Could this be a known issue? The project is a service built with Sql. Hoping someone can shed some light on this.