查询json字段，返回包含指定属性的json数据

[sxldfang]

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"] |
+----+----------------------------+--------------+

[学海无涯-回头是岸]

你看下 网上的MySQL 出来json

[sxldfang]

引用

你看下 网上的MySQL 出来json

没看粗来啊~~~

[zjcxc]

自己写个函数，类似这样

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

[zjcxc]

然后调用函数实现查询
SELECT *, f(m, c) FROM me;

[天地炫舞]

直接查询出数据应该就可以了吧，为什么要执着的查出一个json，根据条件查询出数据并不难，使用mysql5.7对json类型的操作函数即可。三楼不愧为数据库大佬，佩服，佩服，函数写的很强