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

json, mysql, sql

Solution

The `REGEXP` function just returns 0 or 1. You will have to use other string functions.

Try this: `substr(misc,locate('group_id',misc)+11,5) as Misc`. But that assumes that group_id always has 5 characters.

So this is better: `substring_index(substr(misc,locate('group_id',misc)+char_length('group_id')+3),'"',1) as Misc`.

Here is a fiddle to show it working: http://sqlfiddle.com/#!2/ea02e/15

EDIT You can get rid of the `+3` magic number by including the double quotes and colon in the strings like this: `substring_index(substr(misc,locate('"group_id":"',misc)+char_length('"group_id":"')),'"',1) as Misc`

Problem

I have the 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 i retrieve the product_name,image_path & only "group_id" value like "40067" from "misc" column. I tried below query but it returning 1/0 in Misc column. ``` SELECT product_name,image_path,misc REGEXP '(.*\"group_id\":*)' as Misc FROM ref_products where product_id=1 ``` Any idea guys how to do it ?

Original source