👀 Views: 273 💬 Answers: 1 📅 Created: 2025-06-12

mysql json aggregate-functions sql

I can't seem to get I'm experiencing unexpected behavior when trying to use aggregate functions on columns defined as JSON in MySQL 5.7. I have a table `orders` with the following structure: ```sql CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_data JSON ); ``` I am trying to retrieve the count of orders for each customer along with the total value of their orders stored in the `order_data` JSON field, which contains a `value` key. My query looks like this: ```sql SELECT customer_id, COUNT(*) AS order_count, SUM(JSON_UNQUOTE(order_data->'$.value')) AS total_value FROM orders GROUP BY customer_id; ``` However, I am getting the following behavior message: ``` behavior Code: 1411. Invalid JSON path expression. ``` I've checked that the `value` key exists in all rows, but I suspect it might be due to some rows containing `NULL` values in the `order_data`. When I run the query without the `SUM` part, it runs successfully. I've also tried using `IFNULL` to handle potential `NULL` values: ```sql SELECT customer_id, COUNT(*) AS order_count, SUM(IFNULL(JSON_UNQUOTE(order_data->'$.value'), 0)) AS total_value FROM orders GROUP BY customer_id; ``` This still results in the same behavior. I've verified that the JSON structure is valid by checking it manually and through other queries. Is there a best practice for aggregating JSON values without running into these issues? Also, is there a specific reason why the `SUM` function behaves differently when applied to a JSON column? Any help would be appreciated! What's the correct way to implement this?