DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • NFT Wallets Unleashed: A Data Structures and Application Design Journey
  • How To Select the Right Vector Database for Your Enterprise GENERATIVE-AI Stack
  • Vector Databases Are Reinventing How Unstructured Data Is Analyzed
  • JSON Minify Full Guideline: Easy For You

Trending

  • Medallion Architecture: Efficient Batch and Stream Processing Data Pipelines With Azure Databricks and Delta Lake
  • How AI Agents Are Transforming Enterprise Automation Architecture
  • Understanding IEEE 802.11(Wi-Fi) Encryption and Authentication: Write Your Own Custom Packet Sniffer
  • Build Your First AI Model in Python: A Beginner's Guide (1 of 3)
  1. DZone
  2. Data Engineering
  3. Databases
  4. Learning the Basics: How To Use JSON in SQLite

Learning the Basics: How To Use JSON in SQLite

This beginner-friendly guide explains how to work with JSON in the SQLite database: benefits and use cases, essential JSON functions in SQLite, and more.

By 
Peter Varon user avatar
Peter Varon
·
Sep. 26, 23 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
2.0K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Bar graph comparing query performance across SQLite, MongoDB, PostgreSQL, and MySQL.

Using the ClickHouse benchmark tool, we compared the performance of leading databases, MySQL, PostgreSQL, SQLite, and MongoDB, across diverse queries.

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.

This is a sample json file opened in Dadroit JSON Viewer, to be used throughout the post about a movie record, consisting of these fields: "ID", "Name", "Year", "Genre" and "Cast" as arrays, "Director", "Runtime", and "Rate”.

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:

json_object
{"ID":1,"Name":"Forgotten in the Planet","Year":1970}

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:

movie_name
Forgotten in the Planet

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:

array_result
[1,2,3]

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:

property_type
integer

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:

array_length
3

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:

result
{"ID":1,"Name":"Forgotten in the Planet"}

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:

insert_movie
{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}

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:

result_of_remove
{"ID":1,"Name":"Forgotten in the Planet","Year":1970}

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:

result_of_replace
{"ID":1,"Name":"Forgotten in the Planet","Year":1971,"Director":"Henrie Randell Githens"}

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:

result_of_valid
1

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!

Data management Data structure JSON SQLite

Published at DZone with permission of Peter Varon. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • NFT Wallets Unleashed: A Data Structures and Application Design Journey
  • How To Select the Right Vector Database for Your Enterprise GENERATIVE-AI Stack
  • Vector Databases Are Reinventing How Unstructured Data Is Analyzed
  • JSON Minify Full Guideline: Easy For You

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!