SQLite JSON: Store, Query, Update and Index JSON (Tested Examples)

Ninad Pathak

October 7, 2026

Some data doesn’t fit neat columns, like per-user settings or an API response. SQLite can store it as JSON text and still query the fields inside it. In this article I’ll walk you through storing and querying JSON in SQLite, then updating and indexing it, with examples I’ve run.

TLDR

Yes, SQLite supports JSON. It stores JSON as text, and the JSON functions are built in by default since SQLite 3.38.0.
Store JSON in a TEXT column with CHECK (json_valid(data)) so invalid JSON can’t get in.

Read a value with data ->> '$.name', and filter with WHERE data ->> '$.address.city' = 'London'.
Update fields with json_set() and remove them with json_remove().
Expand arrays into rows with json_each().
Index a JSON field with CREATE INDEX … ON users (data ->> 'email').

What is JSON support in SQLite?

SQLite’s JSON support is a set of built-in functions and two operators, -> and ->>, that read and change JSON stored as ordinary text. The docs say it directly: “SQLite stores JSON as ordinary text” (SQLite JSON functions). There’s no separate JSON type. The JSON functions are built into SQLite by default since version 3.38.0 (2022-02-22).

SQLite JSON syntax

These are the pieces you’ll use on every JSON column:

Syntax	What it does
`json_valid(x)`	1 if `x` is valid JSON, 0 if not
`x -> '$.path'`	The value at `path`, as JSON
`x ->> '$.path'`	The value at `path`, as a plain SQL value
`json_extract(x, '$.path')`	Same as `->>` for a single path
`json_set(x, '$.path', value)`	A copy of `x` with `path` set
`json_remove(x, '$.path')`	A copy of `x` without `path`
`json_each(x)`	One row per element of an array or object

A path starts with $, the whole document. $.address.city reaches a nested field.

How to store and query JSON in SQLite

To show you how this works, I will use a users table that keeps each user’s profile as one JSON document.

Step 1: Create a JSON column with a validity check

Use a TEXT column. A CHECK constraint with json_valid() rejects anything that isn’t valid JSON:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT NOT NULL CHECK (json_valid(data)));
sqlite> INSERT INTO users (data) VALUES ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"], "address": {"city": "London"}}'), ('{"name": "Linus", "email": "linus@example.com", "tags": ["beta"], "address": {"city": "Helsinki"}}');

sqlite> INSERT INTO users (data) VALUES ('{name: Grace}');
Error: stepping, CHECK constraint failed: json_valid(data) (19)

Step 2: Read values with json_extract, -> and ->>

sqlite> SELECT id, json_extract(data, '$.name') AS name, data ->> '$.address.city' AS city, data -> '$.tags' AS tags FROM users;
id  name   city      tags
--  -----  --------  ----------------
1   Ada    London    ["admin","beta"]
2   Linus  Helsinki  ["beta"]

-> returns JSON, so a string keeps its quotes, and ->> returns a plain SQL value. The docs: “-> always returns a JSON representation of that subcomponent and the ->> operator always returns an SQL representation” (the -> and ->> operators):

sqlite> SELECT data -> '$.name' AS arrow, data ->> '$.name' AS double_arrow, typeof(data -> '$.name') AS t1, typeof(data ->> '$.name') AS t2 FROM users WHERE id = 1;
arrow  double_arrow  t1    t2
-----  ------------  ----  ----
"Ada"  Ada           text  text

Step 3: Filter rows by a JSON field

Use a full JSON path that starts with $. The short form without $ is there for PostgreSQL compatibility: “If the right operand is an alphanumeric text label X, then it is interpreted as the JSON path ‘$.X’” (json1). A dotted name like 'address.city' isn’t an alphanumeric label, so it matches nothing. Use '$.address.city', or chain the operators:

sqlite> SELECT count(*) AS matches FROM users WHERE data ->> 'address.city' = 'London';
matches
-------
0
sqlite> SELECT id, data ->> 'name' AS name FROM users WHERE data ->> '$.address.city' = 'London';
id  name
--  ----
1   Ada
sqlite> SELECT id, data ->> 'name' AS name FROM users WHERE data -> 'address' ->> 'city' = 'London';
id  name
--  ----
1   Ada

Step 4: Update JSON with json_set and json_remove

SQLite has three functions for changing JSON, and each returns a new JSON value. They differ only in whether they overwrite existing keys and create missing ones (json_set, json_insert, json_replace):

Function	Overwrites an existing key	Creates a missing key
`json_set()`	Yes	Yes
`json_insert()`	No	Yes
`json_replace()`	Yes	No

Wrap a JSON literal in json() so it’s stored as JSON true, not the text “true”:

