1

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

2 Answers 2

4

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;
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;
1
  • 1
    Re: the numbers table: Maybe you can use the MariaDB sequence storage engine to get the numbers. It's also possible to generate numbers on-the-fly with a recursive CTE, see e.g. my answer here.
    – dbdemon
    Aug 28, 2019 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.