Sum(JSON_EXTRACT) MariaDB 10.3.11

Another way to solve this would be by using Recursive Common Table Expressions which is available since MariaDB 10.2.2. This solution does not need a numbers table but recursively iterates the result until the computation of the SUM is complete.

I made a small example which is similar to your data:

CREATE TABLE test_json ( prices JSON NOT NULL, employee_id INT ); INSERT INTO test_json (prices, employee_id) VALUES ( '[{"PRICE":"123"},{"PRICE":"125"}]', 10060 ), ( '[{"PRICE":"5"},{"PRICE":"10"},{"PRICE":"15"},{"PRICE":"20"}]', 10070 ); 

Now the actual query to retrieve the result:

WITH RECURSIVE json_sum (price_sum, prices) AS ( -- select all entries you need to calculate the sum for, initialize sum as 0 SELECT 0 AS price_sum, JSON_EXTRACT(prices, '$[*].PRICE') AS prices FROM `test_json` WHERE employee_id = 10060 -- this is your original WHERE condition from your question UNION SELECT -- add the value of the first entry in price array to the sum price_sum + JSON_UNQUOTE(JSON_EXTRACT(json_sum.prices, '$[0]')) AS price_sum, -- remove the first entry from the price array, so the next iteration will not add it again JSON_REMOVE(json_sum.prices, '$[0]') AS prices FROM json_sum WHERE JSON_LENGTH(json_sum.prices) > 0 ) -- select all sums, filtering out the rows where calculation is incomplete SELECT price_sum AS quality FROM json_sum WHERE JSON_LENGTH(json_sum.prices) = 0; 

Such functions do not exist yet as of MariaDB 10.3, however the basic problem is that SUM() requires a group of rows. By working back from this, we can see that the ideal would be to convert the JSON array ["123", "125"] into individual rows.

Splitting a comma-separated string into rows has been asked for on Stack Overflow, and discussed in a number of places, but the best description I found came from Periscope Data.

The only problem I have with this solution is that it requires a numbers table with consecutive numbers up to the maximum number of values we will be summing. A number of innovative solutions to that can be found in this Stack Overflow question.

Here's what I've come up with, perhaps you can give me some feedback:

SELECT SUM(price) AS total FROM( WITH mycte AS (SELECT TRIM(TRAILING ']' FROM TRIM(LEADING '[' FROM JSON_EXTRACT(prices))) AS prices_extracted) SELECT TRIM(BOTH '"' FROM TRIM(SUBSTRING_INDEX( SUBSTRING_INDEX(prices, ',', n), ',', -1 ))) AS PRICE FROM mycte JOIN numbers ON CHAR_LENGTH(prices_extracted) - CHAR_LENGTH(REPLACE(prices_extracted, ',', '')) >= n - 1 ) derived_table;