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: Drag and drop your JSON file into SQLizer

Drag and drop a JSON file to convert to SQL

Step 2: Select your options

Choose your JSON to SQL conversion options

Step 3: Hit convert

You’re nearly done - your SQL file will be ready in a few seconds. It’s really that easy.

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 1

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 2

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.

🖤 SQLizer team.

Hey there! SQLizer’s got some fantastic news for you. If you’re looking to convert your data, here’s the scoop: For personal projects with fewer than 5,000 rows, it’s all on the house!

But wait, there’s more! If you’ve got a beefy CSV file with over 5,000 rows that needs the SQL treatment, we’ve got you covered. Just use the code CONVERTME10, and you’ll snag a cool 10% off your Pro Monthly plan for the first 12 months. Or, if you prefer going big, you can grab a one-time 10% discount on your Pro Annual plan. It’s a win-win, no matter how you slice it!