雖然 SQLite 是一個(gè)嵌入式數(shù)據(jù)庫，但是它支持 JSON 存儲，并且通過 JSON1 擴(kuò)展插件提供了許多 JSON 函數(shù)和運(yùn)算符；同時(shí)，SQLite 表達(dá)式索引（Indexes On Expressions）和生成列（Generated Column）為 JSON 數(shù)據(jù)提供了索引支持，從而實(shí)現(xiàn)了文檔存儲和處理功能。

本文給大家介紹一下如何將 SQLite 作為一個(gè)文檔數(shù)據(jù)庫使用。

一個(gè)文檔存儲案例

我們首先來看一個(gè)簡單的案例：

sqlite>?create?table?docs( ? ...> id int not null primary key, ? ...> content text ? ...>  sqlite> insert into docs(id, content)  ? ...> values (1, json('{"name":"apple", "price":6.50}'));

首先，我們創(chuàng)建了一個(gè)測試表 docs；其中 content 字段用于存儲 JSON 文檔，字段類型為 TEXT。

然后，我們使用 json() 函數(shù)確保了輸入字符串符合 JSON 格式要求，如果參數(shù)不滿足 JSON 格式要求將會返回錯(cuò)誤。例如：

sqlite> select json('"not a valid json string');Error: malformed JSON

接下來我們可以在查詢中使用 JSON 文檔中的內(nèi)容：

sqlite> select * from docs  ? ...> where json_extract(content, '$.name') = 'apple'; 1|{"name":"apple","price":6.50}

json_extract() 函數(shù)用于從 JSON 文檔中返回 name 節(jié)點(diǎn)的數(shù)據(jù)，具體的函數(shù)介紹參考下文。

如果想要對以上查詢進(jìn)行優(yōu)化，可以使用表達(dá)式索引。例如：

sqlite> create index docs_name on docs(json_extract(content, '$.name'));
sqlite> explain query plan ? ...> select * from docs ???...>?where?json_extract(content,?'$.name')?=?'apple';QUERY PLAN`--SEARCH TABLE docs USING INDEX docs_name (<expr>=?)

我們對文檔 content 中的 name 節(jié)點(diǎn)進(jìn)行了索引，查詢時(shí)可以通過索引提高檢索的速度。

目前還有一個(gè)問題，SQLite 并沒有提供原始的 JSON 數(shù)據(jù)類型，content 字段中仍然可以插入任何數(shù)據(jù)。這個(gè)問題我們可以通過生成列來解決。例如：

sqlite>?drop?table?docs;
sqlite> create table docs( ? ...> content text, ? ...> id int generated always as (json_extract(content, '$.id')) virtual not null ? ...> ) sqlite> insert into docs(content)  ? ...> values (json('{"id":1, "name":"apple", "price":6.50}'));

我們將 id 字段定義為一個(gè)非空的虛擬生成列，數(shù)據(jù)來自于 content 字段而不會占用額外的存儲。json_extract() 函數(shù)的使用意味著插入無效的 JSON 文檔同樣會返回 Error: malformed JSON 錯(cuò)誤信息。例如：

sqlite> insert into docs(content) values (json('{"id":1, "name":"apple", "price":6.50]}'));Error:?malformed?JSON
sqlite> insert into docs(content) values (json('{"name":"apple", "price":6.50}'));Error: NOT NULL constraint failed: docs.id

第一個(gè)錯(cuò)誤是因?yàn)槲臋n不是有效的 JSON 格式，第二個(gè)錯(cuò)誤是因?yàn)槲臋n中沒有 id 節(jié)點(diǎn)。

由于 SQLite 生成列無法作為主鍵字段，我們不能將 id 字段定義為該表的主鍵。不過，我們可以為 id 字段創(chuàng)建一個(gè)唯一索引，加上非空約束后的效果和主鍵一樣。

sqlite>?create?unique?index?docs_id?on?docs(id);
sqlite> insert into docs(content) values (json('{"id":1, "name":"banana", "price":8.00}'));Error: UNIQUE constraint failed: docs.id

接下來我們詳細(xì)介紹一下 JSON1 插件。

JSON1 插件概述

• json(json)，驗(yàn)證輸入?yún)?shù)是否符合 JSON 格式并返回結(jié)果。

• json_array(value1,value2,...)，創(chuàng)建一個(gè) JSON 數(shù)組。

• json_array_length(json)，返回 JSON 數(shù)組中的元素個(gè)數(shù)。

• json_array_length(json,path)，返回指定路徑上的 JSON 數(shù)組中的元素個(gè)數(shù)。

• json_extract(json,path,...)，提取指定路徑上的元素。

• json_insert(json,path,value,...)，在指定路徑上插入元素。

• json_object(label1,value1,...)，創(chuàng)建一個(gè) JSON 對象。

• json_patch(json1,json2)，增加、修改或者刪除 JSON 對象中的元素。

• json_remove(json,path,...)，刪除指定路徑上的元素。

• json_replace(json,path,value,...)，替換指定路徑上的元素。

• json_set(json,path,value,...)，設(shè)置指定路徑上的元素。

• json_type(json)，返回最外層元素的 JSON 數(shù)據(jù)類型。

• json_type(json,path)，返回指定路徑上的元素的 JSON 數(shù)據(jù)類型。

• json_valid(json)，驗(yàn)證輸入?yún)?shù)是否符合 JSON 格式。

• json_quote(value)，將 SQL 數(shù)據(jù)轉(zhuǎn)換為 JSON 格式。

• json_group_array(value)，返回聚合后的 JSON 數(shù)組。

• json_group_object(name,value)，返回聚合后的 JSON 對象。

以下 2 個(gè)是表值函數(shù)：

• json_each(json) 和 json_each(json,path)，將 JSON 元素轉(zhuǎn)換為 SQL 數(shù)據(jù)行。

• json_tree(json) 和 json_tree(json,path)，遞歸遍歷 JSON 元素并轉(zhuǎn)換為 SQL 數(shù)據(jù)行。

json1 插件目前不支持 JSON 文檔的二進(jìn)制編碼（BSON）。經(jīng)過試驗(yàn)沒有找到比純文本編碼格式明顯更小或者更快的二進(jìn)制編碼，目前的實(shí)現(xiàn)可以支持 1GB/s 的 JSON 文本解析。所有的 json1 函數(shù)參數(shù)都不接受 BLOB，如果指定這種參數(shù)將會報(bào)錯(cuò)，因?yàn)?BLOB 是為了將來增強(qiáng)而保留的二進(jìn)制 JSON 存儲類型。

json1 擴(kuò)展名中的數(shù)字“1”是故意設(shè)計(jì)的，設(shè)計(jì)人員預(yù)計(jì)將來會基于 json1 的經(jīng)驗(yàn)創(chuàng)建新的不兼容的 JSON 擴(kuò)展。一旦獲得足夠的經(jīng)驗(yàn)，某種JSON 擴(kuò)展可能會被添加到 SQLite 核心代碼中。目前，對 JSON 的支持仍然是通過擴(kuò)展的形式實(shí)現(xiàn)。

通用參數(shù)說明

對于第一個(gè)參數(shù)是 JSON 的函數(shù)，該參數(shù)可以是一個(gè) JSON 對象、數(shù)組、數(shù)字、字符串或者 null。SQLite 數(shù)字和 NULL 值分別被當(dāng)作 JSON 數(shù)字和 null，SQLite 文本可以被當(dāng)作? JSON 對象、數(shù)組或者字符串。如果 SQLite 本文不符合 JSON 對象、數(shù)組或者字符串格式，函數(shù)將會返回錯(cuò)誤， json_valid() 和 json_quote() 函數(shù)除外。

為了驗(yàn)證格式的有效性，JSON 輸入?yún)?shù)中開頭和結(jié)尾的空白字符將會被忽略。根據(jù) JSON 規(guī)范，內(nèi)部的空白字符也會被忽略。這些函數(shù)完全遵循 RFC-7159 JSON 語法。

對于接受 PATH 參數(shù)的函數(shù)，PATH 必須滿足一定的格式，否則函數(shù)將會返回錯(cuò)誤。滿足格式的 PATH 必須是一個(gè)以“\$”符號開頭，加上零個(gè)或多個(gè)“.objectlabel”或者“[arrayindex]”組成的文本。

其中，arrayindex 通常是一個(gè)非負(fù)的整數(shù) N，表示選擇數(shù)組的第 N 個(gè)元素，從 0 開始計(jì)數(shù)。arrayindex 也可以使用“#-N”的形式，表示選擇從右邊開始的第 N 個(gè)元素。數(shù)組最后一個(gè)元素是“#-1”，字符“#”相當(dāng)于數(shù)據(jù)元素的個(gè)數(shù)。

對于接受 value 參數(shù)（value1，value2 等）的函數(shù)，這些參數(shù)通常被當(dāng)作引號引用的字符串常量，并且最終解析為 JSON 字符串?dāng)?shù)據(jù)。不過，如果某個(gè) value 參數(shù)直接來自另一個(gè) json1 函數(shù)的輸出結(jié)果，那么該參數(shù)將被當(dāng)作實(shí)際的 JSON，傳入的將會是完整的 JSON 而不是字符串常量。

例如，在下面的 json_object() 函數(shù)調(diào)用中，value 參數(shù)看起來像是一個(gè)滿足格式的 JSON 數(shù)組。但是，由于它是一個(gè)普通的 SQL 文本，因此被解析為一個(gè)字符串常量，并且作為一個(gè)字符串被添加到結(jié)果中：

SELECT json_object('ex','[52,3.14159]');json_object('ex','[52,3.14159]')|--------------------------------|{"ex":"[52,3.14159]"} ? ? ? ? ? |

但是，如果一個(gè)外部 json_object() 調(diào)用中的 value 參數(shù)來自另一個(gè)函數(shù)的結(jié)果，例如 json() 或者 json_array()，將會被解析為實(shí)際的 JSON 并且作為 JSON 添加到結(jié)果中：

SELECT json_object('ex',json('[52,3.14159]'));json_object('ex',json('[52,3.14159]'))|--------------------------------------|{"ex":[52,3.14159]} ? ? ? ? ? ? ? ? ? |

SELECT json_object('ex',json_array(52,3.14159));json_object('ex',json_array(52,3.14159))|----------------------------------------|{"ex":[52,3.14159]} ? ? ? ? ? ? ? ? ? ? |

總之，json 參數(shù)總是被解釋為 JSON，無論該參數(shù)的值來自何處。但是 value 參數(shù)只有當(dāng)其直接來自另一個(gè) json1 函數(shù)時(shí)才被解釋為 JSON。

JSON 函數(shù)說明

接下來我們詳細(xì)介紹 json1 擴(kuò)展中的各種函數(shù)。

json()

json(X) 函數(shù)可以驗(yàn)證參數(shù) X 符合 JSON 字符串的格式，并且返回一個(gè)精簡版的 JSON 字符串（刪除了所有不必要的空白字符）。如果 X 不是一個(gè)格式正確的 JSON 字符串，函數(shù)將會返回錯(cuò)誤。

如果參數(shù) X 是一個(gè)包含重復(fù)標(biāo)簽的 JSON 對象，不確定是否保留重復(fù)元素。當(dāng)前實(shí)現(xiàn)保留了重復(fù)元素，但是將來可能會刪除重復(fù)元素，而且沒有提示。例如：

SELECT json(' { "this" : "is", "a": [ "test" ] } ') AS doc;doc ? ? ? ? ? ? ? ? ? ? ? |--------------------------|{"this":"is","a":["test"]}|

SELECT json(' { "this" : "is", "a": [ "test" } ') AS doc;SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (malformed JSON)

json_array()

json_array(value1,value2,...) 函數(shù)接收零個(gè)或多個(gè)參數(shù)，并且返回一個(gè)由這些參數(shù)組成的 JSON 數(shù)組。如果任何參數(shù)是 BLOB，函數(shù)將會返回錯(cuò)誤。

TEXT 類型的參數(shù)通常會轉(zhuǎn)換為引號包含的 JSON 字符串。但是，如果該參數(shù)來自其他 json1 函數(shù)的輸出，將會作為 JSON 傳入。這種處理方式可以實(shí)現(xiàn) json_array() 和 json_object() 函數(shù)的嵌套調(diào)用。json() 函數(shù)也可以將字符串轉(zhuǎn)換為 JSON。

例如：

SELECT json_array(1,2,'3',4) AS doc;doc ? ? ? ?|-----------|[1,2,"3",4]|
SELECT json_array('[1,2]') AS doc;doc ? ? ?|---------|["[1,2]"]|
SELECT json_array(json_array(1,2)) AS doc;doc ? ?|-------|[[1,2]]|
SELECT json_array(1,null,'3','[4,5]','{"six":7.7}') AS doc;doc ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |------------------------------------|[1,null,"3","[4,5]","{\"six\":7.7}"]|
SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) AS doc;doc ? ? ? ? ? ? ? ? ? ? ? ? ? |------------------------------|[1,null,"3",[4,5],{"six":7.7}]|

json_array_length()

json_array_length(X) 函數(shù)返回 JSON 數(shù)組 X 中的元素個(gè)數(shù)，如果 X 是其他 JSON 數(shù)據(jù)而不是數(shù)組時(shí)返回 0。json_array_length(X,P) 函數(shù)返回路徑 P 對應(yīng)數(shù)組中的元素個(gè)數(shù)，如果 X 或者路徑 P 對應(yīng)的是其他 JSON 數(shù)據(jù)而不是數(shù)組時(shí)返回 0，如果路徑 P 沒有對應(yīng)的元素時(shí)返回 NULL。如果 X 不是一個(gè)格式正確的 JSON 字符串，或者 P 不是一個(gè)格式正確的路徑，函數(shù)將會返回錯(cuò)誤。

例如：

SELECT json_array_length('[1,2,3,4]') AS length;length|------|?????4|
SELECT json_array_length('[1,2,3,4]', '$') AS length;length|------| ? ? 4|
SELECT json_array_length('[1,2,3,4]', '$[2]') AS length;length|------|?????0|
SELECT json_array_length('{"one":[1,2,3]}') AS length;length|------|?????0|
SELECT?json_array_length('{"one":[1,2,3]}',?'$.one')?AS?length;
SELECT json_array_length('{"one":[1,2,3]}', '$.two') AS length;length|------| ? ? ?|

json_extract()

json_extract(X,P1,P2,...) 函數(shù)提取并返回 JSON 數(shù)據(jù) X 中的一個(gè)或多個(gè)元素。如果只提供了路徑 P1，對于 JSON null 返回的數(shù)據(jù)類型為 NULL，對于 JSON 數(shù)字返回的數(shù)據(jù)類型為 INTEGER 或者 REAL，對于 JSON false 返回的數(shù)據(jù)為 INTEGER 類型的 0，對于 JSON true 返回的數(shù)據(jù)為 INTEGER 類型的 1，對于 JSON 字符串返回的數(shù)據(jù)類型為去掉引號的文本，對于 JSON 對象和數(shù)組返回的是它們的文本形式。如果指定了多個(gè)路徑參數(shù)（P1、P2 等），函數(shù)將會返回 SQLite 文本形式的 JSON 數(shù)組，包含了每個(gè)路徑對應(yīng)的數(shù)據(jù)。

例如：

SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') AS doc;doc ? ? ? ? ? ? ? ? ? ? ?|-------------------------|{"a":2,"c":[4,5,{"f":7}]}|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') AS doc;doc ? ? ? ? ?|-------------|[4,5,{"f":7}]|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') AS doc;doc ? ?|-------|{"f":7}|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') AS doc;doc|---|??7|
SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') AS doc;doc ? ? ?|---------|[[4,5],2]|
SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') AS doc;doc|---|??5|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') AS doc;doc|---|???|
SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') AS doc;doc ? ? |--------|[null,2]|

json_insert()、json_replace 和 json_set()

json_insert(json,path,value,...)、json_replace(json,path,value,...) 和 json_set(json,path,value,...) 函數(shù)的第一個(gè)參數(shù)是一個(gè) JSON 數(shù)據(jù)，加上零個(gè)或多個(gè)路徑和數(shù)據(jù)的參數(shù)對，使用 path/value 參數(shù)對更新輸入的 JSON 數(shù)據(jù)后返回一個(gè)新的 JSON 字符串。這些函數(shù)的區(qū)別僅僅在于創(chuàng)建新值和覆蓋舊值得方式不同。

這三個(gè)函數(shù)參數(shù)的個(gè)數(shù)總是奇數(shù)，第一個(gè)參數(shù)總是需要修改的原始 JSON。隨后的參數(shù)成對出現(xiàn)，每對參數(shù)中的第一個(gè)是路徑，第二個(gè)是在該路徑上插入、替換或者設(shè)置的數(shù)據(jù)。

數(shù)據(jù)的修改按照從左至右的順序執(zhí)行，前面的數(shù)據(jù)更改會影響后續(xù)的路徑搜索。

如果某個(gè) path/value 參數(shù)對中的數(shù)據(jù)是 TEXT 類型，通常來說將會作為一個(gè)引號引用的 JSON 字符串插入，即使這個(gè)字符串看起來像有效的 JSON。不過，如果該數(shù)據(jù)值另一個(gè) json1 函數(shù)（例如 json()、json_array() 或者 json_object()）的結(jié)果，將被解釋為一個(gè) JSON 插入并且保留所有的子結(jié)構(gòu)。

如果第一個(gè)參數(shù)不是一個(gè)格式正確的 JSON，或者任何 PATH 不是一個(gè)格式正確的路徑，或者任何參數(shù)是 BLOB，函數(shù)將會返回錯(cuò)誤。

如果想要在數(shù)據(jù)的最后追加元素，可以使用 json_insert() 函數(shù)并且指定索引下標(biāo)“#”。例如：

SELECT json_insert('[1,2,3,4]','$[#]',99) AS doc;doc ? ? ? ? |------------|[1,2,3,4,99]|

SELECT json_insert('[1,[2,3],4]','$[1][#]',99) AS doc;doc ? ? ? ? ? |--------------|[1,[2,3,99],4]|

其他示例：

SELECT json_insert('{"a":2,"c":4}', '$.a', 99) AS doc;doc ? ? ? ? ?|-------------|{"a":2,"c":4}|
SELECT json_insert('{"a":2,"c":4}', '$.e', 99) AS doc;doc ? ? ? ? ? ? ? ? |--------------------|{"a":2,"c":4,"e":99}|
SELECT json_replace('{"a":2,"c":4}', '$.a', 99) AS doc;doc ? ? ? ? ? |--------------|{"a":99,"c":4}|
SELECT json_replace('{"a":2,"c":4}', '$.e', 99) AS doc;doc ? ? ? ? ?|-------------|{"a":2,"c":4}|
SELECT json_set('{"a":2,"c":4}', '$.a', 99) AS doc;doc ? ? ? ? ? |--------------|{"a":99,"c":4}|
SELECT json_set('{"a":2,"c":4}', '$.e', 99) AS doc;doc ? ? ? ? ? ? ? ? |--------------------|{"a":2,"c":4,"e":99}|
SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]') AS doc;doc ? ? ? ? ? ? ? ? ?|---------------------|{"a":2,"c":"[97,96]"}|
SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":[97,96]}|
SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":[97,96]}|

json_object()

json_object(label1,value1,...) 函數(shù)接收零個(gè)或多個(gè)參數(shù)對，并且返回一個(gè)由這些參數(shù)組成的 JSON 對象。。每對參數(shù)中的第一個(gè)是元素標(biāo)簽，第二個(gè)是對應(yīng)的數(shù)據(jù)。如果任何參數(shù)是 BLOB，函數(shù)將會返回錯(cuò)誤。

json_object() 函數(shù)目前可以接受重復(fù)的元素標(biāo)簽，將來可能不允許。

如果只傳入一個(gè) TEXT 類型的參數(shù)，即使它是一個(gè)格式正確的 JSON，通常也會被轉(zhuǎn)換為引號引用的 JSON 字符串。不過，如果該參數(shù)直接來自其他 json1 函數(shù)的輸出，將被被當(dāng)作 JSON 處理，所有的類型信息和子結(jié)構(gòu)都會保留。這種處理方式可以實(shí)現(xiàn) json_array() 和 json_object() 函數(shù)的嵌套調(diào)用。json() 函數(shù)也可以將字符串轉(zhuǎn)換為 JSON。

例如：

SELECT json_object('a',2,'c',4) AS doc;doc ? ? ? ? ?|-------------|{"a":2,"c":4}|
SELECT json_object('a',2,'c','{e:5}') AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":"{e:5}"}|
SELECT json_object('a',2,'c',json_object('e',5)) AS doc;doc ? ? ? ? ? ? ? ?|-------------------|{"a":2,"c":{"e":5}}|

json_patch()

json_patch(T,P) 函數(shù)利用 RFC-7396 MergePatch 算法將補(bǔ)丁 P 應(yīng)用到輸入 T，返回修補(bǔ)之后的 T 副本。

MergePatch 可以增加、修改或者刪除 JSON 對象中的元素，因此對于 JSON 對象，json_patch() 函數(shù)一般可以作為 json_set() 和 json_remove() 函數(shù)的替代。不過，MergePatch 將 JSON 數(shù)組當(dāng)作原子對象處理，不能追加或者修改數(shù)組中的單個(gè)元素，只能將整個(gè)數(shù)組作為一個(gè)單元進(jìn)行插入、替換或者刪除。因此，json_patch() 對于處理包含數(shù)組（尤其是數(shù)組中包含很多子結(jié)構(gòu)）的 JSON 用處不大。

例如：

SELECT json_patch('{"a":1,"b":2}','{"c":3,"d":4}') AS doc;doc ? ? ? ? ? ? ? ? ? ? ?|-------------------------|{"a":1,"b":2,"c":3,"d":4}|
SELECT json_patch('{"a":[1,2],"b":2}','{"a":9}') AS doc;doc ? ? ? ? ?|-------------|{"a":9,"b":2}|
SELECT json_patch('{"a":[1,2],"b":2}','{"a":null}') AS doc;doc ? ?|-------|{"b":2}|
SELECT json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') AS doc;doc ? ? ? ? ?|-------------|{"a":9,"c":8}|
SELECT json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') AS doc;doc ? ? ? ? ? ? ? ? ? ? ? ? ? ?|-------------------------------|{"a":{"x":1,"y":9},"b":3,"c":8}|

json_remove()

json_remove(X,P,...) 函數(shù)第一個(gè)參數(shù) X 是一個(gè) JSON 數(shù)據(jù)，加上零個(gè)或多個(gè)路徑參數(shù) P，返回一個(gè)刪除指定元素后的 JSON。如果指定路徑上沒有對應(yīng)的元素，忽略該參數(shù)。

數(shù)據(jù)的刪除按照從左至右的順序執(zhí)行，前面的數(shù)據(jù)更改會影響后續(xù)的路徑搜索。

如果沒有指定路徑參數(shù)，json_remove(X) 函數(shù)將會返回格式化后的 X，刪除了多余的空白字符。

如果第一個(gè)參數(shù)不是一個(gè)格式正確的 JSON，或者任何 PATH 不是一個(gè)格式正確的路徑，或者任何參數(shù)是 BLOB，函數(shù)將會返回錯(cuò)誤。

例如：

SELECT json_remove('[0,1,2,3,4]','$[2]') AS doc;doc ? ? ?|---------|[0,1,3,4]|
SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]') AS doc;doc ? ?|-------|[1,3,4]|
SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]') AS doc;doc ? ?|-------|[1,2,4]|
SELECT json_remove('[0,1,2,3,4]','$[#-1]','$[0]') AS doc;doc ? ?|-------|[1,2,3]|
SELECT json_remove('{"x":25,"y":42}') AS doc;doc ? ? ? ? ? ?|---------------|{"x":25,"y":42}|
SELECT json_remove('{"x":25,"y":42}','$.z') AS doc;doc ? ? ? ? ? ?|---------------|{"x":25,"y":42}|
SELECT json_remove('{"x":25,"y":42}','$.y') AS doc;doc ? ? |--------|{"x":25}|
SELECT json_remove('{"x":25,"y":42}','$') AS doc;doc|---| ? |

json_type()

json_type(X) 函數(shù)返回 X 最外層元素的 JSON 數(shù)據(jù)類型。json_type(X,P) 函數(shù)返回路徑 P 對應(yīng)元素的 JSON 數(shù)據(jù)類型。json_type() 函數(shù)返回的結(jié)果為以下字符串之一：'null'、'true'、'false'、'integer'、'real'、'text'、'array' 或者 'object'。如果 json_type(X,P) 函數(shù)中的路徑 P 對應(yīng)的元素不存在，函數(shù)將會返回 NULL。

如果參數(shù)不是一個(gè)格式正確的 JSON，或者參數(shù)是 BLOB，函數(shù)將會返回錯(cuò)誤。

例如：

SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}') AS type;type ?|------|object|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$') AS type;type ?|------|object|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') AS type;type |-----|array|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') AS type;type ? |-------|integer|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') AS type;type|----|real|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') AS type;type|----|true|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') AS type;type |-----|false|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') AS type;type|----|null|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]') AS type;type|----|text|
SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') AS type;type|----| ? ?|

json_valid()

json_valid(X) 函數(shù)用于驗(yàn)證參數(shù)的格式。如果 X 是一個(gè)格式正確的 JSON，函數(shù)返回 1；否則，函數(shù)返回 0。

例如：

SELECT json_valid('{"x":35}') AS is_json;is_json|-------|??????1|
SELECT json_valid('{"x":35') AS is_json;is_json|-------| ? ? ?0|

json_quote()

json_quote(X) 函數(shù)將 SQL 數(shù)據(jù) X（一個(gè)數(shù)字或者字符串）轉(zhuǎn)換為對應(yīng)的 JSON 形式。例如：

SELECT json_quote(3.14159) AS json;json ? |-------|3.14159|
SELECT json_quote('verdant') AS json;json ? ? |---------|"verdant"|

json_group_array() 和 json_group_object()

json_group_array(X) 函數(shù)是一個(gè)聚合函數(shù)，返回一個(gè)由所有 X 構(gòu)成的 JSON 數(shù)組。例如：

SELECT json_group_array(X)FROM ( ?SELECT json_array(1,2) AS X ?UNION ALL ?SELECT json_array(3,4) ?UNION ALL ?SELECT 5) t;json_group_array(X)|-------------------|[[1,2],[3,4],5] ? ?|

與此類似，json_group_object(NAME,VALUE) 也是一個(gè)聚合函數(shù)，返回一個(gè)由所有 NAME/VALUE 對組成的 JSON 對象。例如：

SELECT json_group_object(name, value)FROM ( ?SELECT 'first' AS name, json_object('a',2,'c',4) AS value ?UNION ALL ?SELECT 'rgb', json_array(255,255,255) ?UNION ALL  ?SELECT 'id', 100) t;json_group_object(name, value) ? ? ? ? ? ? ? ? ? ? ?|----------------------------------------------------|{"first":{"a":2,"c":4},"rgb":[255,255,255],"id":100}|

json_each() 和 json_tree()

json_each(X) 和 json_tree(X) 表值函數(shù)將輸入?yún)?shù) X 中的每個(gè)元素轉(zhuǎn)換為一行數(shù)據(jù)。json_each(X) 函數(shù)只遍歷頂層 JSON 數(shù)組或者對象的直接子節(jié)點(diǎn)，如果頂層元素是一個(gè)基本值則只返回該節(jié)點(diǎn)自身。json_tree(X) 函數(shù)從頂層元素開始遞歸遍歷所有的 JSON 子結(jié)構(gòu)。

json_each(X,P) 和 json_tree(X,P) 函數(shù)和上面兩個(gè)函數(shù)類似，只是它們將路徑 P 對應(yīng)的元素作為頂層元素。

json_each() 和 json_tree() 函數(shù)返回的表結(jié)構(gòu)如下：

CREATE TABLE json_tree( ? ?key ANY, ? ? ? ? ? ? -- key for current element relative to its parent ? ?value ANY, ? ? ? ? ? -- value for the current element ? ?type TEXT, ? ? ? ? ? -- 'object','array','string','integer', etc. ? ?atom ANY, ? ? ? ? ? ?-- value for primitive types, null for array & object ? ?id INTEGER, ? ? ? ? ?-- integer ID for this element ? ?parent INTEGER, ? ? ?-- integer ID for the parent of this element ? ?fullkey TEXT, ? ? ? ?-- full path describing the current element ? ?path TEXT, ? ? ? ? ? -- path to the container of the current row ? ?json JSON HIDDEN, ? ?-- 1st input parameter: the raw JSON ? ?root TEXT HIDDEN ? ? -- 2nd input parameter: the PATH at which to start);

• 字段 key 是 JSON 數(shù)組中每個(gè)元素的下標(biāo)，或者 JSON 對象中每個(gè)元素的標(biāo)簽。其他情況下，key 字段為空。

• 字段 atom 是基本元素（除了 JSON 數(shù)組和對象之外的元素）對應(yīng)的 SQL 值，JSON 數(shù)組和對象的 atom 字段為空。對于基本 JSON 元素而言，字段 value 的值和 atom 字段相同；對于 JSON 數(shù)組和對象元素而言，字段 value 是文本格式的 JSON 數(shù)據(jù)。

• 字段 type 的值是一個(gè) SQL 文本，根據(jù) JSON 元素的類型不同可能的取值為 'null'、'true'、'false'、'integer'、'real'、'text'、'array' 或者 'object'。

• 字段 id 是一個(gè)整數(shù)，標(biāo)識了 JSON 字符串中的每個(gè)的 JSON 元素。id 是一個(gè)內(nèi)部生成的編號，計(jì)算方法在將來的版本中可能會發(fā)生改變。唯一可以確認(rèn)的是每一行都會有一個(gè)不同的編號。

• 字段 parent 對于 json_each() 函數(shù)總是返回 NULL。對于 json_tree() 函數(shù)，字段 parent 是當(dāng)前元素的父節(jié)點(diǎn) id；如果是頂層元素，字段的值為 NULL。

• 字段 fullkey 是一個(gè)文本值，標(biāo)識了當(dāng)前元素在原始 JSON 字符串中的路徑。即使通過參數(shù) root 提供了其他的起點(diǎn)，也會返回從真正的頂層元素開始的完整路徑。

• 字段 path 是到包含當(dāng)前行的數(shù)組或?qū)ο笕萜鞯穆窂?，或者頂層元素是一個(gè)基本類型時(shí)（意味著函數(shù)只返回當(dāng)前行）到當(dāng)前行的路徑。

假設(shè)存在以下 user 表：

CREATE TABLE user(name, phone);INSERT INTO user(name, phone) VALUES ('anne', json_array('010-12345678', '020-10003333'));INSERT INTO user(name, phone) VALUES ('tony', json_array('010-12349999', '800-10007777'));

字段 phone 中使用 JSON 數(shù)組的形式存儲了零個(gè)或多個(gè)電話號碼。以下語句可以找出電話號碼以 020 開頭的用戶：

SELECT DISTINCT user.name ?FROM user, json_each(user.phone) WHERE json_each.value LIKE '010-%';name|----|anne|

現(xiàn)在假設(shè)當(dāng)用戶只有一個(gè)電話號碼時(shí)，字段 phone 中存儲的是普通文本。例如：

INSERT INTO user(name, phone) VALUES ('kevin', '020-10005555');

現(xiàn)在同樣需要找出電話號碼以 020 開頭的用戶。由于 json_each() 函數(shù)要求第一個(gè)參數(shù)是一個(gè)格式正確的 JSON，因此它只能用于包含 2 個(gè)或更多電話號碼的用戶：

SELECT name FROM user WHERE phone LIKE '020-%'UNION ALLSELECT user.name ?FROM user, json_each(user.phone) WHERE json_valid(user.phone) ? AND json_each.value LIKE '020-%';name |-----| anne|kevin|

假設(shè)存在以下 big 表：

CREATE TABLE big(json JSON);INSERT INTO big(json) VALUES (json_object('name', 'anne','phone', json_array('010-12345678', '020-10003333')));INSERT INTO big(json) VALUES (json_object('name', 'tony','phone', json_array('010-12349999', '800-10007777')));

如果想要逐行返回?cái)?shù)據(jù)中的內(nèi)容，可以執(zhí)行以下語句：

SELECT big.rowid, fullkey, value ?FROM big, json_tree(big.json) WHERE json_tree.type NOT IN ('object','array');rowid|fullkey ? |value ? ? ? |-----|----------|------------| ? ?1|$.name ? ?|anne ? ? ? ?| ? ?1|$.phone[0]|010-12345678| ? ?1|$.phone[1]|020-10003333| ? ?2|$.name ? ?|tony ? ? ? ?| ? ?2|$.phone[0]|010-12349999| ? ?2|$.phone[1]|800-10007777|

查詢條件中的 type NOT IN ('object','array') 從結(jié)果中去除了容器節(jié)點(diǎn)，只返回了葉子元素。我們也可以使用以下語句實(shí)現(xiàn)相同的效果：

SELECT big.rowid, fullkey, atom ?FROM big, json_tree(big.json) WHERE atom IS NOT NULL;

假設(shè) big.json 字段中的每一行是一個(gè) JSON 對象，包含一個(gè)唯一標(biāo)識節(jié)點(diǎn)'\$.id' 和一個(gè)嵌套其他對象的 '\$.partlist' 節(jié)點(diǎn)。例如：

INSERT INTO big(json) VALUES (json_object('id', 1,'partlist', json_array('6fa5181e-5721-11e5-a04e-57f3d7b32808', 'a18437b3-b6c4-4473-a9c5-50e7b8eef6be')));INSERT INTO big(json) VALUES (json_object('id', 2,'partlist', json_object('uuid','6fa5181e-5721-11e5-a04e-57f3d7b32808')));INSERT INTO big(json) VALUES (json_object('id', 3,'partlist', json_array(json_object('uuid','e7e3845d-cdfe-48aa-877f-9121b970761d'),json_object('uuid','6fa5181e-5721-11e5-a04e-57f3d7b32808'))));

如果想要找出 '$.partlist' 元素中任意節(jié)點(diǎn)包含一個(gè)或多個(gè) uuid 為 '6fa5181e-5721-11e5-a04e-57f3d7b32808' 的文檔，可以使用以下語句：

SELECT DISTINCT json_extract(big.json,'$.id') ?FROM big, json_tree(big.json, '$.partlist') WHERE json_tree.key='uuid' ? AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';json_extract(big.json,'$.id')|-----------------------------| ? ? ? ? ? ? ? ? ? ? ? ? ? ?2| ? ? ? ? ? ? ? ? ? ? ? ? ? ?3|

編譯 JSON1 插件

SQLite 可加載擴(kuò)展文檔描述了如何將可加載擴(kuò)展編譯為共享庫。文檔中描述的方法也適用于 json1 模塊。

json1 源代碼包含在 SQLite 程序包中，默認(rèn)沒有啟用編譯。可以使用 -DSQLITE_ENABLE_JSON1?編譯時(shí)選項(xiàng)啟用 json1 擴(kuò)展。編譯命令行工具和測試工具時(shí)，標(biāo)準(zhǔn)的 makefile 中包含了該選項(xiàng)，所以命令行工具可以使用 json1。

版本支持

json1 擴(kuò)展使用了 SQLite 3.9.0 引入的 sqlite3_value_subtype() 和sqlite3_result_subtype() 接口，因此更早版本的 SQLite 無法使用 json1 擴(kuò)展。

當(dāng)前的 JSON 庫實(shí)現(xiàn)使用了一個(gè)遞歸下降語法解析器。為了避免使用過多的堆棧空間，任何超過 2000 層嵌套的 JSON 輸入都被視為無效數(shù)據(jù)。嵌套級別的限制符合 RFC-7159 section 9?規(guī)定的 JSON 兼容實(shí)現(xiàn)。

總結(jié)

本文介紹了 SQLite 中的文檔存儲功能。我們可以借助于 json1 擴(kuò)展插件提供的 JSON 函數(shù)實(shí)現(xiàn)文檔數(shù)據(jù)的存儲以及 JSON 文檔和 SQL 數(shù)據(jù)的相互轉(zhuǎn)換，同時(shí)還可以利用 SQLite 表達(dá)式索引和生成列為 JSON 數(shù)據(jù)提供索引支持，從而實(shí)現(xiàn)了將 SQLite 作為一個(gè)文檔數(shù)據(jù)庫使用。