Selecting the Sum of a JSON Property in MySQL

In this tutorial, you'll learn how to work with JSON data in MySQL, particularly how to select and sum values from a JSON property across multiple rows in a table. We’ll use random examples to illustrate this.

Prerequisites

• Basic understanding of MySQL queries.
• A MySQL database with a table that includes a JSON column.

Step 1: Setting Up the Example Table

Let's start by creating an example table that includes a JSON column:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255),
    order_details JSON
);

Step 2: Inserting Data into the Table

Now, let's insert some sample data into the orders table. Each row will contain an order with a JSON field for order_details, which includes properties like quantity, price, and product.

INSERT INTO orders (customer_name, order_details) VALUES
('Alice', '{"quantity": 3, "price": 19.99, "product": "T-shirt"}'),
('Bob', '{"quantity": 1, "price": 9.99, "product": "Cap"}'),
('Charlie', '{"quantity": 2, "price": 15.49, "product": "Mug"}');

Step 3: Selecting and Summing a JSON Property

Suppose you want to calculate the total quantity of products ordered by all customers. The quantity is stored within the JSON field order_details. You can extract this value and sum it across all rows using the following query:

SELECT SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.quantity')) AS UNSIGNED)) AS total_quantity
FROM orders;

Explanation:

• JSON_EXTRACT(order_details, '$.quantity'): Extracts the value of the quantity key from the JSON order_details column.
• JSON_UNQUOTE(...): Removes any quotes from the extracted JSON value.
• CAST(... AS UNSIGNED): Converts the extracted value to an unsigned integer to allow for summation.
• SUM(...): Sums all the quantities across the rows.

Step 4: Running the Query

When you run the above query, MySQL will return the total quantity of products ordered by all customers:

+---------------+
| total_quantity|
+---------------+
|            6  |
+---------------+

Additional Example: Summing Prices

Similarly, if you wanted to calculate the total revenue generated by summing the price field in the JSON column, you could write:

SELECT SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(order_details, '$.price')) AS DECIMAL(10,2))) AS total_revenue
FROM orders;

This will sum up the prices from each order and return the total revenue.

Summary

This quick tutorial demonstrated how to sum JSON properties in MySQL using the JSON_EXTRACT, JSON_UNQUOTE, and CAST functions. These powerful tools allow you to efficiently work with JSON data stored in your MySQL tables.

Conclusion

Now that you've learned how to sum JSON properties in MySQL, you can apply these techniques to your own data. Whether it's quantities, prices, or other numeric values stored within JSON fields, MySQL provides the functionality you need to handle JSON data effectively.