mysql(5.6及以下)解析json的方法实例详解
16lz
2021-12-21
mysql(5.6及以下)解析json
#json解析函数 DELIMITER $$DROP FUNCTION IF EXISTS `json_extract_c`$$CREATE FUNCTION `json_extract_c`(details TEXT,required_field VARCHAR (255)) RETURNS TEXT CHARSET latin1BEGINSET details = SUBSTRING_INDEX(details, "{", -1);SET details = SUBSTRING_INDEX(details, "}", 1);RETURN TRIM( BOTH '"' FROM SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( details, CONCAT('"', SUBSTRING_INDEX(required_field,'$.', -1),'":' ),-1 ),',"',1 ),':',-1 )) ;END$$DELIMITER ;example:select json_extract_c(json, "$.totalDays"), json from item limit 100;
CREATE TABLE `json_test` ( `id` int(11) DEFAULT NULL, `person_desc` text COLLATE utf8mb4_unicode_ci) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;insert INTO json_test values(2,'{"firstName":"Sergei","lastName":"Rachmaninoff","instrument":"piano"}');SELECT id,json_extract_c(person_desc,'$.lastName') as "keys" FROM json_test;
CREATE DEFINER=`zhangfen`@`%` FUNCTION `json_extract_c`(details TEXT,required_field VARCHAR (255)) RETURNS text CHARSET latin1BEGINSET details = SUBSTRING_INDEX(details, "{", -1);SET details = SUBSTRING_INDEX(details, "}", 1);RETURN TRIM( BOTH '"' FROM SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT('"":"",',details), CONCAT( '"', SUBSTRING_INDEX(required_field,'$.', -1), '":' ), -1 ), ',"', 1 ), ':', -1 )) ;END
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- 《Android和PHP最佳实践》官方站
- android用户界面之按钮(Button)教程实例汇
- Android(安卓)- Manifest 文件 详解
- TabHost与RadioGroup结合完成的菜单【带效果图】5个Activity
- Android的Handler机制详解3_Looper.looper()不会卡死主线程
- Android(安卓)UI开发第十七篇——Android(安卓)Fragment实例(Lis
- Android——Activity四种启动模式
- Selector、shape详解(一)