狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

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

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

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

我們首先來(lái)看一個(gè)簡(jiǎn)單的案例：

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è)測(cè)試表 docs；其中 content 字段用于存儲(chǔ) JSON 文檔，字段類(lèi)型為 TEXT。

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

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

接下來(lái)我們可以在查詢(xún)中使用 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ù)介紹參考下文。

如果想要對(duì)以上查詢(xún)進(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>=?)

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

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

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ù)來(lái)自于 content 字段而不會(huì)占用額外的存儲(chǔ)。json_extract() 函數(shù)的使用意味著插入無(wú)效的 JSON 文檔同樣會(huì)返回 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中沒(méi)有 id 節(jié)點(diǎn)。

由于 SQLite 生成列無(wú)法作為主鍵字段，我們不能將 id 字段定義為該表的主鍵。不過(guò)，我們可以為 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

接下來(lái)我們?cè)敿?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 對(duì)象。

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

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

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

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

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

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

• 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 對(duì)象。

以下 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)過(guò)試驗(yàn)沒(méi)有找到比純文本編碼格式明顯更小或者更快的二進(jìn)制編碼，目前的實(shí)現(xiàn)可以支持 1GB/s 的 JSON 文本解析。所有的 json1 函數(shù)參數(shù)都不接受 BLOB，如果指定這種參數(shù)將會(huì)報(bào)錯(cuò)，因?yàn)?BLOB 是為了將來(lái)增強(qiáng)而保留的二進(jìn)制 JSON 存儲(chǔ)類(lèi)型。

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

通用參數(shù)說(shuō)明

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

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

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

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

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

例如，在下面的 json_object() 函數(shù)調(diào)用中，value 參數(shù)看起來(lái)像是一個(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ù)來(lái)自另一個(gè)函數(shù)的結(jié)果，例如 json() 或者 json_array()，將會(huì)被解析為實(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，無(wú)論該參數(shù)的值來(lái)自何處。但是 value 參數(shù)只有當(dāng)其直接來(lái)自另一個(gè) json1 函數(shù)時(shí)才被解釋為 JSON。

JSON 函數(shù)說(shuō)明

接下來(lái)我們?cè)敿?xì)介紹 json1 擴(kuò)展中的各種函數(shù)。

json()

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

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

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ù)將會(huì)返回錯(cuò)誤。

