總和（JSON_EXTRACT）MariaDB 10.3.11

我找了這麼久，找不到任何正確的資訊 - 可以對 JSON 列中的值求和。

我在列中的值如下所示：

[{"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"
}]

表 = 表列 = 價格

我選擇提取值如下所示：

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

我有這個：

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

有沒有可能對這些值求和並得到：

|   QUALITY     |
|   ["248"]     |

解決此問題的另一種方法是使用自 MariaDB 10.2.2 起可用的遞歸公用表表達式。該解決方案不需要numbers表，而是遞歸迭代結果，直到 SUM 的計算完成。

我做了一個與您的數據類似的小例子：

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
);

現在檢索結果的實際查詢：

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;

自 MariaDB 10.3 起，此類函式尚不存在，但基本問題是 SUM() 需要一組行。通過回溯，我們可以看到理想的做法是將 JSON 數組["123", "125"]轉換為單獨的行。

Stack Overflow 上已經要求將逗號分隔的字元串拆分為行，並​​在許多地方進行了討論，但我發現的最佳描述來自 Periscope Data。

我對這個解決方案的唯一問題是它需要一個包含連續數字的數字表，直到我們將求和的最大值數。在這個 Stack Overflow 問題中可以找到許多創新的解決方案。

這是我想出的，也許你可以給我一些回饋：

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;

引用自：https://dba.stackexchange.com/questions/227312