mysql 5.6 json_mysql 列转行并进行json截取(mysql 5.6)

最新推荐文章于 2024-02-02 14:43:54 发布

长颈鹿很忙

最新推荐文章于 2024-02-02 14:43:54 发布

阅读量609

点赞数

文章标签： mysql 5.6 json

本文链接：https://blog.csdn.net/weixin_31474035/article/details/113171859

版权

本文介绍如何在MySQL 5.6中将JSON列转换为行，并进行JSON字段的截取。通过示例查询展示了如何利用SUBSTRING_INDEX和REPLACE函数解析JSON字符串，提取'val'和'area'字段的值。

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

set @fild='},{';selectsubstring_index(substring_index(result,',',1),':',-1) asval

,replace(substring_index(substring_index(result,',',2),':',-1),'"','') asareafrom(SELECTt1.id

,t2.num_rn

,substring_index(substring_index(t1.image_file,@fild, t2.num_rn + 1), @fild, -1) as result --列转行

FROM(SELECT

1 asid

,'[{"val":4,"area":"floor","setVal":null,"isExceed":false,"isQualified":true},{"val":11,"area":"floor","setVal":null,"isExceed":true,"isQualified":false},{"val":3,"area":"floor","setVal":null,"isExceed":false,"isQualified":true},{"val":3,"area":"walls0","setVal":null,"isExceed":false,"isQualified":true},{"val":4,"area":"walls0","setVal":null,"isExceed":false,"isQualified":true},{"val":4,"area":"walls0","setVal":null,"isExceed":false,"isQualified":true},{"val":3,"area":"walls0","setVal":null,"isExceed":false,"isQualified":true},{"val":6,"area":"walls0","setVal":null,"isExceed":false,"isQualified":true},{"val":7,"area":"walls0","setVal":null,"isExceed":false,"isQualified":true},{"val":3,"area":"walls0","setVal":null,"isExceed":false,"isQualified":true},{"val":3,"area":"walls1","setVal":null,"isExceed":false,"isQualified":true},{"val":3,"area":"walls1","setVal":null,"isExceed":false,"isQualified":true},{"val":2,"area":"walls1","setVal":null,"isExceed":false,"isQualified":true},{"val":3,"area":"walls1","setVal":null,"isExceed":false,"isQualified":true},{"val":5,"area":"walls1","setVal":null,"isExceed":false,"isQualified":true},{"val":10,"area":"walls1","setVal":null,"isExceed":true,"isQualified":false},{"val":7,"area":"walls1","setVal":null,"isExceed":false,"isQualified":true},{"val":1,"area":"walls5","setVal":null,"isExceed":false,"isQualified":true},{"val":4,"area":"walls5","setVal":null,"isExceed":false,"isQualified":true}]' asimage_file

) t1join(select 0 as num_rn union all

select 1 as num_rn union all

select 2 as num_rn union all

select 3 as num_rn union all

select 4 as num_rn union all

select 5 as num_rn union all

select 6 as num_rn union all

select 7 as num_rn union all

select 8 as num_rn union all

select 9 asnum_rn

) t2ON t2.num_rn < ((LENGTH(t1.image_file) - LENGTH(REPLACE(t1.image_file, @fild, '')))/length(@fild) + 1)order byt2.num_rn

) t3