mysql 字段json行转列

楚啵啵

已于 2023-02-10 17:39:06 修改

阅读量619

点赞数

文章标签： mysql 数据库 sql

于 2023-02-10 17:35:58 首次发布

本文链接：https://blog.csdn.net/qq_36907589/article/details/128974499

版权

文章展示了如何在MySQL中进行JSON字段的提取和处理，包括使用JSON_EXTRACT和SUBSTRING_INDEX函数从JSON数组中提取特定信息，以及如何创建和填充表格数据。内容涉及到SQL查询语句，用于处理包含多级行政区划信息的JSON数据。

摘要由CSDN通过智能技术生成

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for keyid
-- ----------------------------
DROP TABLE IF EXISTS `keyid`;
CREATE TABLE `keyid` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of keyid
-- ----------------------------
INSERT INTO `keyid` VALUES ('0');
INSERT INTO `keyid` VALUES ('1');
INSERT INTO `keyid` VALUES ('2');
INSERT INTO `keyid` VALUES ('3');
INSERT INTO `keyid` VALUES ('4');
INSERT INTO `keyid` VALUES ('5');
INSERT INTO `keyid` VALUES ('6');
INSERT INTO `keyid` VALUES ('7');
INSERT INTO `keyid` VALUES ('8');
INSERT INTO `keyid` VALUES ('9');

执行sql 创建表

sql字段表的三条数据 json 字段名为 AREA 第二条是2个省市区要拆分展示四列

[{"cityCode":"0472","cityName":"包头市","districtCode":"047202","districtName":"昆都仑区","provinceCode":"040","provinceName":"内蒙古"}]

[{"cityCode":"011","cityName":"北京市","districtCode":"01001","districtName":"东城区","provinceCode":"010","provinceName":"北京市"},{"cityCode":"0310","cityName":"邯郸市","districtCode":"031002","districtName":"丛台区","provinceCode":"060","provinceName":"河北省"}]

[]

select 
	a.id,a.dealer_id
	replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].provinceCode'),'"','')  as authProvinceCode,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].provinceName'),'"','') as authProvinceName,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].cityCode'),'"','') as authCityCode,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].cityName'),'"','') as authCityName,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].districtCode'),'"','') as authDistrictCode,
  replace(JSON_EXTRACT(SUBSTRING_INDEX( SUBSTRING_INDEX( a.jsonarr, ";", b.id + 1 ), ";",- 1 ), '$[0].districtName'),'"','') as authDistrictName 
from 
	( select tt.id,tt.dealer_id,
	 (case tt.AREA
     when '[]'  then ( '{"cityCode":"","cityName":"","districtCode":"","districtName":"","provinceCode":"","provinceName":""}')
     else ( replace(replace(replace(tt.AREA,"},{","};{"),"]",""),"[",""))
     end 
	 )as jsonarr
from ltx_dealer_auth_info tt where tt.dealer_id = 'f66b4c11b2dd43a3bb65c98c3e818f5c' and tt.del_flag='0') a
join keyid b 
  -- &lt; sql中相当于小于 如果放到XML文件中
  -- on b.id &lt; ( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
on b.id<( length( a.jsonarr ) - length( replace ( a.jsonarr, ";", "" ) ) + 1 )
where a.jsonarr != ''