JSON 函數和運算子預設內建於 SQLite，自 SQLite 版本 3.38.0 (2022-02-22) 起。如果加入 -DSQLITE_OMIT_JSON 編譯時間選項，則可以省略它們。在版本 3.38.0 之前，JSON 函數是一個延伸，只有在包含 -DSQLITE_ENABLE_JSON1 編譯時間選項時才會包含在建置中。換句話說，JSON 函數從 SQLite 版本 3.37.2 及更早版本中選擇加入，到 SQLite 版本 3.38.0 及更高版本中選擇退出。

3. 介面概觀

SQLite 將 JSON 儲存為一般文字。向後相容性限制表示 SQLite 只能儲存 NULL、整數、浮點數、文字和 BLOB 等值。無法新增新的「JSON」類型。

3.1. JSON 引數

對於將 JSON 作為第一個引數的函數，該引數可以是 JSON 物件、陣列、數字、字串或 null。SQLite 數字值和 NULL 值分別會被解釋為 JSON 數字和 null。SQLite 文字值可以理解為 JSON 物件、陣列或字串。如果將不是格式良好的 JSON 物件、陣列或字串的 SQLite 文字值傳遞到 JSON 函數，該函數通常會擲回錯誤。（此規則的例外是 json_valid()、json_quote() 和 json_error_position()。）

這些常式理解所有 rfc-8259 JSON 語法和 JSON5 延伸。這些常式產生的 JSON 文字總是嚴格符合標準 JSON 定義，且不包含任何 JSON5 或其他延伸。在版本 3.42.0 (2023-05-16) 中新增了讀取和理解 JSON5 的能力。先前版本的 SQLite 只會讀取標準 JSON。

3.2. JSONB

從版本 3.45.0 (2024-01-15) 開始，SQLite 允許其 JSON 的內部「解析樹」表示形式以 BLOB 的形式儲存在磁碟上，採用我們稱為「JSONB」的格式。透過直接將 SQLite 的 JSON 內部二進制表示形式儲存在資料庫中，應用程式可以在讀取和更新 JSON 值時，繞過解析和呈現 JSON 的開銷。內部 JSONB 格式所使用的磁碟空間也比文字 JSON 略少。

任何接受文字 JSON 作為輸入的 SQL 函數參數，也會接受 JSONB 格式的 BLOB。函數在兩種情況下都會執行相同操作，只不過當輸入為 JSONB 時，由於不需要執行 JSON 解析器，因此執行速度會更快。

大多數回傳 JSON 文字的 SQL 函數，都有對應的函數回傳等效的 JSONB。以文字格式回傳 JSON 的函數開頭為「json_」，而回傳二進制 JSONB 格式的函數開頭為「jsonb_」。

3.2.1. JSONB 格式

JSONB 是由 SQLite 使用的 JSON 二進制表示形式，僅供 SQLite 內部使用。應用程式不應在 SQLite 外部使用 JSONB，也不應嘗試逆向工程 JSONB 格式。

「JSONB」這個名稱的靈感來自 PostgreSQL，但 SQLite JSONB 的磁碟格式與 PostgreSQL 的不同。這兩種格式名稱相同，但二進制不相容。PostgreSQL JSONB 格式聲稱可以提供物件和陣列中元素的 O(1) 查詢。SQLite 的 JSONB 格式並未做出這樣的宣稱。SQLite 的 JSONB 在 SQLite 中大多數操作的時間複雜度為 O(N)，就像文字 JSON 一樣。JSONB 在 SQLite 中的優點是它比文字 JSON 更小、更快，潛在速度快上好幾倍。磁碟上的 JSONB 格式有空間可以加入增強功能，未來版本的 SQLite 可能包含提供 JSONB 中元素的 O(1) 查詢選項，但目前尚無此功能。

3.2.2. 處理格式錯誤的 JSONB

SQLite 所產生的 JSONB 永遠都是格式正確的。如果你遵循建議的做法，將 JSONB 視為不透明的 BLOB，那麼你不會有任何問題。但是 JSONB 僅僅是一個 BLOB，因此惡意的程式設計師可以設計出類似於 JSONB 但技術上格式錯誤的 BLOB。當格式錯誤的 JSONB 被輸入到 JSON 函數中時，可能會發生以下任一情況

SQL 陳述可能會中止，並顯示「格式錯誤的 JSON」錯誤。
如果 JSONB blob 的格式錯誤部分不會影響答案，則可能會傳回正確的答案。
可能會傳回奇怪或沒有意義的答案。

SQLite 處理無效 JSONB 的方式可能會隨著 SQLite 版本的不同而改變。系統遵循垃圾進垃圾出的規則：如果你輸入無效的 JSONB 給 JSON 函數，你會得到無效的答案。如果你懷疑我們的 JSONB 的有效性，請使用 json_valid() 函數來驗證它。

