MySQL自定义函数实现JSON数据的聚合

1
2

{k1: v1, k2: v2}
{k1: v3, k2: v4, k3: v6}

1

{k1: v1+v3, k2: v2+v4, k3: v6}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

DROP FUNCTION IF EXISTS sumjson;
DELIMITER //
CREATE FUNCTION sumjson(json text)
RETURNS VARCHAR(255)
BEGIN
  -- sumjson 用于实现对json格式数据的聚合

  DECLARE i int DEFAULT 0;
  DECLARE idx int DEFAULT 0;
  DECLARE len int DEFAULT 0;
  DECLARE item VARCHAR(50) DEFAULT '';
  DECLARE k VARCHAR(50) DEFAULT '';
  DECLARE v VARCHAR(50) DEFAULT '';
  DECLARE ks VARCHAR(255) DEFAULT '"k0"';
  DECLARE vs VARCHAR(255) DEFAULT '0';
  DECLARE kt VARCHAR(255) DEFAULT '"k0"';
  DECLARE vt VARCHAR(255) DEFAULT '0';
  DECLARE vsa VARCHAR(255) DEFAULT '';
  DECLARE vsb VARCHAR(255) DEFAULT '';
  DECLARE s VARCHAR(255) DEFAULT '';

  -- json = '{"k0":118},{"k0":83},{"k1":107},{"k1":10},{"k2":10}'
  -- ks = '"k0","k1","k2"'
  -- vs = '201,117,10'
  while item <> json and length(json) > 0 do
    -- item = {"k0":118}
    set item = substring_index(json, ',', 1);
    set len = length(item);
    set json = substring(json, len+2);

    -- 提取item中的键值对
    set item = replace(item, '{', '');
    set item = replace(item, '}', '');
    set k = substring_index(item, ':', 1);
    set v = substring_index(item, ':', -1);
    set item = concat('{', item, '}');

    -- 计算k的位置索引
    set s = ks;
    set idx = 0;
    while kt <> k and length(s) > 0 do
      set kt = substring_index(s, ',', 1);
      set len = length(kt);
      set s = substring(s, len+2);
      set idx = idx+1;
    end while;

    if kt <> k then
      -- 没用找到则直接添加
      set ks = concat(ks, ',', k);
      set vs = concat(vs, ',', v);
    else
      -- 计算v的位置索引
      set i = 0;
      set s = vs;
      while i < idx and length(s) > 0 do
        -- 将 v 与 vs 中对应位置的值相加后再拼回原来的格式
        -- 如 vs = '201,107', v = '10', idx = 1 则将对应位置值相加后再复原得 vs = '201,117'
        if length(vsa) = 0 then
          if i > 0 then
            set vsa = vt;
          end if;
        else
          set vsa = concat(vsa, ',', vt);
        end if;
        set vt = substring_index(s, ',', 1);
        set len = length(vt);
        set s = substring(s, len+2);
        set i = i+1;
      end while;

      if length(vt) = 0 then
        set vt = substring_index(vs, ',', 1);
      end if;

      if length(vsa) = 0 then
        set vt = concat(cast(vt as signed) + cast(v as signed), '');
      else
        set vsb = substring(vs, length(concat(vsa, ',', vt))+2);
        set vt = concat(cast(vt as signed) + cast(v as signed), '');
        if length(vsb) > 0 then
          set vt = concat(vsa, ',', vt, ',', vsb);
        else
          set vt = concat(vsa, ',', vt);
        end if;
      end if;

      set vs = vt;
    end if;

  end while;

  -- ks = '"k0","k1","k2"'
  -- vs = '201,117,10'
  -- s = {"k0":201,"k1":117,"k2":10}
  set kt = '';
  set vt = '';
  set s = '';
  while kt <> ks and length(ks) > 0 do
    set kt = substring_index(ks, ',', 1);
    set len = length(kt);
    set ks = substring(ks, len+2);

    set vt = substring_index(vs, ',', 1);
    set len = length(vt);
    set vs = substring(vs, len+2);

    if length(s) = 0 then
      set s = concat('{', kt, ':', vt);
    else
      set s = concat(s, ',', kt, ':', vt);
    end if;
  end while;
  set s = concat(s, '}');

  RETURN s;
END //
DELIMITER ;