Learning the Basics: How To Use JSON in SQLite

In this guide, we explore a fascinating intersection between two popular technologies: JSON and SQLite. Knowing how to use JSON with SQLite is important in modern software development, especially when dealing with complex data structures that may not completely fit in a tabular structure. Whether you're an experienced developer or an eager beginner to expand your knowledge, this tutorial will help you start learning how to use JSON in the SQLite database.

Let's get started!

Empowering Modern Application Data Management With SQLite

SQLite as the most widely deployed and used database engine is a user-friendly database that doesn't require any complicated setup or server connection. SQLite is straightforward and adaptable to diverse applications, which has made it a go-to choice in software development. SQLite has a small binary footprint, generally less than 1 MB, which means it is lighter than other databases. What's more, SQLite complies fully with ACID principles.

Another unique feature of SQLite is that it's well-suited for individual applications and internet-connected devices like smart home gadgets, which are part of the Internet of Things (IoT). Also, despite its simplicity, SQLite has a strong command over standard SQL language. It can handle things like transactions, sub-queries, and triggers. So, SQLite is simple to use, yet still quite powerful.

The capability of SQLite extends beyond just simple data storage. SQLite is efficient and user-friendly, with features such as full-text search and blob support. SQLite also provides an extension mechanism for additional functionality, thereby making it an adaptable tool in the modern software ecosystem.

Fun Fact: Did you know, while many people pronounce SQLite as 'S-Q-Lite' (sequel-light), its creator, Richard Hipp, actually intended it to be pronounced as 'S-Q-L-ite' (ess-que-ell-ite) just like a mineral, emphasizing its robust yet lightweight nature?

Why SQLite Excels in the Realm of Modern Relational Databases

SQLite is a go-to solution for scenarios where full-scale client-server databases might be overkill since it is lightweight and serverless. Because SQLite is self-contained, it doesn't rely on any external dependencies, making it very reliable. SQLite databases are portable across different file systems and architectures, so data migration in the SQLite database is effortless.

SQLite’s typical use cases are across a variety of domains, as the most widely deployed database engine in existence. For example, SQLite is a standard choice for local persistence in applications, especially mobile apps. SQLite is also widely used for data analysis and testing, where its clarity and power are a winning combination. Lastly, SQLite is an ideal choice for website data storage, where it can manage user data, site content, and more.

The performance of SQLite is impressive, with speed often exceeding other famous databases for most common operations.

Why SQLite's JSON Handling Capabilities Make It an Outstanding Choice for Modern Data Management

Managing unstructured data efficiently is a challenge that many developers face. That's where JSON comes in. As a flexible, schema-less data format, JSON is useful for handling data that doesn't fit neatly into a tabular structure.

By storing JSON data in SQLite, you can leverage SQLite's powerful querying capabilities to extract and manipulate your JSON data efficiently. The beauty of this combination lies in the fact that SQLite comes with built-in functions to manage JSON data with simplicity. Moreover, JSON's popularity as a data interchange format and its portability means that JSON data stored in SQLite can easily be shared, migrated, or exported to different systems.

SQLite's JSON support has matured over time. It was first introduced as an extension in version 3.9.0, released in 2015, but later versions came with built-in support for JSON. SQLite lets you save and fetch JSON data using a TEXT column and a suite of JSON functions, such as json(),  json_extract(), json_object(), and json_array().

Understanding SQLite's Powerful Functions for JSON Querying

SQLite manages and manipulates JSON data using JSON functions. Here are the top 10 JSON functions in SQLite, listed as a reference, and the use case of each will be exemplified using a simple SQL query in the following section.

1. json(): This function verifies if a string is a valid JSON. If it is, the function returns the same JSON. If it is not, it returns NULL.
2. json_extract(): This function extracts an object from a JSON string using a path.
3. json_array(): This function creates a JSON array.
4. json_array_length(): This function returns the length of the JSON array.
5. json_insert(): This function inserts a JSON value into a JSON string.
6. json_object(): This function creates a JSON object.
7. json_remove(): This function removes a property from the JSON string.
8. json_replace(): This function replaces a value in a JSON string.
9. json_type(): This function returns the type of the JSON value (like INTEGER, REAL, NULL, TRUE, FALSE, TEXT, and BLOB).
10. json_valid(): This function verifies if a string is a valid JSON.

Practical Series of Examples for Understanding JSON Encode and Decode Functions in SQLite

In this section, we’ve provided minimal examples and a brief explanation for each of the JSON functions we listed in the previous section. We use an example of JSON data from the Dadroit JSON generator. Here is the original JSON to give you context.

The json() Function in SQLite

This query converts the JSON text into a JSON object.

SELECT
    json ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}' ) AS json_object;

The result of this query would be like this:

The json_extract() Function in SQLite

This query extracts the Name value from the JSON object by using it as a path.

