sql - How to retrieve values stored in JSON array in MySQL query itself? -

i have following table

product_id    product_name    image_path                             misc ----------   --------------  ------------                           ------      1            flex        http://firstpl...      {"course_level_id":19,"group_id":"40067"}      2           android      http://firstpl...      {"course_level_id":20,"group_id":"40072"} 

so how can retrieve product_name,image_path & "group_id" value "40067" "misc" column.

i tried below query returning 1/0 in misc column.

select product_name,image_path,misc regexp '(.*\"group_id\":*)' misc ref_products product_id=1 

any idea guys how ?

the regexp function returns 0 or 1. have use other string functions.

try this: substr(misc,locate('group_id',misc)+11,5) misc. assumes group_id has 5 characters.

so better: substring_index(substr(misc,locate('group_id',misc)+char_length('group_id')+3),'"',1) misc.

here fiddle show working: http://sqlfiddle.com/#!2/ea02e/15

edit can rid of +3 magic number including double quotes , colon in strings this: substring_index(substr(misc,locate('"group_id":"',misc)+char_length('"group_id":"')),'"',1) misc