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.