JSON Parsing in MySQL Using common_schema

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:

{"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:

``` 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.