一、 样例数据准备
CREATE TABLE `product_name_copy_2` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` json DEFAULT NULL COMMENT '产品名',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
{
"sites": [
{ "name":"大爷" , "url":"www.daye.com" },
{ "name":"二大爷" , "url":"www.erdaye.com" },
{ "name":"老大爷" , "url":"www.laodaye.com" }
]
}
二、操作测试
- 获取单层json值
select name , replace(json_extract(name,'$.sites'),'"','') as sites from product_name_copy_2
- 获取嵌套json值
select name , replace(json_extract(name,'$.sites[0]'),'"','') as url from product_name_copy_2
select name , replace(json_extract(name,'$.sites[0].url'),'"','') as url from product_name_copy_2
– 获取嵌套值并提取域名
-- regexp_substr(待处理字符串,要匹配的正则,从第几个字符开始(默认1),正则的第几个匹配组,i/c[i不分大小写-c反之])
select name , regexp_substr(json_extract(name,'$.sites[0].url'),'[a-z]+',1,2) as sites from product_name_copy_2
- 结果示例