Mysql的json字段转换和获取json中的某个key的值

水深无声 2023-09-29 11:12 6阅读 0赞

首先数据库的字段类型是json 如图的数据![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70][]

查询的数据如下，字段section为多个json的数组

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 1][]

通过Mysql的函数JSON\_EXTRACT 查询这个字段的第一个json 得出来以下数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 2][]

函数可以缩写成这样

![20190531104502112.png][]

然后要再查询json里面的字段就需要指定某个key ($.video)即可查询该值

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 3][]

转为字符串用函数 JSON\_UNQUOTE即可

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190531103816203.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190531104203451.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190531104343224.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1,size_16,color_FFFFFF,t_70
[20190531104502112.png]: https://img-blog.csdnimg.cn/20190531104502112.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190531104539140.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190531104725615.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlbmhhbzI0NzI1,size_16,color_FFFFFF,t_70