Posts tagged with 'sum JSON in MySQL'

Found 1 posts tagged with 'sum JSON in MySQL'.

Selecting the Sum of a JSON Property in MySQL

Tutorial August 17, 2024
json mysql

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.

Read More