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:
1 2 3 4
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
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.