56,687
社区成员
发帖
与我相关
我的任务
分享
mysql> create table me(id int auto_increment primary key, m json, c json);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into me(m,c)values('{"xm":"ldf","xb":"nan","mz":"han","hf":true,"sg":168}','["xm","mz"]');
Query OK, 1 row affected (0.00 sec)
mysql> select * from me;
+----+----------------------------------------------------------------+--------------+
| id | m | c |
+----+----------------------------------------------------------------+--------------+
| 1 | {"hf": true, "mz": "han", "sg": 168, "xb": "nan", "xm": "ldf"} | ["xm", "mz"] |
+----+----------------------------------------------------------------+--------------+
1 row in set (0.00 sec)
CREATE FUNCTION f(m json, c json)
RETURNS json
BEGIN
SELECT c->'$[0]', JSON_LENGTH(c), 1 INTO @name, @len, @pos;
SET @r:=CONCAT('{', @name, ':', JSON_EXTRACT(m, CONCAT('$.',@name)), '}');
WHILE @pos < @len DO
SELECT CONCAT('$.', JSON_EXTRACT(c, CONCAT('$[',@pos,']'))), @pos+1 INTO @name, @pos;
SELECT JSON_INSERT( @r, @name, JSON_EXTRACT(m, @name) ) INTO @r;
END WHILE;
RETURN @r;
END