是否可以计算按json键分组的json值的总和？
Mysql版本是谷歌云sql上的5.7.17版本。
示例1:我的观点的一个简短示例：
col1 | col2
-----|-----------------------
aaa | {"key1": 1, "key2": 3}
-----|-----------------------
bbb | {"key1": 0, "key2": 2}
-----|-----------------------
aaa | {"key1": 50, "key2": 0}
SQL查询应生成：
col1 | col2
-----|-----------------------
aaa | {"key1": 51, "key2": 3}
-----|-----------------------
bbb | {"key1": 0, "key2": 2}
或
下面的任何一个模式都可能吗？
示例2:
col1 | col2
-----|-----------------------
aaa | {{"key_name" : "key1", "key_value" : 1}, {"key_name" : "key2", "key_value" : 3}}
-----|-----------------------
bbb | {{"key_name" : "key1", "key_value" : 0}, {"key_name" : "key2", "key_value" : 2}}
-----|-----------------------
aaa | {{"key_name" : "key1", "key_value" : 50}, {"key_name" : "key2", "key_value" : 0}}
示例3:
col1 | col2
-----|-----------------------
aaa | [{"key_name" : "key1", "key_value" : 1}, {"key_name" : "key2", "key_value" : 3}]
-----|-----------------------
bbb | [{"key_name" : "key1", "key_value" : 0}, {"key_name" : "key2", "key_value" : 2}]
-----|-----------------------
aaa | [{"key_name" : "key1", "key_value" : 50}, {"key_name" : "key2", "key_value" : 0}]
示例4:
col1 | col2
-----|-----------------------
aaa | {"key1": {"key_name" : "key1", "key_value" : 1}, "key2": {"key_name" : "key2", "key_value" : 3}}
-----|-----------------------
bbb | {"key1": {"key_name" : "key1", "key_value" : 0}, "key2": {"key_name" : "key2", "key_value" : 2}}
-----|-----------------------
aaa | {"key1": {"key_name" : "key1", "key_value" : 50}, "key2": {"key_name" : "key2", "key_value" : 0}}
我相信这样的方法是可行的。
SELECT SUM(col2->>"$.key1"), SUM(col2->>"$.key2") FROM your_table GROUP BY col1
TL；DR：是的，可以在不事先知道密钥名称的情况下完成，而且没有任何替代数据格式比原始数据格式有任何优势。
这可以在不事先知道关键名称的情况下完成，但这很痛苦。。。基本上，您必须查看表中的每个值，以确定表中不同键的集合，然后才能对它们求和。由于这一要求，以及备用数据格式每个条目都可以有多个键的事实，使用其中任何一个都没有好处。
由于你必须寻找所有不同的键，所以在寻找它们的同时求和也很容易。这个函数和过程一起可以做到这一点。函数json_merge_sum获取两个JSON值并将它们合并，将两个值中出现键的值相加，例如
SELECT json_sum_merge('{"key1": 1, "key2": 3}', '{"key3": 1, "key2": 2}')
输出：
{"key1": 1, "key2": 5, "key3": 1}
功能代码：
DELIMITER //
DROP FUNCTION IF EXISTS json_merge_sum //
CREATE FUNCTION json_sum_merge(IN j1 JSON, IN total JSON) RETURNS JSON
BEGIN
DECLARE knum INT DEFAULT 0;
DECLARE jkeys JSON DEFAULT JSON_KEYS(j1);
DECLARE kpath VARCHAR(20);
DECLARE v INT;
DECLARE l INT DEFAULT JSON_LENGTH(jkeys);
kloop: LOOP
IF knum >= l THEN
LEAVE kloop;
END IF;
SET kpath = CONCAT('$.', JSON_EXTRACT(jkeys, CONCAT('$[', knum, ']')));
SET v = JSON_EXTRACT(j1, kpath);
IF JSON_CONTAINS_PATH(total, 'one', kpath) THEN
SET total = JSON_REPLACE(total, kpath, JSON_EXTRACT(total, kpath) + v);
ELSE
SET total = JSON_SET(total, kpath, v);
END IF;
SET knum = knum + 1;
END LOOP kloop;
RETURN total;
END
该过程count_keys执行相当于GROUP BY子句的操作。它在表中查找col1的所有不同值，然后为具有该值col1的每一行调用json_sum_merge。请注意，行选择查询执行SELECT ... INTO—一个伪变量，因此不会生成输出，并使用MIN()确保只有一个结果(以便可以将其分配给变量)。
程序：
DELIMITER //
DROP PROCEDURE IF EXISTS count_keys //
CREATE PROCEDURE count_keys()
BEGIN
DECLARE finished INT DEFAULT 0;
DECLARE col1val VARCHAR(20);
DECLARE col1_cursor CURSOR FOR SELECT DISTINCT col1 FROM table2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;
OPEN col1_cursor;
col1_loop: LOOP
FETCH col1_cursor INTO col1val;
IF finished=1 THEN
LEAVE col1_loop;
END IF;
SET @total = '{}';
SET @query = CONCAT("SELECT MIN(@total:=json_sum_merge(col2, @total)) INTO @json FROM table2 WHERE col1='", col1val, "'");
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT col1val AS col1, @total AS col2;
END LOOP col1_loop;
END
举个稍大的例子：
col1 col2
aaa {"key1": 1, "key2": 3}
bbb {"key1": 4, "key2": 2}
aaa {"key1": 50, "key3": 0}
ccc {"key2": 5, "key3": 1, "key4": 3}
bbb {"key1": 5, "key2": 1, "key5": 3}
CALL count_keys()产生：
col1 col2
aaa {"key1": 51, "key2": 3, "key3": 0}
bbb {"key1": 9, "key2": 3, "key5": 3}
ccc {"key2": 5, "key3": 1, "key4": 3}
注意，我在过程中调用了表table2，您需要对其进行编辑(在两个查询中)以适应。
"简短示例"的SQL：
SELECT col1,
JSON_OBJECT('key1', SUM(value1), 'key2', SUM(value2)) AS col2
FROM
(SELECT col1,
JSON_EXTRACT(col2, '$.key1') AS value1,
JSON_EXTRACT(col2, '$.key2') AS value2
FROM tbl) subq
GROUP BY col1;
示例3:的解决方案
DROP TABLE IF EXISTS jsondata;
CREATE TABLE jsondata (json JSON, col varchar(11));
INSERT INTO jsondata VALUES
('[{"key_name" : "key1", "key_value" : 1}, {"key_name" : "key2", "key_value" : 3}]', 'aaa'),
('[{"key_name" : "key1", "key_value" : 0}, {"key_name" : "key3", "key_value" : 2}]', 'bbb'),
('[{"key_name" : "key1", "key_value" : 50}, {"key_name" : "key2", "key_value" : 0}]', 'aaa');
DROP FUNCTION IF EXISTS json_sum_by_col;
CREATE FUNCTION json_sum_by_col(col varchar(100)) RETURNS JSON
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE select_values JSON;
DECLARE temp_result JSON;
DECLARE json_result JSON DEFAULT '[]';
DECLARE temp_key varchar(11);
DECLARE temp_value int;
DECLARE curs CURSOR FOR SELECT json FROM jsondata WHERE jsondata.col = col;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN curs;
read_loop: LOOP
SET i = 0;
FETCH curs INTO select_values;
IF done THEN
LEAVE read_loop;
END IF;
WHILE i < JSON_LENGTH(select_values) DO
-- extract key and value for i element
SET temp_key = JSON_EXTRACT(JSON_EXTRACT(select_values, CONCAT('$[',i,']')), '$.key_name');
SET temp_value = JSON_EXTRACT(JSON_EXTRACT(select_values, CONCAT('$[',i,']')), '$.key_value');
-- search json_result for key
SET @search = JSON_SEARCH(json_result, 'one', JSON_UNQUOTE(temp_key));
IF @search IS NOT NULL THEN
-- if exists add to existing value
SET @value_path = JSON_UNQUOTE(REPLACE(@search, 'name', 'value'));
SET temp_value = temp_value + JSON_EXTRACT(json_result, @value_path);
SET json_result = JSON_REPLACE(json_result, @value_path, temp_value);
ELSE
-- else attach it to json_result
SET temp_result = JSON_OBJECT("key_name", JSON_UNQUOTE(temp_key), "key_value", temp_value);
SET json_result = JSON_INSERT(json_result, CONCAT('$[',JSON_LENGTH(json_result),']'), temp_result);
END IF;
SELECT i + 1 INTO i;
END WHILE;
END LOOP;
CLOSE curs;
RETURN json_result;
END;
SELECT col, json_sum_by_col(col) FROM jsondata GROUP BY col;
你可以在这里运行