1.建表语句
CREATE TABLE `uset_test_demo` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`context` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
2.源数据
{
"title": "Harry Potter",
"author": "J.K. Rowling",
"year": 2005
}
SELECT * FROM `uset_test_demo`
3.提取单个字段
#提取json数据并去双引号,单个提取
SELECT utd.id,trim(both '"' from JSON_EXTRACT(utd.context,'$[0].title')) '标题' from uset_test_demo utd
4.提取多个字段为字符串数组
这里暂时没测试出来去除双引号
#提取json数据,多个字段为数组
SELECT utd.id,trim(both '"' from JSON_EXTRACT(utd.context,'$[0].title','$[0].author','$[0].year')) 'list' from uset_test_demo utd
5提取json数据,多个字段,并对其中string数据去除双引号
SELECT utd.id,
trim(both '"' from JSON_EXTRACT(utd.context,'$[0].title'))'标题',
trim(both '"' from JSON_EXTRACT(utd.context,'$[0].author'))'作者',
JSON_EXTRACT(utd.context,'$[0].year') '年度'
from uset_test_demo utd