Sum(JSON_EXTRACT) MariaDB 10.3.11

Question

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"]     |

Answer 1

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;

Answer 2

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;