废话不多说,直接上实例。
一、json结构
创建测试表
分析:article表中的字段category与tags均为json类型
填写测试数据
总体预览
二、json查询
select id,json_extract(category,'$.name') as name from test.article;#提取json字段里面的信息
# column->path形式 访问json中的元素 category->'$.name'
select id,category->'$.name' as name from test.article;#提取json字段里面的信息(访问json中的元素 category->'$.name')
select id,json_unquote(json_extract(category,'$.name')) as name from test.article;#提取json字段里面的信息,json_unqoute去双引号
select id,json_unquote(category->'$.name') as name from test.article;#提取json字段里面的信息,json_unqoute去双引号
select id,category->>'$.name' as name from test.article;
select * from test.article where category='{"id": 1, "name": "php"}'; #json不同于字符串,不能当作字符串做比较
select * from test.article where category=cast('{"id": 1, "name": "php"}' as JSON); #通过CAST将字符串转换成JSON形式
select * from test.article where category->'$.name'='java';
select * from test.article where category->>'$.name'='java';
#JSON 中的元素搜索是严格区分变量类型的,比如说整型和字符串是严格区分的
select * from test.article where category->'$.id'='2';#字符号串
select * from test.article where category->'$.id'=2;#整形
select * from test.article where category->'$.id'='3';#字符号串
select * from test.article where json_extract(category,'$.id')='3';#字符号串
select * from test.article where json_contains(category,'2','$.id');#整数
select * from test.article where json_contains(category,'&#