我們做出這個承諾：格式錯誤的 JSONB 永遠不會導致記憶體錯誤或類似問題，進而導致漏洞。無效的 JSONB 可能會導致奇怪的答案，或者可能會導致查詢中止，但不會導致崩潰。

3.3. PATH 參數

對於接受 PATH 參數的函數，該 PATH 必須格式正確，否則函數會擲出錯誤。格式正確的 PATH 是以一個「$」字元開頭的文字值，後面接著零個或多個「.objectlabel」或「[arrayindex]」實例。

arrayindex 通常是非負整數 N。在這種情況下，所選取的陣列元素是陣列的第 N 個元素，從左邊的 0 開始。arrayindex 也可以是「#-N」形式，這種情況下，所選取的元素是從右邊數起的第 N 個元素。陣列的最後一個元素是「#-1」。將「#」字元視為「陣列中的元素數量」。然後，運算式「#-1」會評估為對應於陣列中最後一個條目的整數。有時陣列索引僅為 # 字元會很有用，例如將值附加到現有的 JSON 陣列時

json_set('[0,1,2]','$[#]','new') → '[0,1,2,"new"]'

3.4. VALUE 參數

對於接受「值」參數的函數（也顯示為「值1」和「值2」），這些參數通常被理解為引號括起來的字串，並在結果中成為 JSON 字串值。即使輸入的值字串看起來像格式良好的 JSON，它們在結果中仍會被解釋為字串。

但是，如果值參數直接來自另一個 JSON 函數的結果或來自 -> 運算子（但不是 ->> 運算子），則該參數被理解為實際的 JSON，並且會插入完整的 JSON，而不是引號括起來的字串。

例如，在以下對 json_object() 的呼叫中，值參數看起來像格式良好的 JSON 陣列。但是，由於它只是普通的 SQL 文字，因此它被解釋為字串，並作為引號括起來的字串新增到結果中

json_object('ex','[52,3.14159]') → '{"ex":"[52,3.14159]"}'
json_object('ex',('[52,3.14159]'->>'$')) → '{"ex":"[52,3.14159]"}'

但是，如果外部 json_object() 呼叫中的值參數是另一個 JSON 函數（例如 json() 或 json_array()）的結果，則該值被理解為實際的 JSON，並以這種方式插入

json_object('ex',json('[52,3.14159]')) → '{"ex":[52,3.14159]}'
json_object('ex',json_array(52,3.14159)) → '{"ex":[52,3.14159]}'
json_object('ex','[52,3.14159]'->'$') → '{"ex":[52,3.14159]}'

說清楚一點：「json」參數始終被解釋為 JSON，無論該參數的值來自何處。但「值」參數僅在這些參數直接來自另一個 JSON 函數或 -> 運算子時才被解釋為 JSON。

在被解釋為 JSON 字串的 JSON 值引數中，Unicode 逸出序列不會被視為等同於表示表達的 Unicode 碼點的字元或逸出的控制字元。此類逸出序列不會被轉換或特別處理；它們會被 SQLite 的 JSON 函式視為純文字。

3.5. 相容性

此 JSON 函式庫的目前實作使用遞迴下降剖析器。為了避免使用過多的堆疊空間，任何超過 1000 層巢狀的 JSON 輸入都會被視為無效。JSON 的相容實作允許巢狀深度限制，請見 RFC-8259 第 9 節。

3.6. JSON5 擴充

從版本 3.42.0 (2023-05-16) 開始，這些例程會讀取並解釋包含 JSON5 擴充的 JSON 文字輸入。然而，由這些例程產生的 JSON 文字將永遠嚴格符合 JSON 的規範定義。

以下是 JSON5 擴充的概要（改編自 JSON5 規範）

