mysql 查询json类型数据

t1.id AS item_id, t1.title AS item_name,t6.id AS topic_id, t6.title AS topic_name, t1.extra AS media_id, t1.biz_type from tem t1 join component_item t2 on t1.id = t2.item_id join component t3 on t2.component_id = t3.id join drawer t4 on t4.id = t3.drawer_id join channel_drawer t5 on t5.drawer_id = t4.id join channel t6 on t6.id = t5.channel_id where t1.biz_type in ("JUMP_TO_SHOW","JUMP_TO_VIDEO") and t1.extra IS NOT NULL and t6.topic_property IS NOT NULL

 

 

优化后的sql

select t1.id AS item_id, t1.title AS item_name,t6.id AS topic_id, 
       t6.title AS topic_name, JSON_EXTRACT(t1.extra,‘$.value‘) AS media_id, 
       trim(LEADING ‘JUMP_TO_‘ from t1.biz_type) AS biz_type
        from item_pre t1
        join component_item_pre t2 on t1.id = t2.item_id
        join component_pre t3 on t2.component_id = t3.id
        join drawer_pre t4 on t4.id = t3.drawer_id
        join channel_drawer_pre t5 on t5.drawer_id = t4.id
        join channel_pre t6 on t6.id = t5.channel_id
        where t1.biz_type in ("JUMP_TO_SHOW","JUMP_TO_VIDEO")
              and t1.extra IS NOT NULL 
              and t6.topic_property IS NOT NULL
              and JSON_EXTRACT(t6.topic_property,‘$.group‘)= "电影"

 

 

mysql 查询json类型数据

标签：src   分享   val   mys   rac   extract   mysq   draw   raw