Efficient data storage and retrieval are crucial aspects of modern applications. JSON (JavaScript Object Notation) is a popular data transmission format because it is simple and adaptable. SQLite, a self-contained and serverless relational database, is widely used for local data storage. This guide will explore the way to store and retrieve JSON data in SQLite using the command line interface (CLI).
What is JSON?
JSON is a simple data exchange format that is simple for both people and machines to comprehend and produce. It is derived from JavaScript, but is language-independent, making it a popular choice for web services and mobile applications.
Store and Retrieve JSON Data in SQLite
SQLite supports various data types, including text, numeric, and blob (binary large object). Since JSON is a text-based format, we can store and retrieve JSON data in SQLite by following the below-given steps.
Step 1: Creating a Table in SQLite
Let’s consider an example where we have a table called Employees with three columns: emp_id (integer), name (text), and department (text).
emp_id INTEGER PRIMARY KEY,
name TEXT,
department TEXT
);
sqlite> INSERT INTO Employees (emp_id, name, department) VALUES (1, 'Elena', 'HR');
sqlite> INSERT INTO Employees (emp_id, name, department) VALUES (2, 'Jeremy', 'Marketing');
sqlite> INSERT INTO Employees (emp_id, name, department) VALUES (3, 'Larry', 'HR');
sqlite> INSERT INTO Employees (emp_id, name, department) VALUES (4, 'Janet', 'Sales');
sqlite> INSERT INTO Employees (emp_id, name, department) VALUES (5, 'Liam', 'IT');
sqlite> INSERT INTO Employees (emp_id, name, department) VALUES (6, 'Harry', 'HR');
sqlite> INSERT INTO Employees (emp_id, name, department) VALUES (7, 'Alexis', 'Research');
The Employees table is created with the CREATE TABLE command. It has three columns: the primary key, emp_id, of type INTEGER, name, of type TEXT, and department, of type TEXT. Each employee’s identity and uniqueness are guaranteed by the primary key.
These instructions insert the Employees database with sample data. The columns are listed clearly in each INSERT statement, followed by the relevant values for each column. In this fashion, each employee is given a special emp_id along with their name and department.
Step 2: Storing JSON Data
To store JSON data, we need to convert it to a string representation before inserting it into the table. This can be done by serializing the JSON object using the PRAGMA json_encode function.
When inserting JSON data into the SQLite database, it is important to ensure that the data is properly formatted. This means that the JSON data should be in the correct format and the data types of the columns should match the data types in the JSON data.
Here’s an example using the SQLite CLI:
INSERT INTO Employees (department) VALUES (json('{"name": "HR", "salary": 45000}'));
Step 3: Retrieving JSON Data from SQLite
To retrieve JSON data from SQLite, we need to perform the reverse process of storing. First, we query the table to fetch the JSON data as a string, and then we deserialize it back into a JSON object. Here’s an example of querying using the SELECT query:
The above SQL query is a SELECT statement that obtains the employee with an emp_id value of 1 from the Employees table’s department column.
Output
The JSON data will be printed as a string. There are several programming languages and packages that we may use to deserialize it back into a JSON object.
Conclusion
Storing and retrieving JSON data in SQLite using the command line interface provides a convenient way to manage structured data within applications. By leveraging SQLite’s text data type and JSON manipulation functions, we can seamlessly integrate JSON data with a relational database.
