SQLite JSON

Using JSON fields in SQLite can be a powerful way to store and query structured data within a relational database. JSON (JavaScript Object Notation) is a lightweight data-interchange format that is easy for humans to read and write, and easy for machines to parse and generate. SQLite introduced support for JSON functionality in version 3.9.0. In this article, we'll explore how to use JSON fields in SQLite.

Create a Table with a JSON Column:

Now, let's create a table with a JSON column. For example, we'll create a table to store information about books, including a JSON column for storing additional metadata.

CREATE TABLE books 
 (id INTEGER PRIMARY KEY,    
  title TEXT,    
  author TEXT,
  metadata JSON
);

In this example, the metadata column is defined as a JSON type.

Insert JSON Data:

You can insert JSON data into the table using the INSERT statement. Here's an example:

-- Inserting data with nested fields
INSERT INTO books (title, author, metadata)
VALUES (
    'The Great West',
    'P. Lee Fitzgerald',
    json('{"genre": "Fiction", "year": 1975, "details": {"language": "English", "pages": 150}}')
);

INSERT INTO books (title, author, metadata)
VALUES (
    'The Customer Support',
    'Harper Scott',
    json('{"genre": "Fiction", "year": 1980, "details": {"language": "English", "pages": 281}}')
);

Query JSON Data:

You can query JSON data using the json_extract() function. For example, to retrieve the genre of a book:

SELECT title, json_extract(metadata, '$.genre') AS genre
FROM books;

Now, let's say you want to retrieve books where the language is English and the page count is greater than 200. You can achieve this using nested field querying in the WHERE condition:

Update JSON Data:

To update JSON data, you can use the json_insert(), json_replace(), or json_remove() functions. For instance, to update the genre of a book:

UPDATE books
SET metadata = json_replace(metadata, '$.genre', 'Classic')
WHERE title = 'The Great West';
-- let's check the result

SELECT title, json_extract(metadata, '$.genre') AS genre
FROM books;