Thursday, March 17, 2016

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.

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.