Mariadb – Sum(JSON_EXTRACT) MariaDB 10.3.11

jsonmariadbsum

I'm looking so long, and cannot find anything right concerning – ho to sum the values from JSON column.

My values in the column looks like this:

[{"file":"FILE.doc",
  "POSITION":"3245",
  "NUMBER":"2345",
  "PRICE":"123",
  "PRICE2":"3456",
  "NUMBER":"2019-01-25",
  "date":"2019\/01\/15",
  "added_by":"admin"
 },{
  "file":"FILE.doc",
  "POSITION":"345",
  "numer_faktury":"456",
  "PRICE":"125",
  "PRICE2":"567",
  "DATE":"2019-01-11",
  "DATE":"2019\/01\/15",
  "added_by":"admin"
}]

Table = table
column = prices

My select to extract the values looks as follow:

SELECT JSON_EXTRACT(prices, 
    '$[*].PRICE') as quality FROM table
    WHERE employee_id = 10060

And I've got this:

|  QUALITY   |
["123", "125"]

Is there any possible to sum the values and get:

|   QUALITY     |
|   ["248"]     |

Best Answer

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;
Related Question