How to Use JSON Data in SQLite

SQLite is a serverless, and open-source RDBMS, which is used to manage the data of a relational database in the form of rows and columns of a table, but data can be in different formats one of them is JSON which stores data in key-value pairs. Now, What is the JSON data, and how can it be used in SQLite? This write-up will correspond to this question.

What is the JSON data?

The JSON is derived from the JavaScript Object Notation, which is used to store and extract data. The JSON has no relation with the JavaScript, the name is similar to it because the JSON also stores the data in the form of objects like JavaScript.

To understand it more precisely, we consider an example of a website, the website is linked with the server and client, now to communicate between the client and server, there are different mediums used like JSON data and XML, but the most convenient way is of JSON data.

There are many advantages of the JSON data, the most prominent of them are:

It contains an easy format that can be read and understood by anyone
It is language independent and supports all the programming languages
It is easy to access
Its syntax is simple so the parsing of the data and execution is faster
It is compatible with a wide range of browsers
It has faster server parsing which allows users to get responses to its queries from the server in a short time
It stores data in arrays so it provides ease in sharing data of any size

How is the JSON data created?

The JSON data can be used to save data like MySQL, SQLite, and PostgreSQL. Data types that are used in JSON data are string, boolean, array, object, null, and number. For understanding, let us store some data in the form of JSON data, we store the names of the students with their id’s and ages as:

[

{

"Id": 1,

"Name": "John",

"Age": 24

},

{

"Id": 2,

"Name": "Paul",

"Age": 19

},

{

"Id": 3,

"Name": "Rogger",

"Age": 21

}

]

Copy the above lines, open the text editor and paste these lines in it, and save the file with any name like we save it with school_data.json. [Note: .json extension should be used with the file name]

The data has been saved in JSON format and is ready to use.

How to convert JSON data to SQLite format?

One of the approaches to use JSON data in SQLite is converting it to a format that SQLite understands. First, convert the .json into the .sql by the online tool offered by sqlizer.io. Open the link in the internet browser, choose the file by clicking on “Select your file…”, and convert it to .sql by clicking on “Convert My File”.

Once the file is converted, download it by clicking on the file name.

A prompt will appear, click on OK button.

Now to open it in SQLite, convert the .sql file to .sqlite by another online tool presented by RebaseData. By clicking on the dialogue box choose the file and click on the CONVERT button.

Once the conversion is completed, a statement will be displayed of a successful conversion, now click on the Download button, to download it in .sqlite format.

A prompt will appear, choose Save and click on OK.

Convert SQL to SQLite from command-line: We can also use the terminal for the conversion of the SQL to SQLite format using the curl command. The general syntax of using the curl command is as:

$ curl -F files[]=input_file_name.sql 'https://www.rebasedata.com/api/v1/convert?outputFormat=sqlite&errorResponse=zip -o output_file_name.zip

For example, we have a file by name, school_data.json, we will use the curl command to convert it from .sql to .sqlite using the terminal as:

$ curl -F files[]=school_data.sql 'https://www.rebasedata.com/api/v1/convert?outputFormat=sqlite&errorResponse=zip' -o RESULT.zip

In the above command, replace the school_data.sql with your sql file name which you want to convert in sqlite. The file has been converted to the zip folder by name result.zip. To confirm it list down the contents by using the ls command:

$ ls

Now, we can either use the file downloaded by the GUI method in Downloads or directly convert from the command line method. Here we will use the file from the Downloads folder so, open the terminal by pressing CTRL+ALT+T.

Go to the Download folder by using “cd”, and list down its contents by the “ls” command.

$ cd Downloads

Unzip the file, result.zip. by using the unzip command:

$ unzip -q RESULT.zip

Again list down contents of the Downloads folder using the ls command:

$ ls

So we can see from the output, the zip file has been unzipped, data.sqlite has been extracted.

How is the JSON data used in SQLite?

The data has been converted to a format that can be modified with SQLite queries. Now, open the SQLite environment by using the command:

$ sudo sqlite3

Now choose the database, data.sqlite as:

.open DATA.sqlite

Show the tables of the database:

.tables

Only one table, school_data, has been displayed. To display the table, execute the following command:

SELECT * FROM school_data;

The data which we created in the JSON data has been displayed in SQLite which can be edited by using the SQLite queries.

Conclusion

JSON data is a format used to transport the data from the server to the website and vice versa. In this write-up, we discussed how the JSON data can be retrieved in SQLite so its data can be edited in SQLite. We just converted the .json file into the .sqlite file using online tools. Then we open the .sqlite file in the SQLite and display its content in the form of SQLite tables. After that, the JSON data will be accessible and editable in SQLite.