物件金鑰可以是不帶引號的識別碼。
物件可以有一個單一尾隨逗號。
陣列可以有一個單一尾隨逗號。
字串可以用單引號。
字串可以透過逸出換行字元來跨越多行。
字串可以包含新的字元逸出。
數字可以是十六進位的。
數字可以有前導或尾隨小數點。
數字可以是「Infinity」、「-Infinity」和「NaN」。
數字可以以明確的正號開頭。
允許單行 (//...) 和多行 (/*...*/) 註解。
允許額外的空白字元。

若要將字串 X 從 JSON5 轉換為規範的 JSON，請呼叫「json(X)」。「json()」函式的輸出將是規範的 JSON，無論輸入中是否存在任何 JSON5 擴充。為了向後相容，json_valid(X) 函式在沒有「flags」引數的情況下，會繼續對非規範 JSON 的輸入回報 false，即使輸入是函式能夠理解的 JSON5。若要判斷輸入字串是否為有效的 JSON5，請在 json_valid 的「flags」引數中包含 0x02 位元：「json_valid(X,2)」。

這些常式理解所有 JSON5，以及更多。SQLite 以這兩種方式擴充 JSON5 語法

嚴格的 JSON5 要求未引號的物件金鑰必須是 ECMAScript 5.1 IdentifierNames。但需要大型 unicode 表格和大量程式碼才能判斷金鑰是否為 ECMAScript 5.1 IdentifierName。因此，SQLite 允許物件金鑰包含任何大於 U+007f 且不是空白字元的 unicode 字元。這個放寬的「識別碼」定義大幅簡化實作，讓 JSON 解析器更小且執行速度更快。
JSON5 允許浮點無限大表示為「Infinity」、「-Infinity」或「+Infinity」，大小寫完全相同 - 開頭的「I」為大寫，其他所有字元為小寫。SQLite 也允許縮寫「Inf」取代「Infinity」，並允許這兩個關鍵字出現在大小寫字母的任何組合中。類似地，JSON5 允許「NaN」表示非數字。SQLite 將其擴充為也允許「QNaN」和「SNaN」，大小寫字母的任何組合皆可。請注意，SQLite 將 NaN、QNaN 和 SNaN 解釋為「null」的替代拼法。已新增此擴充，因為（據我們所知）有許多 JSON 實際上包含這些非標準的無限大與非數字表示法。

3.7. 效能考量

大多數 JSON 函式使用 JSONB 進行內部處理。因此，如果輸入為文字，它們必須先將輸入文字轉換為 JSONB。如果輸入已經是 JSONB 格式，則不需要轉換，可以略過這個步驟，效能也更快。

因此，當一個 JSON 函式的引數是由另一個 JSON 函式提供時，通常使用「jsonb_」變體作為引數函式會更有效率。

... json_insert(A,'$.b',json(C)) ... ← 效率較低。
... json_insert(A,'$.b',jsonb(C)) ... ← 效率較高。

聚合 JSON SQL 函數是此規則的例外。這些函數都使用文字而非 JSONB 來進行處理。因此，對於聚合 JSON SQL 函數，使用「json_」函數而非「jsonb_」函數提供參數會更有效率。

... json_group_array(json(A))) ... ← 效率較高。
... json_group_array(jsonb(A))) ... ← 效率較低。

3.8. JSON BLOB 輸入錯誤

如果 JSON 輸入為非 JSONB 的 BLOB，且轉換為文字時看起來像文字 JSON，則會被接受為文字 JSON。這實際上是 SQLite 開發人員不知道的原始實作中一個長期的錯誤。文件指出，輸入 JSON 函數的 BLOB 應該會引發錯誤。但在實際實作中，只要 BLOB 內容是資料庫文字編碼中的有效 JSON 字串，就會接受輸入。

當 JSON 常式在 3.45.0 版本（2024-01-15）中重新實作時，這個 JSON BLOB 輸入錯誤意外地被修正了。這導致依賴舊行為的應用程式中斷。（為這些應用程式辯護：它們經常被 readfile() SQL 函數（在 CLI 中可用）引誘使用 BLOB 作為 JSON。Readfile() 用於從磁碟檔案讀取 JSON，但 readfile() 會傳回 BLOB。而且對它們來說這樣做是可行的，所以為什麼不這樣做呢？）

為了向後相容，如果沒有其他解釋可行，將 BLOB 解釋為文字 JSON 的（以前不正確的）舊有行為在此記錄，並在 3.45.1 版本（2024-01-30）和所有後續版本中正式支援。

4. 函數詳細資料

下列各節提供各種 JSON 函數和運算子的操作的附加詳細資料：

4.1. json() 函數

json(X) 函數驗證其引數 X 是否為有效的 JSON 字串或 JSONB blob，並傳回已縮小的 JSON 字串版本，其中已移除所有不必要的空白。如果 X 不是格式良好的 JSON 字串或 JSONB blob，則此常式會擲回錯誤。

如果輸入是 JSON5 文字，則在傳回之前會將其轉換為標準 RFC-8259 文字。

如果傳遞給 json(X) 的引數 X 包含具有重複標籤的 JSON 物件，則會無法確定是否保留重複項。目前的實作會保留重複項。但是，此常式的未來強化可能會選擇靜默移除重複項。

範例

json(' { "this" : "is", "a": [ "test" ] } ') → '{"this":"is","a":["test"]}'

4.2. jsonb() 函數

jsonb(X) 函數傳回以引數 X 提供的 JSON 的二進位 JSONB 表示法。如果 X 是沒有有效 JSON 語法的 TEXT，則會引發錯誤。

如果 X 是 BLOB 且看似是 JSONB，則此常式只會傳回 X 的副本。不過，只會檢查 JSONB 輸入的最外層元素。不會驗證 JSONB 的深層結構。

4.3. json_array() 函數

