是否可以计算按 json 键分组的 json 值的总和？

谷歌云sql上的Mysql版本是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}}

最佳答案

TL;DR:是的，它可以在事先不知道 key 名称的情况下完成，并且没有任何替代数据格式比原始数据格式有任何优势。

这可以在事先不知道键名的情况下完成，但这很痛苦......基本上你必须查看表中的每个值以确定表中不同键的集合，然后才能对它们求和。由于这个要求，以及备用数据格式每个条目都可以有多个键的事实，使用其中任何一个都没有优势。

由于您必须寻找所有不同的键，因此在寻找它们的同时进行求和也很容易。这个函数和过程一起可以做到这一点。 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，您需要对其进行编辑(在两个查询中)以适应。

关于MySQL SUM json 值按 json 键分组，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/41650221/