學習基礎：如何在SQLite中使用JSON

在本指南中，我們將探索兩種流行技術——JSON與SQLite之間的迷人交匯點。了解如何在SQLite中使用JSON對於現代軟件開發至關重要，尤其是當處理可能不完全適合表格結構的複雜數據結構時。無論您是經驗豐富的開發者還是渴望擴展知識的初學者，本教程都將幫助您開始學習如何在SQLite數據庫中使用JSON。

讓我們開始吧！

利用SQLite增強現代應用數據管理

SQLite作為部署最廣泛、使用最廣泛的數據庫引擎，是一款用戶友好的數據庫，無需任何複雜的設置或服務器連接。SQLite直觀且適應性強，適用於各種應用，使其成為軟件開發的首選。SQLite的二進制足跡較小，通常小於1 MB，意味著它比其他數據庫更輕便。此外，SQLite完全符合ACID原則。

SQLite的另一個獨特之處在於，它非常適合個別應用程式和互聯網連接設備，如智能家居裝置，這些都是物聯網（IoT）的一部分。此外，儘管其簡潔性，SQLite對標準SQL語言有著強大的掌控能力，能夠處理事務、子查詢和觸發器等事項。因此，SQLite使用簡單，但功能依然相當強大。

SQLite的能力不僅限於簡單的數據存儲。它高效且用戶友好，具備全文搜索和blob支援等特性。SQLite還提供了一個擴展機制，用於增加額外功能，使其成為現代軟件生態系統中適應性強的工具。

有趣的事實：你知道嗎，雖然許多人將SQLite發音為’S-Q-Lite’（序列光），但其創造者Richard Hipp實際上希望它被發音為’S-Q-L-ite’（資料庫光），就像一種礦物，強調其堅固而輕巧的特性。

SQLite在現代關聯式資料庫領域的卓越之處

SQLite 是輕量級且無伺服器的解決方案，適用於那些全面性的客戶端-伺服器資料庫可能過於龐大的場景。由於 SQLite 是自包含的，它不依賴任何外部依賴，因此非常可靠。SQLite 資料庫能夠跨不同的檔案系統和架構進行移植，使得在 SQLite 資料庫中的數據遷移變得輕而易舉。

SQLite 的典型應用案例遍及多種領域，作為現存最廣泛部署的資料庫引擎。例如，SQLite 是應用程式，尤其是移動應用中本地持久化的標準選擇。SQLite 也廣泛用於數據分析和測試，其清晰度和強大功能組合成為致勝組合。最後，SQLite 是網站數據存儲的理想選擇，能夠管理用戶數據、網站內容等。

SQLite 的性能令人印象深刻，對於大多數常見的操作，其速度常常超過其他著名的資料庫。

為何SQLite的JSON處理能力使其成為現代數據管理的傑出選擇

有效管理非結構化數據是許多開發者面臨的挑戰。這正是JSON發揮作用的地方。作為一種靈活、無模式的數據格式，JSON非常適合處理無法整齊地放入表格結構的數據。

通過在SQLite中存儲JSON數據，您可以利用SQLite強大的查詢能力高效地提取和操作JSON數據。這種結合的美妙之處在於，SQLite配備了內置函數，可以簡單地管理JSON數據。此外，JSON作為數據交換格式的普及及其可移植性意味著存儲在SQLite中的JSON數據可以輕鬆共享、遷移或導出到不同系統。

SQLite的JSON支持隨著時間的推移已經成熟。它最初在2015年發布的3.9.0版本中作為擴展引入，但後續版本提供了對JSON的內置支持。SQLite允許您使用TEXT列和一系列JSON函數（如json()、json_extract()、json_object()和json_array()）保存和檢索JSON數據。

理解SQLite用於JSON查詢的強大函數

SQLite 透過JSON 函數管理和操作 JSON 資料。以下列出了 SQLite 中最常用的 10 個 JSON 函數，並提供參考，每個函數的使用案例將透過簡單的 SQL 查詢在接下來的章節中進行示例。

1. json()：此函數驗證字串是否為有效的 JSON。若是，函數返回相同的 JSON；若不是，則返回 NULL。
2. json_extract()：此函數使用路徑從 JSON 字串中提取對象。
3. json_array()：此函數創建一個 JSON 數組。
4. json_array_length()：此函數返回 JSON 數組的長度。
5. json_insert()：此函數將 JSON 值插入到 JSON 字串中。
6. json_object()：此函數創建一個 JSON 對象。
7. json_remove()：此函數從 JSON 字串中移除一個屬性。
8. json_replace()：此函數替換 JSON 字串中的值。
9. json_type()：此函數返回 JSON 值的類型（如 INTEGER, REAL, NULL, TRUE, FALSE, TEXT, 和 BLOB）。
10. json_valid()：此函數驗證字串是否為有效的 JSON。

SQLite 中 JSON 編碼與解碼函數的實用系列示例

在本節中，我們提供了每個JSON函數的簡化示例及簡要說明，這些函數在我們前一節中列出。我們使用來自Dadroit JSON 生成器的JSON數據作為示例。以下是原始JSON，以便您了解背景。

SQLite 中的 json() 函數

此查詢將JSON文本轉換為JSON對象。

SELECT
    json ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}' ) AS json_object;

