雖然 SQLite 是一個嵌入式數(shù)據(jù)庫，但是它支持 JSON 存儲，并且通過 JSON1 擴展插件提供了許多 JSON 函數(shù)和運算符；同時，SQLite 表達式索引（Indexes On Expressions）和生成列（Generated Column）為 JSON 數(shù)據(jù)提供了索引支持，從而實現(xiàn)了文檔存儲和處理功能。
本文給大家介紹一下如何將 SQLite 作為一個文檔數(shù)據(jù)庫使用。
一個文檔存儲案例
我們首先來看一個簡單的案例：
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)建了一個測試表 docs；其中 content 字段用于存儲 JSON 文檔，字段類型為 TEXT。
然后，我們使用 json() 函數(shù)確保了輸入字符串符合 JSON 格式要求，如果參數(shù)不滿足 JSON 格式要求將會返回錯誤。例如：
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é)點的數(shù)據(jù)，具體的函數(shù)介紹參考下文。
如果想要對以上查詢進行優(yōu)化，可以使用表達式索引。例如：
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é)點進行了索引，查詢時可以通過索引提高檢索的速度。
目前還有一個問題，SQLite 并沒有提供原始的 JSON 數(shù)據(jù)類型，content 字段中仍然可以插入任何數(shù)據(jù)。這個問題我們可以通過生成列來解決。例如：
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 字段定義為一個非空的虛擬生成列，數(shù)據(jù)來自于 content 字段而不會占用額外的存儲。json_extract() 函數(shù)的使用意味著插入無效的 JSON 文檔同樣會返回 Error: malformed JSON 錯誤信息。例如：
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
第一個錯誤是因為文檔不是有效的 JSON 格式，第二個錯誤是因為文檔中沒有 id 節(jié)點。
由于 SQLite 生成列無法作為主鍵字段，我們不能將 id 字段定義為該表的主鍵。不過，我們可以為 id 字段創(chuàng)建一個唯一索引，加上非空約束后的效果和主鍵一樣。
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
接下來我們詳細介紹一下 JSON1 插件。
JSON1 插件概述
json1 插件是一個可加載的擴展，實現(xiàn)了 15 個應(yīng)用程序定義的 SQL 函數(shù)和 2 個表值函數(shù)，可以用于管理 SQLite 中的 JSON 文檔。其中，以下 13 個函數(shù)是標量函數(shù)：
json(json)，驗證輸入?yún)?shù)是否符合 JSON 格式并返回結(jié)果。
json_array(value1,value2,...)，創(chuàng)建一個 JSON 數(shù)組。
json_array_length(json)，返回 JSON 數(shù)組中的元素個數(shù)。
json_array_length(json,path)，返回指定路徑上的 JSON 數(shù)組中的元素個數(shù)。
json_extract(json,path,...)，提取指定路徑上的元素。
json_insert(json,path,value,...)，在指定路徑上插入元素。
json_object(label1,value1,...)，創(chuàng)建一個 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)?shù)是否符合 JSON 格式。
json_quote(value)，將 SQL 數(shù)據(jù)轉(zhuǎn)換為 JSON 格式。
以下 2 個是表值函數(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 數(shù)據(jù)。向下兼容意味著 SQLite 只能存儲 NULL、整數(shù)、浮點數(shù)、文本以及 BLOB，無法增加第 6 個類型“JSON”。
json1 插件目前不支持 JSON 文檔的二進制編碼（BSON）。經(jīng)過試驗沒有找到比純文本編碼格式明顯更小或者更快的二進制編碼，目前的實現(xiàn)可以支持 1GB/s 的 JSON 文本解析。所有的 json1 函數(shù)參數(shù)都不接受 BLOB，如果指定這種參數(shù)將會報錯，因為 BLOB 是為了將來增強而保留的二進制 JSON 存儲類型。
json1 擴展名中的數(shù)字“1”是故意設(shè)計的，設(shè)計人員預(yù)計將來會基于 json1 的經(jīng)驗創(chuàng)建新的不兼容的 JSON 擴展。一旦獲得足夠的經(jīng)驗，某種JSON 擴展可能會被添加到 SQLite 核心代碼中。目前，對 JSON 的支持仍然是通過擴展的形式實現(xiàn)。
通用參數(shù)說明
對于第一個參數(shù)是 JSON 的函數(shù)，該參數(shù)可以是一個 JSON 對象、數(shù)組、數(shù)字、字符串或者 null。SQLite 數(shù)字和 NULL 值分別被當作 JSON 數(shù)字和 null，SQLite 文本可以被當作? JSON 對象、數(shù)組或者字符串。如果 SQLite 本文不符合 JSON 對象、數(shù)組或者字符串格式，函數(shù)將會返回錯誤， json_valid() 和 json_quote() 函數(shù)除外。
為了驗證格式的有效性，JSON 輸入?yún)?shù)中開頭和結(jié)尾的空白字符將會被忽略。根據(jù) JSON 規(guī)范，內(nèi)部的空白字符也會被忽略。這些函數(shù)完全遵循 RFC-7159 JSON 語法。
對于接受 PATH 參數(shù)的函數(shù)，PATH 必須滿足一定的格式，否則函數(shù)將會返回錯誤。滿足格式的 PATH 必須是一個以“\$”符號開頭，加上零個或多個“.objectlabel”或者“[arrayindex]”組成的文本。
其中，arrayindex 通常是一個非負的整數(shù) N，表示選擇數(shù)組的第 N 個元素，從 0 開始計數(shù)。arrayindex 也可以使用“#-N”的形式，表示選擇從右邊開始的第 N 個元素。數(shù)組最后一個元素是“#-1”，字符“#”相當于數(shù)據(jù)元素的個數(shù)。
對于接受 value 參數(shù)（value1，value2 等）的函數(shù)，這些參數(shù)通常被當作引號引用的字符串常量，并且最終解析為 JSON 字符串數(shù)據(jù)。不過，如果某個 value 參數(shù)直接來自另一個 json1 函數(shù)的輸出結(jié)果，那么該參數(shù)將被當作實際的 JSON，傳入的將會是完整的 JSON 而不是字符串常量。
例如，在下面的 json_object() 函數(shù)調(diào)用中，value 參數(shù)看起來像是一個滿足格式的 JSON 數(shù)組。但是，由于它是一個普通的 SQL 文本，因此被解析為一個字符串常量，并且作為一個字符串被添加到結(jié)果中：
SELECT json_object('ex','[52,3.14159]');json_object('ex','[52,3.14159]')|--------------------------------|{"ex":"[52,3.14159]"} ? ? ? ? ? |
但是，如果一個外部 json_object() 調(diào)用中的 value 參數(shù)來自另一個函數(shù)的結(jié)果，例如 json() 或者 json_array()，將會被解析為實際的 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ù)只有當其直接來自另一個 json1 函數(shù)時才被解釋為 JSON。
JSON 函數(shù)說明
接下來我們詳細介紹 json1 擴展中的各種函數(shù)。
json()
json(X) 函數(shù)可以驗證參數(shù) X 符合 JSON 字符串的格式，并且返回一個精簡版的 JSON 字符串（刪除了所有不必要的空白字符）。如果 X 不是一個格式正確的 JSON 字符串，函數(shù)將會返回錯誤。
如果參數(shù) X 是一個包含重復(fù)標簽的 JSON 對象，不確定是否保留重復(fù)元素。當前實現(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ù)接收零個或多個參數(shù)，并且返回一個由這些參數(shù)組成的 JSON 數(shù)組。如果任何參數(shù)是 BLOB，函數(shù)將會返回錯誤。
TEXT 類型的參數(shù)通常會轉(zhuǎn)換為引號包含的 JSON 字符串。但是，如果該參數(shù)來自其他 json1 函數(shù)的輸出，將會作為 JSON 傳入。這種處理方式可以實現(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 中的元素個數(shù)，如果 X 是其他 JSON 數(shù)據(jù)而不是數(shù)組時返回 0。json_array_length(X,P) 函數(shù)返回路徑 P 對應(yīng)數(shù)組中的元素個數(shù)，如果 X 或者路徑 P 對應(yīng)的是其他 JSON 數(shù)據(jù)而不是數(shù)組時返回 0，如果路徑 P 沒有對應(yīng)的元素時返回 NULL。如果 X 不是一個格式正確的 JSON 字符串，或者 P 不是一個格式正確的路徑，函數(shù)將會返回錯誤。
例如：
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 中的一個或多個元素。如果只提供了路徑 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ù)組返回的是它們的文本形式。如果指定了多個路徑參數(shù)（P1、P2 等），函數(shù)將會返回 SQLite 文本形式的 JSON 數(shù)組，包含了每個路徑對應(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ù)的第一個參數(shù)是一個 JSON 數(shù)據(jù)，加上零個或多個路徑和數(shù)據(jù)的參數(shù)對，使用 path/value 參數(shù)對更新輸入的 JSON 數(shù)據(jù)后返回一個新的 JSON 字符串。這些函數(shù)的區(qū)別僅僅在于創(chuàng)建新值和覆蓋舊值得方式不同。
| 函數(shù) | 是否覆蓋已有元素 | 是否創(chuàng)建不存在的元素 |
|---|
| json_insert() | ? | ?? |
| json_replace() | ?? | ? |
| json_set() | ?? | ?? |
這三個函數(shù)參數(shù)的個數(shù)總是奇數(shù)，第一個參數(shù)總是需要修改的原始 JSON。隨后的參數(shù)成對出現(xiàn)，每對參數(shù)中的第一個是路徑，第二個是在該路徑上插入、替換或者設(shè)置的數(shù)據(jù)。
數(shù)據(jù)的修改按照從左至右的順序執(zhí)行，前面的數(shù)據(jù)更改會影響后續(xù)的路徑搜索。
如果某個 path/value 參數(shù)對中的數(shù)據(jù)是 TEXT 類型，通常來說將會作為一個引號引用的 JSON 字符串插入，即使這個字符串看起來像有效的 JSON。不過，如果該數(shù)據(jù)值另一個 json1 函數(shù)（例如 json()、json_array() 或者 json_object()）的結(jié)果，將被解釋為一個 JSON 插入并且保留所有的子結(jié)構(gòu)。
如果第一個參數(shù)不是一個格式正確的 JSON，或者任何 PATH 不是一個格式正確的路徑，或者任何參數(shù)是 BLOB，函數(shù)將會返回錯誤。
如果想要在數(shù)據(jù)的最后追加元素，可以使用 json_insert() 函數(shù)并且指定索引下標“#”。例如：
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ù)接收零個或多個參數(shù)對，并且返回一個由這些參數(shù)組成的 JSON 對象。。每對參數(shù)中的第一個是元素標簽，第二個是對應(yīng)的數(shù)據(jù)。如果任何參數(shù)是 BLOB，函數(shù)將會返回錯誤。
json_object() 函數(shù)目前可以接受重復(fù)的元素標簽，將來可能不允許。
如果只傳入一個 TEXT 類型的參數(shù)，即使它是一個格式正確的 JSON，通常也會被轉(zhuǎn)換為引號引用的 JSON 字符串。不過，如果該參數(shù)直接來自其他 json1 函數(shù)的輸出，將被被當作 JSON 處理，所有的類型信息和子結(jié)構(gòu)都會保留。這種處理方式可以實現(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 算法將補丁 P 應(yīng)用到輸入 T，返回修補之后的 T 副本。
MergePatch 可以增加、修改或者刪除 JSON 對象中的元素，因此對于 JSON 對象，json_patch() 函數(shù)一般可以作為 json_set() 和 json_remove() 函數(shù)的替代。不過，MergePatch 將 JSON 數(shù)組當作原子對象處理，不能追加或者修改數(shù)組中的單個元素，只能將整個數(shù)組作為一個單元進行插入、替換或者刪除。因此，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ù)第一個參數(shù) X 是一個 JSON 數(shù)據(jù)，加上零個或多個路徑參數(shù) P，返回一個刪除指定元素后的 JSON。如果指定路徑上沒有對應(yīng)的元素，忽略該參數(shù)。
數(shù)據(jù)的刪除按照從左至右的順序執(zhí)行，前面的數(shù)據(jù)更改會影響后續(xù)的路徑搜索。
如果沒有指定路徑參數(shù)，json_remove(X) 函數(shù)將會返回格式化后的 X，刪除了多余的空白字符。
如果第一個參數(shù)不是一個格式正確的 JSON，或者任何 PATH 不是一個格式正確的路徑，或者任何參數(shù)是 BLOB，函數(shù)將會返回錯誤。
例如：
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ù)不是一個格式正確的 JSON，或者參數(shù)是 BLOB，函數(shù)將會返回錯誤。
例如：
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ù)用于驗證參數(shù)的格式。如果 X 是一個格式正確的 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（一個數(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ù)是一個聚合函數(shù)，返回一個由所有 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) 也是一個聚合函數(shù)，返回一個由所有 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 中的每個元素轉(zhuǎn)換為一行數(shù)據(jù)。json_each(X) 函數(shù)只遍歷頂層 JSON 數(shù)組或者對象的直接子節(jié)點，如果頂層元素是一個基本值則只返回該節(jié)點自身。json_tree(X) 函數(shù)從頂層元素開始遞歸遍歷所有的 JSON 子結(jié)構(gòu)。
json_each(X,P) 和 json_tree(X,P) 函數(shù)和上面兩個函數(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ù)組中每個元素的下標，或者 JSON 對象中每個元素的標簽。其他情況下，key 字段為空。
字段 atom 是基本元素（除了 JSON 數(shù)組和對象之外的元素）對應(yīng)的 SQL 值，JSON 數(shù)組和對象的 atom 字段為空。對于基本 JSON 元素而言，字段 value 的值和 atom 字段相同；對于 JSON 數(shù)組和對象元素而言，字段 value 是文本格式的 JSON 數(shù)據(jù)。
字段 type 的值是一個 SQL 文本，根據(jù) JSON 元素的類型不同可能的取值為 'null'、'true'、'false'、'integer'、'real'、'text'、'array' 或者 'object'。
字段 id 是一個整數(shù)，標識了 JSON 字符串中的每個的 JSON 元素。id 是一個內(nèi)部生成的編號，計算方法在將來的版本中可能會發(fā)生改變。唯一可以確認的是每一行都會有一個不同的編號。
字段 parent 對于 json_each() 函數(shù)總是返回 NULL。對于 json_tree() 函數(shù)，字段 parent 是當前元素的父節(jié)點 id；如果是頂層元素，字段的值為 NULL。
字段 fullkey 是一個文本值，標識了當前元素在原始 JSON 字符串中的路徑。即使通過參數(shù) root 提供了其他的起點，也會返回從真正的頂層元素開始的完整路徑。
字段 path 是到包含當前行的數(shù)組或?qū)ο笕萜鞯穆窂?，或者頂層元素是一個基本類型時（意味著函數(shù)只返回當前行）到當前行的路徑。
假設(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ù)組的形式存儲了零個或多個電話號碼。以下語句可以找出電話號碼以 020 開頭的用戶：
SELECT DISTINCT user.name ?FROM user, json_each(user.phone) WHERE json_each.value LIKE '010-%';name|----|anne|
現(xiàn)在假設(shè)當用戶只有一個電話號碼時，字段 phone 中存儲的是普通文本。例如：
INSERT INTO user(name, phone) VALUES ('kevin', '020-10005555');
現(xiàn)在同樣需要找出電話號碼以 020 開頭的用戶。由于 json_each() 函數(shù)要求第一個參數(shù)是一個格式正確的 JSON，因此它只能用于包含 2 個或更多電話號碼的用戶：
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')));
如果想要逐行返回數(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é)點，只返回了葉子元素。我們也可以使用以下語句實現(xiàn)相同的效果：
SELECT big.rowid, fullkey, atom ?FROM big, json_tree(big.json) WHERE atom IS NOT NULL;
假設(shè) big.json 字段中的每一行是一個 JSON 對象，包含一個唯一標識節(jié)點'\$.id' 和一個嵌套其他對象的 '\$.partlist' 節(jié)點。例如：
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é)點包含一個或多個 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 可加載擴展文檔描述了如何將可加載擴展編譯為共享庫。文檔中描述的方法也適用于 json1 模塊。
json1 源代碼包含在 SQLite 程序包中，默認沒有啟用編譯。可以使用 -DSQLITE_ENABLE_JSON1?編譯時選項啟用 json1 擴展。編譯命令行工具和測試工具時，標準的 makefile 中包含了該選項，所以命令行工具可以使用 json1。
版本支持
json1 擴展使用了 SQLite 3.9.0 引入的 sqlite3_value_subtype() 和sqlite3_result_subtype() 接口，因此更早版本的 SQLite 無法使用 json1 擴展。
當前的 JSON 庫實現(xiàn)使用了一個遞歸下降語法解析器。為了避免使用過多的堆棧空間，任何超過 2000 層嵌套的 JSON 輸入都被視為無效數(shù)據(jù)。嵌套級別的限制符合 RFC-7159 section 9?規(guī)定的 JSON 兼容實現(xiàn)。
總結(jié)
本文介紹了 SQLite 中的文檔存儲功能。我們可以借助于 json1 擴展插件提供的 JSON 函數(shù)實現(xiàn)文檔數(shù)據(jù)的存儲以及 JSON 文檔和 SQL 數(shù)據(jù)的相互轉(zhuǎn)換，同時還可以利用 SQLite 表達式索引和生成列為 JSON 數(shù)據(jù)提供索引支持，從而實現(xiàn)了將 SQLite 作為一個文檔數(shù)據(jù)庫使用。
該文章在 2024/10/30 14:33:17 編輯過
400 186 1886
