CREATE TABLE `test_json` ( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `age` int NOT NULL, `test_json_array` json NOT NULL, `test_json_object` json NOT NULL, `test_json_array_object` json NOT NULL, `custinfo` json DEFAULT NULL COMMENT 'json数据', PRIMARY KEY (`id`), KEY `test_json$list_modified` (`age`,(cast(json_extract(`test_json_array`,_utf8mb4'$[*]') as unsigned array)),`name`), KEY `json_more_value_index` ((cast(json_extract(`custinfo`,_utf8mb4'$.zipcode') as unsigned array)),`name`,`age`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
json_keys:获取json对象的key值组成的数组
json_object:创建一个json对象
json_overlaps:对比的俩个json有相同的键值对或者数组元素即可
json_pretty:格式化json,符合人类的阅读习惯
json_quote:将字符串引用为json值,换句话说就是把字符串处理成为带双引号的值
1、json_keys
获取json对象中的key值组成的数组
2、json_object
创建json对象,配合json_contains使用可以很好的进行查询
select * from test_json where json_contains(test_json_object, json_object('height', '175cm'));
3、json_overlaps
对比俩个json,如果有一个键值对或者数组元素则返回true,否则返回false
select * from test_json where json_overlaps(test_json_object, json_object('height', '175cm'));
总结:
标签:overlaps,object,json,pretty,test,array,NULL From: https://www.cnblogs.com/jelly12345/p/17399303.html