TEXT 類(lèi)型的參數(shù)通常會(huì)轉(zhuǎn)換為引號(hào)包含的 JSON 字符串。但是，如果該參數(shù)來(lái)自其他 json1 函數(shù)的輸出，將會(huì)作為 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 對(duì)應(yīng)數(shù)組中的元素個(gè)數(shù)，如果 X 或者路徑 P 對(duì)應(yīng)的是其他 JSON 數(shù)據(jù)而不是數(shù)組時(shí)返回 0，如果路徑 P 沒(méi)有對(duì)應(yīng)的元素時(shí)返回 NULL。如果 X 不是一個(gè)格式正確的 JSON 字符串，或者 P 不是一個(gè)格式正確的路徑，函數(shù)將會(huì)返回錯(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，對(duì)于 JSON null 返回的數(shù)據(jù)類(lèi)型為 NULL，對(duì)于 JSON 數(shù)字返回的數(shù)據(jù)類(lèi)型為 INTEGER 或者 REAL，對(duì)于 JSON false 返回的數(shù)據(jù)為 INTEGER 類(lèi)型的 0，對(duì)于 JSON true 返回的數(shù)據(jù)為 INTEGER 類(lèi)型的 1，對(duì)于 JSON 字符串返回的數(shù)據(jù)類(lèi)型為去掉引號(hào)的文本，對(duì)于 JSON 對(duì)象和數(shù)組返回的是它們的文本形式。如果指定了多個(gè)路徑參數(shù)（P1、P2 等），函數(shù)將會(huì)返回 SQLite 文本形式的 JSON 數(shù)組，包含了每個(gè)路徑對(duì)應(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ù)對(duì)，使用 path/value 參數(shù)對(duì)更新輸入的 JSON 數(shù)據(jù)后返回一個(gè)新的 JSON 字符串。這些函數(shù)的區(qū)別僅僅在于創(chuàng)建新值和覆蓋舊值得方式不同。

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

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

如果某個(gè) path/value 參數(shù)對(duì)中的數(shù)據(jù)是 TEXT 類(lèi)型，通常來(lái)說(shuō)將會(huì)作為一個(gè)引號(hào)引用的 JSON 字符串插入，即使這個(gè)字符串看起來(lái)像有效的 JSON。不過(guò)，如果該數(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ù)將會(huì)返回錯(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ù)對(duì)，并且返回一個(gè)由這些參數(shù)組成的 JSON 對(duì)象。。每對(duì)參數(shù)中的第一個(gè)是元素標(biāo)簽，第二個(gè)是對(duì)應(yīng)的數(shù)據(jù)。如果任何參數(shù)是 BLOB，函數(shù)將會(huì)返回錯(cuò)誤。

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

如果只傳入一個(gè) TEXT 類(lèi)型的參數(shù)，即使它是一個(gè)格式正確的 JSON，通常也會(huì)被轉(zhuǎn)換為引號(hào)引用的 JSON 字符串。不過(guò)，如果該參數(shù)直接來(lái)自其他 json1 函數(shù)的輸出，將被被當(dāng)作 JSON 處理，所有的類(lèi)型信息和子結(jié)構(gòu)都會(huì)保留。這種處理方式可以實(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 對(duì)象中的元素，因此對(duì)于 JSON 對(duì)象，json_patch() 函數(shù)一般可以作為 json_set() 和 json_remove() 函數(shù)的替代。不過(guò)，MergePatch 將 JSON 數(shù)組當(dāng)作原子對(duì)象處理，不能追加或者修改數(shù)組中的單個(gè)元素，只能將整個(gè)數(shù)組作為一個(gè)單元進(jìn)行插入、替換或者刪除。因此，json_patch() 對(duì)于處理包含數(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。如果指定路徑上沒(méi)有對(duì)應(yīng)的元素，忽略該參數(shù)。

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

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

如果第一個(gè)參數(shù)不是一個(gè)格式正確的 JSON，或者任何 PATH 不是一個(gè)格式正確的路徑，或者任何參數(shù)是 BLOB，函數(shù)將會(huì)返回錯(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ù)類(lèi)型。json_type(X,P) 函數(shù)返回路徑 P 對(duì)應(yīng)元素的 JSON 數(shù)據(jù)類(lèi)型。json_type() 函數(shù)返回的結(jié)果為以下字符串之一：'null'、'true'、'false'、'integer'、'real'、'text'、'array' 或者 'object'。如果 json_type(X,P) 函數(shù)中的路徑 P 對(duì)應(yīng)的元素不存在，函數(shù)將會(huì)返回 NULL。

如果參數(shù)不是一個(gè)格式正確的 JSON，或者參數(shù)是 BLOB，函數(shù)將會(huì)返回錯(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)換為對(duì)應(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] ? ?|

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

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ù)組或者對(duì)象的直接子節(jié)點(diǎn)，如果頂層元素是一個(gè)基本值則只返回該節(jié)點(diǎn)自身。json_tree(X) 函數(shù)從頂層元素開(kāi)始遞歸遍歷所有的 JSON 子結(jié)構(gòu)。

json_each(X,P) 和 json_tree(X,P) 函數(shù)和上面兩個(gè)函數(shù)類(lèi)似，只是它們將路徑 P 對(duì)應(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 對(duì)象中每個(gè)元素的標(biāo)簽。其他情況下，key 字段為空。

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

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

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

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

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

• 字段 path 是到包含當(dāng)前行的數(shù)組或?qū)ο笕萜鞯穆窂?，或者頂層元素是一個(gè)基本類(lèi)型時(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ù)組的形式存儲(chǔ)了零個(gè)或多個(gè)電話號(hào)碼。以下語(yǔ)句可以找出電話號(hào)碼以 020 開(kāi)頭的用戶(hù)：

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

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

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

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

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í)行以下語(yǔ)句：

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|

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

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

假設(shè) big.json 字段中的每一行是一個(gè) JSON 對(duì)象，包含一個(gè)唯一標(biāo)識(shí)節(jié)點(diǎn)'\$.id' 和一個(gè)嵌套其他對(duì)象的 '\$.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' 的文檔，可以使用以下語(yǔ)句：

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ò)展編譯為共享庫(kù)。文檔中描述的方法也適用于 json1 模塊。

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

版本支持

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

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

總結(jié)

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