SQLite JSON Functions

SQLite includes a built-in set of JSON functions that let you store, read, modify, and query JSON data directly inside the database. You do not need a separate JSON column type — JSON is stored as TEXT, and the functions parse it on demand.

What JSON Looks Like in SQLite

JSON is a text format for structured data:

Object:   {"name": "Alice", "age": 30, "city": "Delhi"}
Array:    ["red", "green", "blue"]
Nested:   {"user": {"id": 1, "tags": ["admin", "editor"]}}

In SQLite, you store this as a TEXT column value.
The JSON functions let you dig inside that text.

Sample Table

CREATE TABLE users (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  profile TEXT   -- stores JSON
);

INSERT INTO users VALUES
(1, 'Alice', '{"age":30,"city":"Delhi","skills":["SQL","Python"]}'),
(2, 'Bob',   '{"age":25,"city":"Mumbai","skills":["Java","Go"]}'),
(3, 'Carol', '{"age":28,"city":"Delhi","skills":["SQL","R","Excel"]}');

json_extract() — Read a Value from JSON

The path starts with $ (root), then uses . for keys and [n] for array indexes.

-- Extract city from each user's profile
SELECT name, json_extract(profile, '$.city') AS city
FROM users;

name  │ city
──────┼───────
Alice │ Delhi
Bob   │ Mumbai
Carol │ Delhi

-- Extract the first skill (index 0)
SELECT name, json_extract(profile, '$.skills[0]') AS first_skill
FROM users;

name  │ first_skill
──────┼────────────
Alice │ SQL
Bob   │ Java
Carol │ SQL

-- Extract a nested value from deeper inside
-- profile: {"address": {"pin": "110001"}}
SELECT json_extract(profile, '$.address.pin') FROM users;

JSON Path Quick Reference

PATH            MEANING
──────────────────────────────────────────────────
$               Root of the JSON document
$.key           Value of "key" in a JSON object
$.key1.key2     Nested key
$.arr[0]        First element of array "arr"
$.arr[1]        Second element of array "arr"
$.arr[#-1]      Last element of array (FTS5 feature)

json_array_length() — Count Array Elements

SELECT name,
       json_array_length(profile, '$.skills') AS skill_count
FROM users;

name  │ skill_count
──────┼────────────
Alice │ 2
Bob   │ 2
Carol │ 3

json() — Validate and Normalize JSON

-- Validate: returns NULL if input is not valid JSON
SELECT json('{"name":"Alice"}');    -- {"name":"Alice"}
SELECT json('{bad json}');          -- NULL (invalid)
SELECT json_valid('{"ok":true}');   -- 1 (valid)
SELECT json_valid('not json');      -- 0 (invalid)

json_object() — Build a JSON Object

SELECT json_object('id', id, 'name', name) AS json_row
FROM users;

json_row
───────────────────────────────
{"id":1,"name":"Alice"}
{"id":2,"name":"Bob"}
{"id":3,"name":"Carol"}

-- Build from multiple columns
SELECT json_object(
  'id',    id,
  'name',  name,
  'city',  json_extract(profile, '$.city')
) AS record
FROM users;

json_array() — Build a JSON Array

SELECT json_array(1, 2, 3);
-- [1,2,3]

SELECT json_array('SQL', 'Python', NULL);
-- ["SQL","Python",null]

-- Build an array from grouped rows using json_group_array()
SELECT json_group_array(name) AS all_names FROM users;
-- ["Alice","Bob","Carol"]

json_group_object() — Aggregate Rows into JSON

-- Turn result rows into one JSON object: {name: city}
SELECT json_group_object(name, json_extract(profile,'$.city')) AS map
FROM users;

map
─────────────────────────────────────────────
{"Alice":"Delhi","Bob":"Mumbai","Carol":"Delhi"}

json_set(), json_insert(), json_replace() — Modify JSON

-- Update Alice's city inside her profile JSON
SELECT json_set(profile, '$.city', 'Bangalore') AS updated
FROM users WHERE name = 'Alice';

updated:
{"age":30,"city":"Bangalore","skills":["SQL","Python"]}

-- json_insert: only adds if path does not exist
SELECT json_insert('{"a":1}', '$.b', 2);
-- {"a":1,"b":2}

-- json_replace: only updates if path already exists
SELECT json_replace('{"a":1}', '$.a', 99);
-- {"a":99}

-- json_remove: delete a key or array element
SELECT json_remove('{"a":1,"b":2}', '$.b');
-- {"a":1}

json_each() — Expand JSON Array to Rows

-- Expand skills array to individual rows
SELECT u.name, j.value AS skill
FROM users u, json_each(u.profile, '$.skills') j;

name  │ skill
──────┼───────
Alice │ SQL
Alice │ Python
Bob   │ Java
Bob   │ Go
Carol │ SQL
Carol │ R
Carol │ Excel

Filtering by JSON Value

-- Find users living in Delhi
SELECT name FROM users
WHERE json_extract(profile, '$.city') = 'Delhi';

name
──────
Alice
Carol

-- Find users with more than 2 skills
SELECT name FROM users
WHERE json_array_length(profile, '$.skills') > 2;

name
──────
Carol

JSON Index for Performance

-- Index on an extracted JSON value for faster queries
CREATE INDEX idx_city ON users(json_extract(profile, '$.city'));

-- Now this query uses the index:
SELECT name FROM users
WHERE json_extract(profile, '$.city') = 'Delhi';

Key Takeaways

• SQLite stores JSON as TEXT; built-in functions parse it on demand
• json_extract(col, '$.key') reads a value from JSON using a path expression
• json_array_length() counts array elements; json_each() expands arrays to rows
• json_object() and json_array() build JSON from column values
• Create an index on json_extract() to speed up repeated JSON value lookups