MySQL8 把JSON字段，直接转换为多行数据1

1. t_course_put_apply表中的put_targets列为json类型。查询出来的示例数据如下：

select put_targets from t_course_put_apply t WHERE t.id = 2022031120200011002;

查询结果：

{

"region": ["431200"],

"school": ["2022030410000002527", "2022030410000002529", "2022030410000002530", "2022030410000002531", "2022030410000002532", "2022030410000002533", "2022030410000002534", "2022030410000002535", "2022030410000002537", "2022030410000002538"]

}

 

2. 通过一个sql，想把查询结果转换为多行数据。

SELECT

cte_put.school_id, cte_put.region_id

FROM

t_course_put_apply t,

JSON_TABLE(put_targets,

'$' COLUMNS ( nested PATH '$.school[*]' COLUMNS ( school_id varchar(32) PATH '$') ,

nested PATH '$.region[*]' COLUMNS ( region_id varchar(32) PATH '$') )) AS cte_put

WHERE

t.id = 2022031120200011002;

查询结果：

school_id	region_id
2022030410000002527
2022030410000002529
2022030410000002530
2022030410000002531
2022030410000002532
2022030410000002533
2022030410000002534
2022030410000002535
2022030410000002537
2022030410000002538
	431200