json_array() SQL 函數接受零個或多個引數，並傳回由這些引數組成的格式良好的 JSON 陣列。如果 json_array() 的任何引數是 BLOB，則會擲回錯誤。

SQL 類型為 TEXT 的引數通常會轉換為帶引號的 JSON 字串。但是，如果引數是另一個 json1 函數的輸出，則會將其儲存為 JSON。這允許巢狀呼叫 json_array() 和 json_object()。也可以使用 json() 函數強制將字串識別為 JSON。

範例

json_array(1,2,'3',4) → '[1,2,"3",4]'
json_array('[1,2]') → '["[1,2]"]'
json_array(json_array(1,2)) → '[[1,2]]'
json_array(1,null,'3','[4,5]','{"six":7.7}') → '[1,null,"3","[4,5]","{\"six\":7.7}"]'
json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) → '[1,null,"3",[4,5],{"six":7.7}]'

4.4. jsonb_array() 函數

jsonb_array() SQL 函數運作方式與 json_array() 函數相同，但它會以 SQLite 私有的 JSONB 格式，而非標準 RFC 8259 文字格式，傳回建構的 JSON 陣列。

4.5. json_array_length() 函數

json_array_length(X) 函數會傳回 JSON 陣列 X 中的元素數量，如果 X 是陣列以外的 JSON 值，則傳回 0。json_array_length(X,P) 會在 X 中找出路徑 P 的陣列，並傳回該陣列的長度，如果路徑 P 找到的 X 元素不是 JSON 陣列，則傳回 0，如果路徑 P 找不到 X 的任何元素，則傳回 NULL。如果 X 不是格式良好的 JSON 或 P 不是格式良好的路徑，則會擲回錯誤。

範例

json_array_length('[1,2,3,4]') → 4
json_array_length('[1,2,3,4]', '$') → 4
json_array_length('[1,2,3,4]', '$[2]') → 0
json_array_length('{"one":[1,2,3]}') → 0
json_array_length('{"one":[1,2,3]}', '$.one') → 3
json_array_length('{"one":[1,2,3]}', '$.two') → NULL

4.6. json_error_position() 函數

如果輸入 X 是格式良好的 JSON 或 JSON5 字串，則 json_error_positionf(X) 函數會傳回 0。如果輸入 X 包含一個或多個語法錯誤，則此函數會傳回第一個語法錯誤的字元位置。最左邊的字元位置為 1。

如果輸入 X 是 BLOB，則如果 X 是格式良好的 JSONB blob，此常式會傳回 0。如果傳回值為正值，則表示 BLOB 中偵測到第一個錯誤的近似 1-based 位置。

json_error_position() 函數已新增至 SQLite 版本 3.42.0 (2023-05-16)。

4.7. json_extract() 函數

json_extract(X,P1,P2,...) 從 X 中的格式良好的 JSON 中萃取並傳回一個或多個值。如果只提供單一路徑 P1，則結果的 SQL 資料類型會是 JSON null 的 NULL、JSON 數值值的 INTEGER 或 REAL、JSON false 值的 INTEGER zero、JSON true 值的 INTEGER one、JSON 字串值的去引號文字，以及 JSON 物件和陣列值的文字表示。如果有多個路徑引數 (P1、P2 等)，則此常式會傳回 SQLite 文字，此文字是包含各種值的格式良好的 JSON 陣列。

範例

json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$') → '{"a":2,"c":[4,5,{"f":7}]}'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c') → '[4,5,{"f":7}]'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]') → '{"f":7}'
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f') → 7
json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a') → '[[4,5],2]'
json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]') → 5
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') → NULL
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a') → '[null,2]'
json_extract('{"a":"xyz"}', '$.a') → 'xyz'
json_extract('{"a":null}', '$.a') → NULL

SQLite 中的 json_extract() 函數與 MySQL 中的 json_extract() 函數之間存在一個微妙的不相容性。MySQL 版本的 json_extract() 始終傳回 JSON。SQLite 版本的 json_extract() 僅在有兩個或兩個以上 PATH 參數（因為結果是 JSON 陣列）或單一 PATH 參數參照陣列或物件時才傳回 JSON。在 SQLite 中，如果 json_extract() 僅有一個 PATH 參數，且該 PATH 參照 JSON null 或字串或數字值，則 json_extract() 會傳回對應的 SQL NULL、TEXT、INTEGER 或 REAL 值。

MySQL json_extract() 和 SQLite json_extract() 之間的差異僅在存取 JSON 中為字串或 NULL 的個別值時才真正顯著。下表說明了差異

作業	SQLite 結果	MySQL 結果
json_extract('{"a":null,"b":"xyz"}','$.a')	NULL	'null'
json_extract('{"a":null,"b":"xyz"}','$.b')	'xyz'	'"xyz"'

