👀 Views: 20 💬 Answers: 1 📅 Created: 2025-06-14

mysql json aggregation sql

I'm migrating some code and I'm dealing with I'm a bit lost with I'm not sure how to approach I'm sure I'm missing something obvious here, but I'm working with an unexpected scenario with MySQL 8.0 where I'm trying to aggregate JSON data that includes NULL values... I have a table called `orders` that contains a column `order_data`, which is a JSON type. When I try to extract values from this JSON and aggregate them, the NULL values seem to interfere with my results. Here's a simplified version of my query: ```sql SELECT customer_id, SUM(JSON_EXTRACT(order_data, '$.amount')) AS total_amount FROM orders GROUP BY customer_id; ``` When `order_data` contains NULL for some rows, I expected those to be ignored in the aggregation. However, I’m getting an output that includes NULL in the `total_amount`, which isn’t what I intended. The result looks like this: | customer_id | total_amount | |-------------|--------------| | 1 | NULL | | 2 | 150.00 | | 3 | NULL | I've tried using `IFNULL` and `COALESCE` to replace NULL values but it doesn't seem to resolve the scenario. For example, I modified my query to: ```sql SELECT customer_id, SUM(IFNULL(JSON_EXTRACT(order_data, '$.amount'), 0)) AS total_amount FROM orders GROUP BY customer_id; ``` This still results in NULL for customer_id 1. Just to clarify, the JSON structure looks like this: - `{ "amount": 100 }` - `{ "amount": NULL }` - `{ "amount": 50 }` Can anyone guide to understand why I’m seeing NULL in my results and how I can handle this correctly? I need to ensure that if all values for `customer_id` are NULL, it should return 0 instead of NULL. How would you solve this? For context: I'm using Sql on Ubuntu. What's the best practice here? I'm using Sql 3.10 in this project. What are your experiences with this? This issue appeared after updating to Sql 3.10. Could this be a known issue? My development environment is Windows 10. I'm on Ubuntu 22.04 using the latest version of Sql.