1. 版本说明
SELECT @@version; -- 5.6.35-log 不支持 JSON 解析相关函数 -- 5.7.28 支持
2. 测试代码
准备阶段
建表
CREATE TABLE `tb_test_json` ( `id` varchar(64) NOT NULL COMMENT '主键', `json_str` varchar(255) DEFAULT NULL COMMENT 'JSON字符串', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
录入数据
INSERT INTO `test`.`tb_test_json` ( `id`, `json_str` ) VALUES ( '001', '{ \"age\": 10, \"gender\": \"male\", \"hobby\": [ { \"describe\": \"健身时听摇滚乐，身心愉悦\", \"type\": \"music\" }, { \"describe\": \"偶尔垂钓，纯属娱乐\", \"type\": \"fishing\" } ], \"name\": \"杰森\"}' ); INSERT INTO `test`.`tb_test_json` ( `id`, `json_str` ) VALUES ( '002', '{ \"age\": 20, \"gender\": \"female\", \"hobby\": [ { \"describe\": \"买买买，钱包被掏空\", \"type\": \"shopping\" }, { \"describe\": \"东南西北，美食在心中\", \"type\": \"food\" } ], \"name\": \"贝比\"}' ); INSERT INTO `test`.`tb_test_json` ( `id`, `json_str` ) VALUES ( '003', '{ \"age\": 30, \"gender\": \"female\", \"name\": \"汤姆\"}' );
测试说明 JSON相关的函数，大部分我们看名字就知道函数的作用：
2.1 判读是否是合法的JSON类型
SELECT json_valid( 'hello' ), json_valid( '"hello"' );
2.2 JSON文本深度
SELECT id, json_depth( json_str ) FROM tb_test_json;
2.3 JSON文本长度
SELECT id, json_length( json_str ) FROM tb_test_json;
2.4 JSON值类型
SELECT id, json_type( json_str ) FROM tb_test_json;
2.5 JSON的keys
SELECT id, json_keys( json_str ) FROM tb_test_json;
2.6 JSON值获取
SELECT id, json_extract( json_str, '$.name' ) AS name0, json_extract( json_str, '$.age' ) AS age0, json_str ->> '$.name' AS name1, json_str ->> '$.age' AS age1, json_str -> '$.name' AS name2, json_str -> '$.age' AS age2 FROM tb_test_json;
2.7 JSON数据解析
单箭头获取值 双箭头获取字符串
-- 【可以】获取结果 SELECT * FROM tb_test_json WHERE json_str -> '$.age' = 20; -- 【可以】获取结果 SELECT * FROM tb_test_json WHERE json_str ->> '$.age' = '20'; -- 【不可以】获取结果 SELECT * FROM tb_test_json WHERE json_str -> '$.age' = '20';
数据筛选加解析【不同深度】
SELECT id, json_extract( json_str, '$.name' ) AS name0, json_extract( json_str, '$.age' ) AS age0, json_extract( json_str, '$.hobby[0].type' ) AS hobby0, json_str ->> '$.name' AS name1, json_str ->> '$.age' AS age1, json_str ->> '$.hobby[0].type' AS hobby1, json_str -> '$.name' AS name2, json_str -> '$.age' AS age2, json_str -> '$.hobby[0].type' AS hobby2 FROM tb_test_json WHERE json_str ->> '$.name' = '杰森' OR json_str -> '$.age' = 30 OR json_str -> '$.hobby[0].type' = 'shopping';