4.8. jsonb_extract() 函數

jsonb_extract() 函數運作方式與 json_extract() 函數相同，但 json_extract() 通常會傳回文字 JSON 陣列物件時，此常式會以 JSONB 格式傳回陣列或物件。對於傳回文字、數字、null 或布林 JSON 元素的常見情況，此常式運作方式與 json_extract() 完全相同。

4.9. -> 和 ->> 算子

從 SQLite 版本 3.38.0 (2022-02-22) 開始，- > 和 - > > 運算子可用於擷取 JSON 的子元件。SQLite 對 - > 和 - > > 的實作力求與 MySQL 和 PostgreSQL 相容。- > 和 - > > 運算子將 JSON 字串或 JSONB blob 作為其左運算元，並將 PATH 運算式或物件欄位標籤或陣列索引作為其右運算元。- > 運算子傳回所選子元件的文字 JSON 表示形式，如果該子元件不存在，則傳回 NULL。- > > 運算子傳回代表所選子元件的 SQL TEXT、INTEGER、REAL 或 NULL 值，如果子元件不存在，則傳回 NULL。

- > 和 - > > 運算子都會選取其左方 JSON 的相同子元件。不同之處在於 - > 始終傳回該子元件的 JSON 表示形式，而 - > > 運算子始終傳回該子元件的 SQL 表示形式。因此，這些運算子與雙引數 json_extract() 函式呼叫略有不同。如果子元件是 JSON 陣列或物件，則呼叫具有兩個引數的 json_extract() 將傳回子元件的 JSON 表示形式；如果子元件是 JSON null、字串或數值，則將傳回子元件的 SQL 表示形式。

當 - > 運算子傳回 JSON 時，它始終傳回該 JSON 的 RFC 8565 文字表示形式，而不是 JSONB。如果您需要 JSONB 格式的子元件，請使用 jsonb_extract() 函式。

- > 和 - > > 運算子的右運算元可以是格式良好的 JSON 路徑運算式。這是 MySQL 使用的形式。為了與 PostgreSQL 相容，- > 和 - > > 運算子也接受文字物件標籤或整數陣列索引作為其右運算元。如果右運算元是文字標籤 X，則將其解釋為 JSON 路徑 '$.X'。如果右運算元是整數值 N，則將其解釋為 JSON 路徑 '$[N]'。

範例

'{"a":2,"c":[4,5,{"f":7}]}' - > '$' → '{"a":2,"c":[4,5,{"f":7}]}'
'{"a":2,"c":[4,5,{"f":7}]}' - > '$.c' → '[4,5,{"f":7}]'
「{"a":2,"c":[4,5,{"f":7}]}」->「c」 →「[4,5,{"f":7}]」
「{"a":2,"c":[4,5,{"f":7}]}」->「$.c[2]」 →「{"f":7}」
「{"a":2,"c":[4,5,{"f":7}]}」->「$.c[2].f」 →「7」
「{"a":2,"c":[4,5,{"f":7}]}」->>「$.c[2].f」 → 7
「{"a":2,"c":[4,5,{"f":7}]}」->「c」-> 2 ->>「f」 → 7
「{"a":2,"c":[4,5],"f":7}」->「$.c[#-1]」 →「5」
「{"a":2,"c":[4,5,{"f":7}]}」->「$.x」 → NULL
'[11,22,33,44]' -> 3 → '44'
'[11,22,33,44]' ->> 3 → 44
「{"a":"xyz"}」->「$.a」 →「"xyz"」
「{"a":"xyz"}」->>「$.a」 →「xyz」
「{"a":null}」->「$.a」 →「null」
「{"a":null}」->>「$.a」 → NULL

4.10. json_insert()、json_replace 和 json_set() 函數

json_insert()、json_replace 和 json_set() 函數都將單一 JSON 值作為其第一個引數，後接零個或多個路徑和值引數對，並回傳一個新的 JSON 字串，此字串是由透過路徑/值對更新輸入的 JSON 所形成。這些函數之間的差異僅在於它們處理建立新值和覆寫現有值的方式。

函數	若已存在，是否覆寫？	若不存在，是否建立？
json_insert()	否	是
json_replace()	是	否
json_set()	是	是

json_insert()、json_replace() 和 json_set() 函數總是使用奇數個引數。第一個引數永遠都是要編輯的原始 JSON。後續引數以成對出現，每對的第一個元素是路徑，第二個元素是值，用於在該路徑上插入、取代或設定。

編輯從左至右順序執行。先前的編輯所造成的變更會影響後續編輯的路徑搜尋。

如果路徑/值對的值為 SQLite TEXT 值，則通常會插入為帶引號的 JSON 字串，即使該字串看起來像有效的 JSON。但是，如果該值是另一個 json 函數（例如 json() 或 json_array() 或 json_object()）的結果，或者如果它是 -> 算子的結果，則會將其解釋為 JSON 並插入為 JSON，保留其所有子結構。為 ->> 算子結果的值總是會被解釋為 TEXT，並插入為 JSON 字串，即使它們看起來像有效的 JSON。

如果第一個 JSON 參數格式不正確，或任何 PATH 參數格式不正確，或任何參數為 BLOB，這些常式會擲回錯誤。

要將元素追加到陣列的尾端，請使用 json_insert() 搭配陣列索引「#」。範例

json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]'
json_insert('[1,[2,3],4]','$[1][#]',99) → '[1,[2,3,99],4]'

