Using SQLite with JSON Data

Posted on October 13, 2024 in sql sqlite

Using SQLite to store, query and modify JSON data with full flexibility. Uson JSON columns works best, if you need to support a flexible datamodel and during prototyping.

Using SQLite with JSON Data

SQLite does not have a native JSON column type like PostgreSQL. Instead, JSON is stored as TEXT (or sometimes BLOB), and queried or modified using SQLite’s JSON1 extension. Make sure your SQLite build includes JSON1 (for example, run: SELECT json('null');).

Key points:

• Store JSON in a TEXT column.
• Ensure JSON validity with json_valid().
• Use JSON1 functions such as json_extract, json_set, and json_each.
• You can index JSON paths using expression indexes for performance.
• If you use STRICT tables (SQLite 3.37+), declaring a column as JSON enforces valid JSON automatically.

Example: Blog posts with arbitrary metadata

Table schema

CREATE TABLE blogpost (
  id        INTEGER PRIMARY KEY,
  slug      TEXT NOT NULL UNIQUE,
  title     TEXT NOT NULL,
  body      TEXT NOT NULL,
  metadata  TEXT NOT NULL DEFAULT '{}',

  -- Ensure metadata always contains valid JSON
  CHECK (json_valid(metadata))
);

The metadata column can store any structure you like: tags, SEO data, flags, analytics, experiments, etc.

Inserting data

INSERT INTO blogpost (slug, title, body, metadata)
VALUES
(
  'sqlite-json',
  'Using SQLite with JSON',
  '...',
  json('{
    "tags": ["sqlite", "json"],
    "reading_time_min": 7,
    "seo": {
      "title": "SQLite JSON Guide",
      "canonical": "https://example.com/sqlite-json"
    },
    "draft": false,
    "published_at": "2025-12-01",
    "views": 1234
  }')
),
(
  'draft-post',
  'Unreleased Ideas',
  '...',
  json('{
    "tags": ["notes"],
    "draft": true,
    "assigned_to": "alex",
    "experiments": [
      {"name": "A/B", "variant": "B"}
    ]
  }')
);

Using json('...') helps normalize the JSON and ensures validity.

Querying JSON data

Extract a single value

SELECT
  id,
  title,
  json_extract(metadata, '$.reading_time_min') AS reading_time_min
FROM blogpost;

Filter by a JSON boolean (draft posts)

SELECT id, slug, title
FROM blogpost
WHERE json_extract(metadata, '$.draft') = 1;

JSON true and false are typically returned as 1 and 0.

Query nested fields

SELECT id, slug
FROM blogpost
WHERE json_extract(metadata, '$.seo.title') LIKE '%Guide%';

Query array contents (posts tagged with "json")

SELECT DISTINCT b.id, b.slug, b.title
FROM blogpost AS b
JOIN json_each(b.metadata, '$.tags') AS t
WHERE t.value = 'json';

json_each() expands a JSON array into rows.

Numeric comparisons on JSON values

SELECT
  id,
  slug,
  json_extract(metadata, '$.views') AS views
FROM blogpost
WHERE json_extract(metadata, '$.views') > 1000;

Check if a key exists

SELECT id, slug
FROM blogpost
WHERE json_type(metadata, '$.seo.canonical') IS NOT NULL;

json_type() returns NULL if the path does not exist.

Updating JSON data

Add or update a key

UPDATE blogpost
SET metadata = json_set(metadata, '$.views', 2000)
WHERE slug = 'sqlite-json';

Remove a key

UPDATE blogpost
SET metadata = json_remove(metadata, '$.assigned_to')
WHERE slug = 'draft-post';

Append a value to a JSON array

SQLite does not have a simple “append” operator, so arrays are often rebuilt explicitly:

UPDATE blogpost
SET metadata = json_set(
  metadata,
  '$.tags',
  (
    SELECT json_group_array(value)
    FROM (
      SELECT value FROM json_each(metadata, '$.tags')
      UNION ALL SELECT 'sqlite'
    )
  )
)
WHERE slug = 'draft-post';

Use UNION (instead of UNION ALL) to avoid duplicates.

Indexing JSON for performance

If you frequently query a JSON path, create an expression index:

CREATE INDEX idx_blogpost_draft
ON blogpost (json_extract(metadata, '$.draft'));

CREATE INDEX idx_blogpost_published_at
ON blogpost (json_extract(metadata, '$.published_at'));

These indexes allow SQLite to efficiently filter on JSON values.

Summary

• Store JSON in a TEXT column.
• Enforce validity with json_valid().
• Query with json_extract, json_each, and json_type.
• Update parts of JSON using json_set and json_remove.
• Use expression indexes for frequently queried JSON paths.

This approach works well for flexible, evolving metadata such as blog post attributes, feature flags, or analytics data.

Further Information

The blog post SQLite JSON Virtual Columns and Indexing explains the concept of virtual columns in SQLite, which allow you to create computed columns based on JSON extraction expressions. The article also covers how to efficiently index these virtual columns for better performance when querying JSON fields in your tables, with examples demonstrating schema design, creating indexes, and writing efficient queries with SQLite's built-in JSON support. The corresponding Hacker News Discussion has more information.

You can use https://sqliteonline.com to test the examples in the browser without installing any software.

For more information, consult the SQLite JSON documentation.