RIA Dev RIA Dev - 1 year ago 62
JSON Question

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

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 ?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download