mysql进阶操作json篇(二)

一、 样例数据准备

• 建表

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反之])
--  REGEXP_SUBSTR(待处理字段,匹配值,起始下表(默认1),正则匹配到的第几个值)
select name , regexp_substr(json_extract(name,'$.sites[0].url'),'[a-z]+',1,2) as sites from product_name_copy_2  

• 结果示例

  
  运行结果.png