SQLite JSON

提取值

SQLite 的 JSON 函数自 3.9.0 版本（2015-10-14）起可用。

-- json_extract(json, path [, path...])
SELECT json_extract('{"name":"Alice","age":30}', '$.name');
-- Alice

SELECT json_extract('{"a":{"b":42}}', '$.a.b');
-- 42

-- Array access with [index]
SELECT json_extract('{"tags":["redis","sql"]}', '$.tags[0]');
-- redis

-- Multiple paths in one call
SELECT json_extract('{"a":1,"b":2}', '$.a', '$.b');
-- returns first found: 1

-- Shorthand -> and ->> operators (SQLite 3.38.0+)
SELECT data -> '$.name' FROM users;     -- JSON value
SELECT data ->> '$.name' FROM users;    -- text value

-- Use in WHERE clause
SELECT * FROM products
WHERE json_extract(metadata, '$.stock') > 0;

构建 JSON

-- json_object(key, value, ...)
SELECT json_object('id', 1, 'name', 'Alice', 'active', 1);
-- {"id":1,"name":"Alice","active":1}

-- json_array(value, ...)
SELECT json_array(1, 'two', 3.14, NULL);
-- [1,"two",3.14,null]

-- json(): validate and minify
SELECT json('  { "a" : 1 }  ');
-- {"a":1}

-- json_quote(): wrap value in JSON format
SELECT json_quote('hello');    -- "hello"
SELECT json_quote(42);         -- 42
SELECT json_quote(NULL);       -- null

-- Aggregate: json_group_array / json_group_object
SELECT json_group_array(name) FROM users;
-- ["Alice","Bob","Carol"]

SELECT json_group_object(id, name) FROM users;
-- {"1":"Alice","2":"Bob"}

json_each 与 json_tree

将 JSON 展开为行的表值函数——非常适合查询数组。

-- json_each: iterate top-level array or object keys
SELECT key, value, type
FROM json_each('["apple","banana","cherry"]');
-- key=0, value=apple, type=text
-- key=1, value=banana, type=text

-- json_each with path
SELECT value FROM json_each('{"tags":["redis","sql"]}', '$.tags');

-- Use with a table column (JOIN syntax)
SELECT p.id, e.value AS tag
FROM products p, json_each(p.metadata, '$.tags') AS e;

-- json_tree: recursively expand (like json_each but deep)
SELECT key, value, type, path, fullkey
FROM json_tree('{"a":{"b":[1,2,3]}}');

-- Find products where any tag = 'sale'
SELECT DISTINCT p.*
FROM products p
JOIN json_each(p.metadata, '$.tags') t ON t.value = 'sale';

修改 JSON

-- json_set: set values (creates if missing)
SELECT json_set('{"a":1}', '$.b', 2);
-- {"a":1,"b":2}

-- json_insert: insert only (no-op if path exists)
SELECT json_insert('{"a":1}', '$.a', 99);
-- {"a":1}   (no change, key exists)

-- json_replace: replace only (no-op if path missing)
SELECT json_replace('{"a":1}', '$.b', 2);
-- {"a":1}   (no change, key missing)

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

SELECT json_remove('["x","y","z"]', '$[1]');
-- ["x","z"]

-- json_patch: RFC 7396 merge patch
SELECT json_patch('{"a":1,"b":2}', '{"b":null,"c":3}');
-- {"a":1,"c":3}  (b removed because null, c added)

JSON 函数快速参考