SQLite JSON Functions Cheatsheet

SELECT json_extract(payload, '$.type') AS event_type FROM events;

# Read one property from a JSON document.

SELECT payload -> '$.type' AS raw_json,
       payload ->> '$.type' AS text_value
FROM events;

# Read a JSON path with operator syntax.

SELECT json_valid(payload) FROM events;

# Check whether a string contains valid JSON.

SELECT json_type(payload, '$.user') FROM events;

# See whether a value is text, array, object, number, and so on.

UPDATE settings
SET config = json_set(config, '$.theme', 'dark')
WHERE user_id = 1;

# Patch a document with json_set().

UPDATE settings
SET config = json_remove(config, '$.deprecated_flag')
WHERE user_id = 1;

# Delete one path from a document.

SELECT value AS tag
FROM json_each('["sqlite", "json", "fts"]');

# Turn array elements into result rows.

SELECT a.id, je.value AS role
FROM accounts a,
     json_each(a.roles_json) AS je
WHERE je.value = 'admin';

# Search inside per-row arrays.