MySQL Query JSON type data

If the T1 table has a extra field, the field is of type text and the data is in JSON format {"value": "XMJGXIQQQQQQQQQQ"}

How do I get the value of the JSON inside values?

Json_extract (T1.extra, ' $.value ')

Get "XMJGXIQQQQQQQQQQ"

Raw SQL and original results

SelectT1.id asITEM_ID, T1.title asItem_name,t6.id astopic_id, T6.title asTopic_name, T1.extra asmedia_id, T1.biz_type fromtem t1JoinComponent_item T2 onT1.id=t2.item_idJoincomponent T3 ont2.component_id=t3.idJoindrawer T4 onT4.id=t3.drawer_idJoinChannel_drawer T5 ont5.drawer_id=t4.idJoinChannel T6 onT6.id=t5.channel_idwhereT1.biz_typeinch("Jump_to_show", "Jump_to_video") andT1.extra is  not NULL                andT6.topic_property is  not NULL 

Optimized SQL

SelectT1.id asITEM_ID, T1.title asItem_name,t6.id astopic_id, T6.title asTopic_name, Json_extract (T1.extra,'$.value') asmedia_id, trim (Leading'jump_to_'  fromT1.biz_type) asBiz_type fromItem_pre T1JoinComponent_item_pre T2 onT1.id=t2.item_idJoincomponent_pre T3 ont2.component_id=t3.idJoindrawer_pre T4 onT4.id=t3.drawer_idJoinChannel_drawer_pre T5 ont5.drawer_id=t4.idJoinChannel_pre T6 onT6.id=t5.channel_idwhereT1.biz_typeinch("Jump_to_show", "Jump_to_video") andT1.extra is  not NULL                andT6.topic_property is  not NULL               andJson_extract (T6.topic_property,'$.group')=Movie

MySQL Query JSON type data