If you want to convert JSON to SQL there’s no concrete or straightforward way of doing things. Conversion is usually tricky because JSON and SQL are written using different structures.

Realistically, you have only two options: use a conversion tool like SQLizer or write some code and build yourself a script to do it for you. We’ll cover both methods in this blog.

We’ll do the easy way first with SQLizer.

Step 1: Choose the JSON file you want to convert to SQL

Choose the JSON file you want to convert

Step 2: Select JSON as your file type

Select JSON as your file type

Step 3: Type in a name for your database table

Type in a name for your database

Step 4: Convert your file!

Convert your file

Here’s a video showing the exact steps to convert JSON to SQL in 30 seconds.

SQLizer is definitely the easier method of converting JSON files to SQL databases. But as promised, here’s a breakdown of building yourself a script.

How to convert JSON to SQL with your own script

An advance warning: this is going to be a bit messy and complicated. And while we won’t walk through the exact steps of creating a script, we’ve outlined some things you’ll need to watch out for if you do write your own script.

You can convert JSON to SQL by writing a script in most programming languages. The example below uses Python. Here’s what the Python script looks like:

import json

with open('strings.json') as json_data:    d = json.load(json_data)    print(d)

It’s not that simple

“Is that all?” I hear you think. Well, it isn’t as simple as writing and deploying this script.

JSON files are built in a hierarchy with trees of “objects” within the data. Herein lies the problem: SQL is written in a “flat” structure so you need to somehow turn the hierarchical JSON data into a “flat” table with columns and rows.

The most reliable method to convert JSON to SQL is to “flatten” the JSON data - this is what SQLizer does.

Let’s look at some examples.

JSON to SQL example one

Here’s some JSON code we want to convert to SQL:

{    "ListOne": [        {            "foo": 1,            "bar": 2        },        {            "foo": 3,            "bar": 4        }    ],    "ValTwo": "baz",    "ValThree": "fizz"}

After flattening the JSON and converting to SQL, you end up with the following:

CREATE TABLE object_with_list_of_objects (    `ListOne_foo` INT,    `ListOne_bar` INT,    `ValTwo` VARCHAR(3) CHARACTER SET utf8,    `ValThree` VARCHAR(4) CHARACTER SET utf8);INSERT INTO object_with_list_of_objects VALUES (1,2,NULL,NULL);INSERT INTO object_with_list_of_objects VALUES (3,4,'baz','fizz');

In the above example, “ListOne” contains two objects, each with the two values of “foo” and “bar”. Both “foo” and “bar” get their own rows in our database table.

But this isn’t the only way of converting JSON to SQL. If we switch the order of the JSON around we get different SQL.

JSON to SQL example two

Here’s the reversed JSON:

{    "ValOne": "baz",    "ValTwo": "fizz",    "ListThree": [        {            "foo": 1,            "bar": 2        },        {            "foo": 3,            "bar": 4        }    ]}

And here’s the resulting SQL after flattening the reversed JSON:

CREATE TABLE object_with_list_of_objects (    `ValOne` VARCHAR(3) CHARACTER SET utf8,    `ValTwo` VARCHAR(4) CHARACTER SET utf8,    `ListThree_foo` INT,    `ListThree_bar` INT);INSERT INTO object_with_list_of_objects VALUES ('baz','fizz',1,2);INSERT INTO object_with_list_of_objects VALUES ('baz','fizz',3,4);

You’ll notice that “ValOne” and “ValTwo” appear at the top of this SQL and that their values have been “filled down” all the rows. The SQL is reversed.

No ‘right’ way

Both the first and second examples are equally valid ways of converting JSON to SQL. In fact, there isn’t really a ‘right’ or ‘wrong’ way of doing things. This is why it’s tricky and time-consuming writing scripts each time you want to convert JSON to SQL.

Not to mention each programming language has different ways of “flattening” or converting JSON. Just check out this StackOverflow post to see how you can write a script in PHP. You can see how it’s a different process.

A pitfall to avoid: losing data

Another issue to think about is losing the meaning of data from JSON when converting over to SQL. Because the structure changes and there’s no single way of doing this, it’s likely that some meaning from the JSON gets lost in the SQL file somewhere.

SQLizer is built to never lose value during conversion. It took lots of testing and iteration but SQLizer ensures no value is ever lost and that everything is always in the SQL file.

DIY or use SQLizer

As you can see it’s neither easy nor simple to convert JSON to SQL with DIY methods. If you really want to do it yourself, taking the knowledge above, you can find great tips on StackOverflow.

If you’d rather save hours of your time then upload your file to SQLizer and wait about 30 seconds while it converts your JSON file to a SQL database.

Spend less time converting, more time doing actual work. Convert your JSON file now.