SELECT json_extract('{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Name') AS movie_name;

The result of this query would be like this:

The json_array() Function in SQLite

This query makes a new JSON array from the provided inputs.

SELECT
    json_array ( 1, 2, 3 ) AS array_result;

The result would be like this:

The json_type() Function in SQLite

This query retrieves the data type of the Year value from the JSON object.

SELECT
    json_type ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Year' ) AS property_type;

The result would be like this:

The json_array_length() Function in SQLite

This query counts the number of elements in the Cast array in the JSON object.

SELECT
    json_array_length ( '{"Genre":["Comedy","Crime"],"Cast":["Adrian Gratianna","Tani O''Hara","Tessie Delisle"]}', '$.Cast' ) AS array_length;

The result would be like this:

The json_object() Function in SQLite

This query creates a JSON object with the ID and Name key-value pairs.

SELECT
    json_object ( 'ID', 1, 'Name', 'Forgotten in the Planet' ) AS result;

The result would be like this:

The json_insert() Function in SQLite

This query inserts the Director key-value property into the JSON object.

SELECT
    json_insert ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Director', 'Henrie Randell Githens' ) AS insert_movie;

The result would be like this:

The json_remove() Function in SQLite

This query removes the Director key-value pair from the JSON object.

SELECT
    json_remove ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Director' ) AS result_of_remove;

The result would be like this:

The json_replace() Function in SQLite

This query replaces the Year in the JSON object with the new value 1971.

SELECT
     json_replace ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Year', 1971 ) AS result_of_replace;

The result would be like this:

The json_valid() Function in SQLite

This query checks whether the provided string has the correct syntax and structure required for a valid JSON, and returns 1 if it was and 0 otherwise.

SELECT
     json_valid ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}' ) AS result_of_valid;

The result would be like this:

Practical Query Examples for Enhanced SQL Querying Using JSON Functions in SQLite

Now that you’ve learned about the basics of JSON in SQLite, here you are presented with some more examples of a practical workflow with JSON data in the SQLite database, using previously mentioned JSON functions, and the previously mentioned JSON data as the input.

Storing JSON Data in SQLite With Insert Queries

Firstly, you need to insert the JSON into an SQLite database. Let's create a table named movies with one field named data as a text field since you can store JSON in SQLite in a text field. You’ll be using this data field to store and retrieve the JSON values:

CREATE TABLE movies ( data TEXT );

Then let's insert our JSON into the field data of the table movies:

INSERT INTO movies ( data )
 VALUES
     ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Genre":["Comedy","Crime"],"Director":"Henrie Randell Githens","Cast":["Adrian Gratianna","Tani OHara","Tessie Delisle"],"Runtime":90,"Rate":7.0}' );

To edit (replace, insert, remove, etc.) JSON in SQLite, you can use json_replace(), json_insert(), and json_remove() functions.

The following query replaces the movie's name with the new value where the ID is 1:

UPDATE movies
 SET data = json_replace ( data, '$.Name', 'Found in the Universe' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

The following query inserts a new property as a new field into the JSON data stored previously in the row:

UPDATE movies
 SET data = json_insert ( data, '$.Country', 'USA' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

The following query removes the Runtime property from the JSON data stored previously in the row:

UPDATE movies
 SET data = json_remove ( data, '$.Runtime' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Extract JSON Data From SQLite

To retrieve JSON data from SQLite, you can use the json_extract() or the shorthand operator ->:

Select the movie's name:

SELECT
     json_extract ( data, '$.Name' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Or using the -> shorthand operator:

SELECT
     data -> '$.Name'
 FROM
     movies
 WHERE
     data -> '$.ID' = 1;

Retrieve the list of genres:

SELECT
     json_extract ( data, '$.Genre' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Retrieve the first actor from the Cast list:

SELECT
     json_extract ( data, '$.Cast[0]' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Extract the Year and Rate:

SELECT
     json_extract ( data, '$.Year' ) AS Year,
     json_extract ( data, '$.Rate' ) AS Rate
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Concluding Insights

Well done on completing this journey! You've learned how JSON data type and SQLite database can work together. SQLite is a handy tool to have in your toolkit. It's simple yet powerful, and easy to use. Even though it's small, it's full of useful features.

Simply put, SQLite lets us save and fetch JSON data using a text column and some JSON functions. These functions allow us to explore, analyze, and change the JSON data in our SQLite database. SQLite offers a lot of tools to manage JSON data, from adding and changing JSON data to fetching it for various purposes. We covered ten primary JSON functions in SQLite that make handling JSON data simpler. Then, we looked at some more examples of SQL queries from using these JSON functions in SQLite.

Remember: getting good at using JSON with SQLite is a skill that needs to be practiced more thoroughly. So, don't be shy - dive in, experiment, and learn. In the end, if you found this guide helpful, feel free to share it. Enjoy your coding journey!