Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev

How do you dig down into the JSON data, say like in comments on a blog post? This is a badly paraphrased question from Peter Zaitsev of Percona at the end of a recent presentation of mine at the Great Wide Open conference. I have to admit I had not done like that with JSON data but it had to be able to be done, right? Surely it can be done. Can't it? Time to dig. I could not find an example of JSON data on a blog with comments. But the good folks at Mongo had an example of a restaurant with grades.

{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
     { "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
     { "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
     { "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
     { "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}

So how do we get the grade information and just the grades (A,A,A,A, and B) from grades?

I created a quick little table named restaurant with a single column named data for this test and then added the above document to the table. Yeah, real creative on the table and columns names there. Let's look at the fields, or keys.

mysql> select json_keys(data) from restaurant;
+----------------------------------------------------------------------+
| json_keys(data)                                                      |
+----------------------------------------------------------------------+
| ["name", "grades", "address", "borough", "cuisine", "restaurant_id"] |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

So can we see the path to the grades key? Yes, but not the date, grade, or score keys from under the grades tag. Note that in the case the 'one' or 'all' second argument returns the same result.

mysql> select json_contains_path(data,'one','$.grades') from restaurant;
+-------------------------------------------+
| json_contains_path(data,'one','$.grades') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

Grab the grades.

mysql> select json_extract(data,'$.grades') from restaurant\G
*************************** 1. row ***************************
json_extract(data,'$.grades'): [{"date": {"$date": 1393804800000}, "grade": "A",
 "score": 2}, {"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, {"da
te": {"$date": 1358985600000}, "grade": "A", "score": 10}, {"date": {"$date": 13
22006400000}, "grade": "A", "score": 9}, {"date": {"$date": 1299715200000}, "gra
de": "B", "score": 14}]
1 row in set (0.00 sec)

This is all the info from the grades tag. Now to whittle this down to just the individual grade entries. We can get the individual grades as $.grades.[N] such as:

mysql> select json_extract(data, '$.grades[1]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[1]'): {"date": {"$date": 1378857600000}, "grade": "
A", "score": 6}
1 row in set (0.00 sec)

mysql> select json_extract(data, '$.grades[4]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[4]'): {"date": {"$date": 1299715200000}, "grade": "
B", "score": 14}
1 row in set (0.00 sec)

Much closer!

And then the individual grade from the 4th entry:

mysql> select json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') from
restaurant;
+--------------------------------------------------------------+
| json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') |
+--------------------------------------------------------------+
| "B"                                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

I need to go read up on JSON pathing and experiment so I can grab all the grades at once but right now I fell like I am halfway done with the 'homework' Peter assigned.