sqlite> UPDATE users SET data = json_set(data, '$.email', 'ada@sqldocs.org', '$.verified', json('true')) WHERE id = 1;
sqlite> SELECT data FROM users WHERE id = 1;
data
------------------------------------------------------------------------------------------------------------
{"name":"Ada","email":"ada@sqldocs.org","tags":["admin","beta"],"address":{"city":"London"},"verified":true}

sqlite> UPDATE users SET data = json_remove(data, '$.verified') WHERE id = 1;
sqlite> SELECT data ->> 'email' AS email, data -> 'verified' AS verified FROM users WHERE id = 1;
email            verified
---------------  --------
ada@sqldocs.org

Examples

Each example below continues on the same users table.

Expand a JSON array into rows

json_each() is a table-valued function. It returns one row for each element of an array or object, so you can join it to the table (json_each):

sqlite> SELECT users.id, data ->> 'name' AS name, tag.value AS tag FROM users, json_each(users.data, '$.tags') AS tag ORDER BY users.id, tag.value;
id  name   tag
--  -----  -----
1   Ada    admin
1   Ada    beta
2   Linus  beta

Going the other way, json_group_array() and json_object() build JSON from rows:

sqlite> SELECT json_group_array(data ->> 'name') AS names, json_object('count', count(*)) AS summary FROM users;
names            summary
---------------  -----------
["Ada","Linus"]  {"count":2}

Index a JSON field

An index on the same expression you filter by lets SQLite search instead of scanning every row. EXPLAIN QUERY PLAN confirms the index is used:

sqlite> CREATE INDEX idx_users_email ON users (data ->> 'email');
sqlite> EXPLAIN QUERY PLAN SELECT id FROM users WHERE data ->> 'email' = 'linus@example.com';
QUERY PLAN
`--SEARCH users USING COVERING INDEX idx_users_email (<expr>=?)

The query has to use exactly the indexed expression, here data ->> 'email'.

Load a JSON file into a table

The sqlite3 shell adds a readfile() function that “reads the entire content of the file named X” (sqlite3 shell). Combined with json_each(), it turns a JSON array in a file into one row per element. I used this file, people.json:

[
  {"name": "Grace", "email": "grace@example.com", "tags": ["admin"]},
  {"name": "Margaret", "email": "margaret@example.com", "tags": []}
]

sqlite> INSERT INTO users (data) SELECT value FROM json_each(readfile('people.json'));
sqlite> SELECT id, data ->> '$.name' AS name, typeof(data) AS stored_as FROM users ORDER BY id;
id  name      stored_as
--  --------  ---------
1   Ada       text
2   Linus     text
3   Grace     text
4   Margaret  text

The two new rows are stored as text, the same as the rows inserted by hand.

Store and read JSON from Python

In Python, convert a dict with json.dumps() before inserting it, and back with json.loads() after reading. SQL can query the JSON in between:

import json, sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT NOT NULL CHECK (json_valid(data)))")
profile = {"name": "Ada", "tags": ["admin", "beta"], "address": {"city": "London"}}
con.execute("INSERT INTO users (data) VALUES (?)", (json.dumps(profile),))  # dict -> JSON text

city = con.execute("SELECT data ->> '$.address.city' FROM users").fetchone()[0]
print("city:", city)
row = con.execute("SELECT data FROM users").fetchone()[0]
print("back to a dict:", json.loads(row)["tags"])                         # JSON text -> dict

$ python3 json_python.py
city: London
back to a dict: ['admin', 'beta']

Edge cases

Both of these trip people up once the basics work.

JSONB is a different, SQLite-only format

SQLite 3.45.0 added JSONB, a binary format stored as a BLOB. The same functions and operators accept it, and json() turns it back into text. It’s for SQLite only: “Applications should not use JSONB outside of SQLite”, and it “is not the same as PostgreSQL’s” (JSONB):

sqlite> CREATE TABLE events (id INTEGER PRIMARY KEY, body BLOB); INSERT INTO events (body) VALUES (jsonb('{"type": "click", "x": 10}'));
sqlite> SELECT typeof(body) AS stored_as, body ->> 'type' AS type, json(body) AS as_text FROM events;
stored_as  type   as_text
---------  -----  -----------------------
blob       click  {"type":"click","x":10}

A path without $ only works for one level

As Step 3 showed, data ->> 'name' works, but data ->> 'address.city' silently matches nothing. Write '$.address.city' for anything nested.

Conclusion

SQLite handles JSON well without a JSON type. Keep JSON in a TEXT column with CHECK (json_valid(...)) and read it with ->>. Change it with json_set(), and index the fields you filter on. For data that fits fixed columns apart from a few flexible attributes, that combination covers what a document store would give you.