其他範例

json_insert('{"a":2,"c":4}', '$.a', 99) → '{"a":2,"c":4}'
json_insert('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'
json_replace('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
json_replace('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4}'
json_set('{"a":2,"c":4}', '$.a', 99) → '{"a":99,"c":4}'
json_set('{"a":2,"c":4}', '$.e', 99) → '{"a":2,"c":4,"e":99}'
json_set('{"a":2,"c":4}', '$.c', '[97,96]') → '{"a":2,"c":"[97,96]"}'
json_set('{"a":2,"c":4}', '$.c', json('[97,96]')) → '{"a":2,"c":[97,96]}'
json_set('{"a":2,"c":4}', '$.c', json_array(97,96)) → '{"a":2,"c":[97,96]}'

4.11. jsonb_insert()、jsonb_replace 和 jsonb_set() 函數

jsonb_insert()、jsonb_replace() 和 jsonb_set() 函數的工作原理分別與 json_insert()、json_replace() 和 json_set() 相同，但 "jsonb_" 版本會以二進位 JSONB 格式傳回結果。

4.12. json_object() 函數

json_object() SQL 函數接受零個或多個參數對，並傳回由這些參數組成的格式良好的 JSON 物件。每個參數對的第一個參數是標籤，第二個參數是值。如果 json_object() 的任何參數是 BLOB，則會擲回錯誤。

json_object() 函數目前允許重複標籤而不抱怨，儘管這可能會在未來的增強功能中有所改變。

具有 SQL 類型 TEXT 的參數通常會轉換成帶引號的 JSON 字串，即使輸入文字是格式良好的 JSON。但是，如果參數是另一個 JSON 函數或 -> 運算子 (但不是 ->> 運算子) 的直接結果，則會將其視為 JSON，並保留其所有 JSON 類型資訊和子結構。這允許巢狀呼叫 json_object() 和 json_array()。 json() 函數也可以用來強制將字串辨識為 JSON。

範例

json_object('a',2,'c',4) → '{"a":2,"c":4}'
json_object('a',2,'c','{e:5}') → '{"a":2,"c":"{e:5}"}'
json_object('a',2,'c',json_object('e',5)) → '{"a":2,"c":{"e":5}}'

4.13. jsonb_object() 函數

jsonb_object() 函數的工作原理與 json_object() 函數相同，但產生的物件會以二進位 JSONB 格式傳回。

4.14. json_patch() 函數

json_patch(T,P) SQL 函數會執行 RFC-7396 MergePatch 演算法，以將修補程式 P 套用到輸入 T。會傳回 T 的修補程式副本。

MergePatch 可以新增、修改或刪除 JSON 物件的元素，因此對於 JSON 物件而言，json_patch() 常式是 json_set() 和 json_remove() 的一般化替換。不過，MergePatch 會將 JSON 陣列物件視為原子。MergePatch 無法附加到陣列，也無法修改陣列的個別元素。它只能插入、替換或刪除整個陣列作為單一單位。因此，在處理包含陣列的 JSON，特別是具有大量子結構的陣列時，json_patch() 並沒有那麼好用。

範例

json_patch('{"a":1,"b":2}','{"c":3,"d":4}') → '{"a":1,"b":2,"c":3,"d":4}'
json_patch('{"a":[1,2],"b":2}','{"a":9}') → '{"a":9,"b":2}'
json_patch('{"a":[1,2],"b":2}','{"a":null}') → '{"b":2}'
json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}') → '{"a":9,"c":8}'
json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}') → '{"a":{"x":1,"y":9},"b":3,"c":8}'

4.15. jsonb_patch() 函數

jsonb_patch() 函數的工作原理與 json_patch() 函數相同，但修補後的 JSON 會以二進位 JSONB 格式傳回。

4.16. json_remove() 函數

json_remove(X,P,...) 函數將單一 JSON 值作為其第一個參數，後接零個或多個路徑參數。json_remove(X,P,...) 函數會傳回 X 參數的副本，並移除由路徑參數識別的所有元素。選擇在 X 中找不到的元素的路徑會被靜默忽略。

移除會從左到右順序進行。先前移除造成的變更會影響後續參數的路徑搜尋。

如果 json_remove(X) 函數未帶任何路徑參數呼叫，則它會傳回重新格式化的輸入 X，並移除多餘的空白。

如果第一個參數不是格式良好的 JSON，或是任何後續參數不是格式良好的路徑，則 json_remove() 函數會擲回錯誤。

範例

json_remove('[0,1,2,3,4]','$[2]') → '[0,1,3,4]'
json_remove('[0,1,2,3,4]','$[2]','$[0]') → '[1,3,4]'
json_remove('[0,1,2,3,4]','$[0]','$[2]') → '[1,2,4]'
json_remove('[0,1,2,3,4]','$[#-1]','$[0]') → '[1,2,3]'
json_remove('{"x":25,"y":42}') → '{"x":25,"y":42}'
json_remove('{"x":25,"y":42}','$.z') → '{"x":25,"y":42}'
json_remove('{"x":25,"y":42}','$.y') → '{"x":25}'
json_remove('{"x":25,"y":42}','$') → NULL

4.17. jsonb_remove() 函數

jsonb_remove() 函數的工作方式就像 json_remove() 函數，只不過編輯後的 JSON 結果會以二進位 JSONB 格式傳回。

4.18. json_type() 函數

json_type(X) 函數會傳回 X 最外層元素的「類型」。json_type(X,P) 函數會傳回 X 中由路徑 P 選取的元素的「類型」。json_type() 傳回的「類型」是下列 SQL 文字值之一：'null'、'true'、'false'、'integer'、'real'、'text'、'array' 或 'object'。如果 json_type(X,P) 中的路徑 P 選取在 X 中不存在的元素，則此函數會傳回 NULL。

如果 json_type() 函數的第一個引數不是格式良好的 JSON 或 JSONB，或者第二個引數不是格式良好的 JSON 路徑，則會擲回錯誤。

範例

json_type('{"a":[2,3.5,true,false,null,"x"]}') → 'object'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$') → 'object'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a') → 'array'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]') → 'integer'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]') → 'real'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]') → 'true'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]') → 'false'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]') → 'null'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]') → 'text'
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]') → NULL

