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

Question

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 ?

Solution 1

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

Solution 2

Since this question was asked MySQL have introduced support for the JSON data type.

In MySQL 5.7.8 (and up) you can query the actual JSON string stored in the column using JSON_EXTRACT() or the equivalent -> alias.

EG:

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

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

Solution 3

You can use a common_schema framework for MySQL (compatible with all MySQL >= 5.1) and then get what you need on this way:

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/

Comments

[zerkms]

You shouldn't have stored it in json in the first place then.

[UI Dev]

No buddy its an easy way to do instead adding more columns store in one column. If any solution to this then welcome

[Admin]

@RIADev - it's very easy: put the data in, then you can't get it out again. You should have stored this data in separate columns.

[UI Dev]

@MikeW i have posted an sample data here. Its not possible to change add the columns & migrate the data. So i need desperately a solution to this.

[Turophile]

No, @zerkms isn't being snarky, he's correct. If you want to query a value, store it in a column. Otherwise, why not just have a single huge varchar column in each table with everything in it?

[Nanne]

I believe it should be noted that this is very strange. Either this is the data you have in your database and you should play with it in code (not in the database). Otherwise you might even go as far as JOIN on this value. If you really need to have this specific piece of your datapoint in SQL then you should really save it as one, instead of this JSON-in-column thing.

[UI Dev]

@Nanne i didn't get your point. Is it really bad to save the data in JSON format.

[Turophile]

Yes. See @zerkms and my comment on the question. You can do what you are doing. You can make it work. But it is the wrong way to use a database.

[Nanne]

Well, there is a reason you use a relational database. If you need the JSON format as is (for instance in a situation where you want to save what exact JSON you have sent to a user) it COULD be usefull, but not in this case, as you demonstrate by wanting to get one of the values from the json in a query. It is not how it should be, and one of the problems you can get is exactly the one you are facing now: you want something but have a hard time getting it. Now, if you cannot change anything, then just do it in code, not in the database. It should be way easier (parse to json for instance)

[UI Dev]

@Nanne now i am clear with your point. See we are doing a project where client is giving always a change request so that time we can not say we have to add two columns in DB , It will be awkward. The servers access only client is having. So we decided instead of adding columns we can do some different. So that it is. Its working perfect in my case thanks.

[Matt2012]

A lot of people being sql purists here. I find storing json in a column a very powerful way of building a backend. The choice to convert data into 'normalised' data for a sql database and then convert it back to json for an app is not an absolute truth but an opinion. The complexity has got to be somewhere. The code above shows that it's relatively easy to still use data stored this way in your views.