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
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
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
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
tb_test_json
WHERE
json_str ->> '$.name' = '杰森' OR json_str -> '$.age' = 30 OR json_str -> '$.hobby[0].type' = 'shopping';
Java开发 @HNXA通信技术股份有限公司
粉丝
