Using JSON Format in MySQL

I decided to play around with SQL yesterday and ended up falling in love with a new function…

Let me tell you the story of how SQL and JSON come together to make me a happy boy.

SQL tables offer a variety of data types, and most are similar to ones you see in other programming languages like Python. But I never expected JSON to be a data type for a SQL column.

But boy, I was wrong…

Once I learned this, I decided to experiment.

I created the table you see below using some nifty JSON functions SQL offers.

So, here’s the process I took

Storing JSON File in MySQL Table

First, I had to get the data from a JSON file into MySQL. I originally planned to use the “LOAD DATA INFILE” method, but my laptop didn’t like that. So instead, I wrote a Python script to import the data neatly into MySQL. [LINK TO GITHUB FOR PYTHON CODE]

Finding Max Values Using SQL

Next, I used two of the new JSON methods to find the maximum number of artists a song in my playlist has.

These are the methods:

JSON_EXTRACT()

• This function accesses different keys in the JSON and returns your desired values.

JSON_LENGTH()

• Some JSON keys have arrays as values. So this function finds the length of an array in a JSON file.

Creating Table for Clean Data

Here I’m creating a simple table that shows all artist names for every track in my playlist. It’s empty to start, but I insert data into it later…

Inserting Data Into New Table

Again, I’m using JSON_EXTRACT() to pull data out of the JSON types, but there’s another function that’s important too.

JSON files store string data within quotes (“ “).

So when transferring data from JSON type to VARCHAR() type, MySQL keeps the double quotes around your string.

The JSON_UNQUOTE() method removes those quotes to make your new table look squeaky clean.