4.19. json_valid() 函數

json_valid(X,Y) 函數如果引數 X 是格式良好的 JSON，則傳回 1，否則傳回 0。Y 參數是一個整數位元遮罩，定義「格式良好」的意義。目前定義了 Y 的下列位元

0x01 → 輸入是嚴格符合標準 RFC-8259 JSON 的文字，沒有任何擴充。
0x02 → 輸入是文字，其為 JSON，帶有上述所述的 JSON5 擴充。
0x04 → 輸入是一個 BLOB，表面上看起來像是 JSONB。
0x08 → 輸入是一個 BLOB，嚴格符合內部 JSONB 格式。

透過組合位元，可以得到下列有用的 Y 值

1 → X 是 RFC-8259 JSON 文字
2 → X 是 JSON5 文字
4 → X 可能為 JSONB
5 → X 為 RFC-8259 JSON 文字或 JSONB
6 → X 為 JSON5 文字或 JSONB ← 這可能是您要的值
8 → X 為嚴格符合 JSONB
9 → X 為 RFC-8259 或嚴格符合 JSONB
10 → X 為 JSON5 或嚴格符合 JSONB

Y 參數為選用。如果省略，則預設為 1，表示預設行為僅在輸入 X 為嚴格符合 RFC-8259 JSON 文字且無任何延伸時，才回傳 true。這讓 json_valid() 的單一參數版本與舊版的 SQLite 相容，也就是在加入對 JSON5 和 JSONB 的支援之前。

Y 參數中的 0x04 和 0x08 位元之間的差異在於，0x04 僅檢查 BLOB 的外層包裝，以查看其表面上是否看起來像 JSONB。這對於大多數目的來說已足夠，而且非常快。0x08 位元會徹底檢查 BLOB 的所有內部詳細資料。0x08 位元會花費與 X 輸入大小成線性的時間，而且慢得多。對於大多數目的來說，建議使用 0x04 位元。

如果您只想了解某個值是否為其他 JSON 函數的合理輸入，則 Y 值為 6 可能就是您要使用的值。

對於最新版本的 json_valid()，任何小於 1 或大於 15 的 Y 值都會引發錯誤。但是，未來版本的 json_valid() 可能會增強為接受此範圍之外的旗標值，並具有我們尚未想到的新意義。

如果 json_valid() 的 X 或 Y 輸入為 NULL，則函數會傳回 NULL。

範例

json_valid('{"x":35}') → 1
json_valid('{x:35}') → 0
json_valid('{x:35}',6) → 1
json_valid('{"x":35') → 0
json_valid(NULL) → NULL

4.20. json_quote() 函數