此查詢的結果將如下所示：

SQLite 中的 json_extract() 函數

此查詢通過使用路徑從JSON對象中提取Name值。

SELECT json_extract('{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Name') AS movie_name;

此查詢的結果將如下所示：

SQLite 中的 json_array() 函數

此查詢從提供的輸入創建新的JSON數組。

SELECT
    json_array ( 1, 2, 3 ) AS array_result;

結果將如下所示：

SQLite 中的 json_type() 函數

此查詢從JSON對象中獲取Year值的數據類型。

SELECT
    json_type ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Year' ) AS property_type;

結果將如下所示：

SQLite 中的 json_array_length() 函數

此查詢計算JSON對象中Cast數組的元素數量。

SELECT
    json_array_length ( '{"Genre":["Comedy","Crime"],"Cast":["Adrian Gratianna","Tani O''Hara","Tessie Delisle"]}', '$.Cast' ) AS array_length;

結果將如下所示：

SQLite 中的 json_object() 函數

此查詢建立一個包含ID和Name鍵值對的JSON物件。

SELECT
    json_object ( 'ID', 1, 'Name', 'Forgotten in the Planet' ) AS result;

結果將如下所示：

SQLite中的json_insert()函數

此查詢將Director鍵值屬性插入JSON物件中。

SELECT
    json_insert ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Director', 'Henrie Randell Githens' ) AS insert_movie;

結果將如下所示：

SQLite中的json_remove()函數

此查詢從JSON物件中移除Director鍵值對。

SELECT
    json_remove ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Director' ) AS result_of_remove;

結果將如下所示：

SQLite中的json_replace()函數

此查詢將JSON物件中的Year替換為新值1971。

SELECT
     json_replace ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Year', 1971 ) AS result_of_replace;

結果將如下所示：

SQLite中的json_valid()函數

此查詢檢查提供的字串是否具有有效JSON所需的正確語法和結構，如果是，則返回1，否則返回0。

SELECT
     json_valid ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}' ) AS result_of_valid;

結果將如下所示：

使用SQLite中JSON函數增強SQL查詢的實用查詢示例

既然您已經了解了SQLite中JSON的基本知識，接下來將展示一些使用先前提到的JSON函數和先前提到的JSON數據作為輸入，在SQLite數據庫中處理JSON數據的實際工作流程示例。

使用插入查詢在SQLite中存儲JSON數據

首先，您需要將JSON插入SQLite資料庫。讓我們建立一個名為movies的表，其中包含一個名為data的文本字段，因為您可以在SQLite的文本字段中存儲JSON。您將使用此data字段來存儲和檢索JSON值：

CREATE TABLE movies ( data TEXT );

接著，讓我們將JSON插入到表movies的字段data中：

INSERT INTO movies ( data )
 VALUES
     ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Genre":["Comedy","Crime"],"Director":"Henrie Randell Githens","Cast":["Adrian Gratianna","Tani OHara","Tessie Delisle"],"Runtime":90,"Rate":7.0}' );

要編輯（替換、插入、刪除等）SQLite中的JSON，您可以使用json_replace()、json_insert()和json_remove()函數。

以下查詢將ID為1的電影的name替換為新值：

UPDATE movies
 SET data = json_replace ( data, '$.Name', 'Found in the Universe' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

以下查詢將一個新屬性作為新字段插入到之前存儲在行中的JSON數據中：

UPDATE movies
 SET data = json_insert ( data, '$.Country', 'USA' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

以下查詢從之前存儲在行中的JSON數據中刪除Runtime屬性：

UPDATE movies
 SET data = json_remove ( data, '$.Runtime' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

從SQLite提取JSON數據

要從SQLite檢索JSON數據，您可以使用json_extract()或簡寫操作符->：

選擇電影的名稱：

SELECT
     json_extract ( data, '$.Name' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

或使用->簡寫操作符：

SELECT
     data -> '$.Name'
 FROM
     movies
 WHERE
     data -> '$.ID' = 1;

檢索類型列表：

SELECT
     json_extract ( data, '$.Genre' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

從Cast列表中檢索第一位演員：

SELECT
     json_extract ( data, '$.Cast[0]' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

提取Year和Rate：

SELECT
     json_extract ( data, '$.Year' ) AS Year,
     json_extract ( data, '$.Rate' ) AS Rate
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

結論見解

恭喜完成這段學習旅程！你已經了解到JSON數據類型與SQLite數據庫如何協同工作。SQLite是一個非常實用的工具，簡單而強大，易於使用。儘管它體積小，卻充滿了實用功能。

簡單來說，SQLite允許我們通過文本列和一些JSON函數來保存和提取JSON數據。這些函數使我們能夠探索、分析和更改SQLite數據庫中的JSON數據。SQLite提供了許多工具來管理JSON數據，從添加和更改JSON數據到為各種目的提取數據。我們介紹了SQLite中十個主要的JSON函數，這些函數使得處理JSON數據更加簡單。接著，我們通過一些SQL查詢的例子，展示了如何在SQLite中使用這些JSON函數。

記住：熟練使用JSON與SQLite是一項需要更深入實踐的技能。因此，不要猶豫，深入研究，進行實驗，並學習。最後，如果你覺得這份指南有幫助，歡迎分享。祝你編程旅程愉快！