CODEWITHSUNDEEP
mysqljson
Code Bunny
Code Bunny

Reputation: 447

Querying a MySQL table with a JSON field and accessing JSON attributes

With MySQL 5.7 new features involving JSON has emerged. Among these features is the ability to query the fields in the JSON object as it is stored in the database.

My object looks like this. 

{
"color": [
  {"WHITE" :{ "size": [
        {"S": [{"Price" : "31"},
              {"discountPrice" : "13" }]}
        ]}},
    {"BLACK" :{ "size": [
        {"S": "69"}, 
        {"M": "31"}, 
        {"L": "55.666"}
        ]}}
]}

I want to query this as if it was regular tabular data, to this end I tried the following query to no avail.

select json_extract(Sku, '$.color[0]') from CRAWL.DAILYDATA;

I want to explode this into a format that looks more like a traditional RDBMS.

Any ideas?

Upvotes: 3

Views: 2099

Answers (1)

juacala
juacala

Reputation: 2235

In order to get data out of a json object as values, you need to get all the way down to the values. For instance, if you wanted to pull all of the values like they are regular RDBMS columns:

select json_extract(Sku, '$.color[0].WHITE.size[0].S[0].price') as price,
json_extract(Sku, '$.color[0].WHITE.size[0].S[0].discountPrice') as discountPrice
from CRAWL.DAILYDATA;

Of course, you need to know exactly what you're looking for in the object. This is the price of having a schema-less object like json. In principle, you could define a mysql function that would use combinations of

json_contains_path

and 

json_extract

to make sure the path you are looking for exists, and otherwise it returns null. Personally though, if you want the RDBMS quality, why not just force it into a form where you can put the values directly into mysql tables? This is, of course, why RDBMS's exist. If you can't put it into such a form, you're going to be stuck with searching your json as above.

Upvotes: 2

Related Questions