Geek asks and answers

How to get 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 get the name product_name, image_path and only the value "group_id", for example "40067" from the column "misc".

I tried to execute the query below, but it returns 1/0 in the Misc column.

SELECT product_name,image_path,misc REGEXP '(.*\"group_id\":*)' as Misc FROM ref_products where product_id=1

Any ideas guys how to do this?

+3
source share
3 answers

The function REGEXPsimply returns 0 or 1. You will have to use other string functions.

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

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

Here is a fiddle showing that it works: http://sqlfiddle.com/#!2/ea02e/15

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

+5

, MySQL JSON.

MySQL 5.7.8 ( ) JSON, , JSON_EXTRACT() ->.

EG:

SELECT product_name, image_path, JSON_EXTRACT(misc,'$.group_id') AS `group_id` 
FROM ref_products 
WHERE product_id=1

: https://dev.mysql.com/doc/refman/5.7/en/json.html

+3

common_schema MySQL ( MySQL >= 5.1), , :

SELECT x.product_name, x.image_path, common_schema.extract_json_value(x.misc,'/group_id') as group_id FROM your_table x

common_schema framework: https://code.google.com/p/common-schema/

+2

More articles:


All Articles