Using JSON Data in SQLite: A Step-by-Step Guide

Using JSON with SQLite: Comprehensive Guide

SQLite, starting from version 3.9.0, includes JSON1, a built-in extension that provides functions to handle JSON data within the database. It allows developers to store, retrieve, and manipulate JSON data using SQL commands. This guide explains how to work with JSON in SQLite using the JSON1 extension, including examples of storage, queries, and JSON manipulation functions.

Why use JSON in SQLite?

1. Flexibility: Store semi-structured or hierarchical data.

2. Compatibility: JSON is widely used for data exchange in web and mobile applications.

3. Efficiency: Perform operations directly on JSON data within the database.

Enabling the JSON1 Extension

Ensure your SQLite version supports the JSON1 extension by running:

sqlite3 --version

If your version is older than 3.9.0, update SQLite to use JSON functionality.

Storing JSON in SQLite

Code:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    details TEXT -- Store JSON data as TEXT
);

-- Insert JSON data into the table
INSERT INTO products (name, details)
VALUES ('Laptop', '{"brand": "Dell", "price": 1200, "features": ["i7", "16GB RAM", "512GB SSD"]}');

Explanation

• JSON is stored as a TEXT string.
• Ensure JSON is valid to use JSON1 functions for querying and manipulation.

Querying JSON Data

Example 1: Extract a JSON Value

Retrieve the brand from the details column.

Code:

SELECT json_extract(details, '$.brand') AS brand
FROM products;

Explanation

• json_extract: Extracts a specific key's value using a JSON path.
• $.brand: Refers to the brand key in the JSON object.

Example 2: Check for a Key

Find records with a specific key in the JSON data.

Code:

SELECT name
FROM products
WHERE json_valid(details) AND json_extract(details, '$.price') > 1000;

Explanation

• json_valid: Ensures the details column contains valid JSON.
• json_extract(details, '$.price') > 1000: Filters products with a price greater than 1000.

JSON Manipulation Functions

1. json_set

Update a JSON object by adding or modifying a key-value pair.

Code:

UPDATE products
SET details = json_set(details, '$.discount', 10)
WHERE id = 1;

Explanation

• Adds a discount key with a value of 10 to the JSON object in the details column.

2. json_remove

Remove a key from a JSON object.

Code:

UPDATE products
SET details = json_remove(details, '$.features')
WHERE id = 1;

Explanation

• Removes the features key from the JSON object in the details column.

3. json_array_length

Count the number of elements in a JSON array.

Code:

SELECT json_array_length(json_extract(details, '$.features')) AS feature_count
FROM products;

Explanation

• Extracts the features array and counts its elements.

Combining JSON with Other SQLite Features

Example: Aggregation with JSON

Get the average price of all products stored in JSON.

Code:

SELECT AVG(json_extract(details, '$.price')) AS avg_price
FROM products;

Advantages of Using JSON in SQLite

1. Hybrid Storage: Combines relational and JSON storage for mixed-use cases.

2. In-Database Manipulation: Perform JSON queries and updates without external parsing.

3. Performance: Efficient for small-scale JSON data processing.

4. Ease of Use: Reduces the need for additional libraries or integrations.

Practical Guides to SQLite Snippets and Examples.