How to Convert a JSON Dataset Into SQL Format Without Coding

There are a variety of ways to turn JSON data into ready-to-use SQL format. But most of them are time-consuming and even annoying if you have to unwrap JSON files to SQL daily.

In this post, I’d like to show you a completely new, pretty easy, and effective way of converting a JSON dataset into a ready-to-use database without coding.

For this purpose, I’ll use TABLUM.IO. It is a data-management SAAS solution designed for data preparation tasks.

If you prefer to watch the tutorial, below is the YouTube video:

To walk you through this short journey I’ll take a test public dataset with the historical weather forecast for the city of Barcelona (latitude and longitude coordinates 41.39174525563591, 2.1621596955134543).

I saved this dataset on my computer and now I’m ready to play around with it!

2. Now, inside the TABLUM.IO you need to drag-n-drop your JSON data into the area shown below:

While the file is being imported, TABLUM.IO automatically processes it on the fly:

Is that all? It Is!

TABLUM.IO recognizes data type, converts a nested structure into a flat format, cleans data based on specified parameters, and performs other comprehensive data-processing tasks which I’m going to cover in my next posts.

Here is what we’ve got after two seconds:

It took me nothing to turn a JSON file into a relational database! The database schema has been generated automatically:

But let’s go further. Now I’m going to “Save” this dataset and “Rename” it:

Great! I can’t wait to query my database using SQL.

I need to press the “+” button to create a new view:

What I want to do is to find out the weather conditions in Barcelona for the near future. I am interested in the dates with the best balance between average daily temperature and low humidity level. For this purpose, I’ll use the following SQL query:

SELECT
    toDate(time) as date,
    avg(nt1_details2_air_temperature) as avg_temp,
    avg(c_1_details2_relative_humidity) as avg_hum
FROM DS_.V_1001
GROUP BY date
ORDER BY avg_hum ASC, avg_temp DESC
LIMIT 5

I put this query into the SQL console and run it:

The query has created a new SQL table with the answer:

Now I can save the results on my computer in multiple formats including CSV, XML, JSON, and TSV:

As one of the key features, TABLUM.IO provides direct SQL access to the imported data via ClickHouse interface. It means you can import and cleanse data with TABLUM.IO and connect your favorite data analytics app directly to it. But I’ll cover this topic in the next posts.

Stay tuned :)