👀 Views: 78 💬 Answers: 1 📅 Created: 2025-07-03

mysql json aggregate-functions SQL

Quick question that's been bugging me - I'm working on a project and hit a roadblock... I'm refactoring my project and I'm working with an unexpected behavior with aggregate functions in MySQL 8.0 when dealing with JSON fields that contain NULL values. I have a table named `orders` where one of the columns, `order_details`, is of type JSON. I'm trying to calculate the total quantity of items ordered by summing up a field within the JSON structure. However, when there are NULL values in the JSON objects, the aggregation seems to ignore these rows entirely, leading to incorrect totals. Here's the query I'm using: ```sql SELECT SUM(JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.quantity'))) AS total_quantity FROM orders; ``` In some cases, if an order doesn't have a `quantity` key or it’s set to NULL, I expect it to contribute a 0 to the total, as I want to include all orders in my count. However, instead, those orders are completely ignored in the aggregation. I've tried using COALESCE to handle the NULL values like so: ```sql SELECT SUM(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.quantity')), 0)) AS total_quantity FROM orders; ``` However, I'm still seeing discrepancies in my totals. The actual data structure looks like this: ```json { "items": [ {"name": "Widget A", "quantity": 2}, {"name": "Widget B", "quantity": null}, {"name": "Widget C"} ] } ``` I need to ensure that all rows are accounted for in my aggregation. Is there a best practice for handling NULLs in JSON fields for aggregations in MySQL, or is there something I'm missing in my query? My team is using Sql for this mobile app. Has anyone dealt with something similar? I'm on Windows 10 using the latest version of Sql. Thanks in advance!