Last week I was implementing a new report using MySQL, and some of the data was stored in JSON format. MySQL has lots of built-in string functions, but none of them work for JSON. My first idea was to use the mysql_json UDF, but then I remembered that common_schema recently added JSON parsing. Since I have common_schema 1.3 installed on all of my databases already, I tried that first.

In this particular case the JSON is pretty simple. It contains two fields: age and gender. Here’s an example of the data format:

1
2
3
4

{"age":"Over 30","gender":"female"}
{"age":"Under 30","gender":"female"}
{"age":"Over 30","gender":"male"}
{"age":"Under 30","gender":"male"}

Parsing that into two separate columns with common_schema is pretty easy; just use the extract_json_value() function like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysql> select common_schema.extract_json_value(f.event_data,'/age') as age,
    ->   common_schema.extract_json_value(f.event_data,'/gender') as gender,
    ->   sum(f.event_count) as event_count 
    -> from json_event_fact f
    -> group by age, gender;

+----------+---------+-------------+
| age      | gender  | event_count |
+----------+---------+-------------+
| Over 30  | female  |     3710983 |
| Over 30  | male    |     2869302 |
| Under 30 | female  |     5027591 |
| Under 30 | male    |     4918382 |
| unknown  | female  |       42039 |
| unknown  | male    |       50173 |
| unknown  | unknown |        8372 |
+----------+---------+-------------+

That fits my reporting use case perfectly, and I’m now looking into other ways to use common_schema to parse JSON stored elsewhere in my database.

If you haven’t tried common_schema, I recommend you check it out. It’s easy to install, saves a lot of time and effort, and gets better with each release. In fact, I’m now using 4 of the 7 “New and Noteworthy” features from the 1.3 release.