json_quote(X) 函數會將 SQL 值 X (數字或字串) 轉換成對應的 JSON 表示法。如果 X 是由其他 JSON 函數傳回的 JSON 值，則此函數為 no-op。

範例

json_quote(3.14159) → 3.14159
json_quote('verdant') → '"verdant"'
json_quote('[1]') → '"[1]"'
json_quote(json('[1]')) → '[1]'
json_quote('[1,') → '"[1,"'

4.21. 陣列和物件彙總函數

json_group_array(X) 函數是彙總 SQL 函數，會傳回一個 JSON 陣列，其中包含彙總中所有 X 值。類似地，json_group_object(NAME,VALUE) 函數會傳回一個 JSON 物件，其中包含彙總中所有 NAME/VALUE 成對值。"jsonb_" 變體相同，但會以二進位 JSONB 格式傳回結果。

4.22. json_each() 和 json_tree() 表值函數

json_each(X) 和 json_tree(X) 表值函數會遍歷提供為第一個引數的 JSON 值，並為每個元素傳回一列。json_each(X) 函數只會遍歷頂層陣列或物件的直接子項，或者如果頂層元素是基本值，則只會遍歷頂層元素本身。json_tree(X) 函數會遞迴地遍歷 JSON 子結構，從頂層元素開始。

json_each(X,P) 和 json_tree(X,P) 函數的工作原理與其單一引數對應函數相同，但它們將由路徑 P 識別的元素視為頂層元素。

json_each() 和 json_tree() 所傳回的資料表的架構如下

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 陣列元素的整數陣列索引，以及 JSON 物件元素的文字標籤。在所有其他情況下，「key」欄位為 NULL。

「atom」欄位是對應於原始元素的 SQL 值，也就是非 JSON 陣列和物件的元素。「atom」欄位對於 JSON 陣列或物件為 NULL。「value」欄位對於原始 JSON 元素與「atom」欄位相同，但對於陣列和物件則採用文字 JSON 值。

「type」欄位是根據目前 JSON 元素的類型，從 ('null', 'true', 'false', 'integer', 'real', 'text', 'array', 'object') 所取得的 SQL 文字值。

「id」欄位是一個整數，用於識別完整 JSON 字串中的特定 JSON 元素。「id」整數是內部管理編號，其計算方式可能會在未來的版本中變更。唯一的保證是「id」欄位在每一列都會不同。

對於 json_each()，「parent」欄位永遠為 NULL。對於 json_tree()，「parent」欄位是目前元素父項目的「id」整數，或者對於頂層 JSON 元素或第二個引數中根路徑所識別的元素為 NULL。

「fullkey」欄位是文字路徑，用於在原始 JSON 字串中唯一識別目前列元素。即使「root」引數提供了替代的起始點，仍會傳回至真實頂層元素的完整金鑰。

「path」欄位是包含目前列的陣列或物件容器的路徑，或者在迭代從原始類型開始並因此只提供單一輸出列的情況下，至目前列的路徑。

4.22.1. 使用 json_each() 和 json_tree() 的範例

假設表格「CREATE TABLE user(name,phone)」將零或多個電話號碼儲存在 user.phone 欄位中的 JSON 陣列物件。若要找出擁有任何 704 區碼電話號碼的所有使用者

SELECT DISTINCT user.name
  FROM user, json_each(user.phone)
 WHERE json_each.value LIKE '704-%';

現在假設 user.phone 欄位包含純文字（如果使用者只有一個電話號碼）和 JSON 陣列（如果使用者有多個電話號碼）。提出相同問題：「哪些使用者擁有 704 區碼的電話號碼？」但現在 json_each() 函數只能呼叫擁有兩個或多個電話號碼的使用者，因為 json_each() 需要格式良好的 JSON 作為其第一個引數

SELECT name FROM user WHERE phone LIKE '704-%'
UNION
SELECT user.name
  FROM user, json_each(user.phone)
 WHERE json_valid(user.phone)
   AND json_each.value LIKE '704-%';

考慮具有「CREATE TABLE big(json JSON)」的不同資料庫。若要查看資料的完整逐行分解

SELECT big.rowid, fullkey, value
  FROM big, json_tree(big.json)
 WHERE json_tree.type NOT IN ('object','array');

在前面，WHERE 子句的「type NOT IN ('object','array')」項目會抑制容器，只讓葉子元素通過。可以用這種方式達成相同的效果

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

假設 BIG 表格中的每個項目都是一個 JSON 物件，其中 '$.id' 欄位是唯一識別碼，而 '$.partlist' 欄位可以是深度巢狀物件。您想要找出每個項目的 id，其中包含一個或多個對 uuid '6fa5181e-5721-11e5-a04e-57f3d7b32808' 的參考，位於 '$.partlist' 中的任何地方。

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';

此頁面最後修改於 2024-03-14 14:17:16 UTC