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)
m字段包含多个键值对,返回c字段给出的键值对。
若希望得到下面的数据,这里应该如何写查询语句,注意c字段的值是不确定的+----+----------------------------+--------------+
| id | mmm | ccc |
+----+----------------------------+--------------+
| 1 | {"mz": "han", "xm": "ldf"} | ["xm", "mz"] |
+----+----------------------------+--------------+
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)
m字段包含多个键值对,返回c字段给出的键值对。
若希望得到下面的数据,这里应该如何写查询语句,注意c字段的值是不确定的+----+----------------------------+--------------+
| id | mmm | ccc |
+----+----------------------------+--------------+
| 1 | {"mz": "han", "xm": "ldf"} | ["xm", "mz"] |
+----+----------------------------+--------------+
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
SELECT *, f(m, c) FROM me;