PostgreSQL中json資料型別詳解

前言

        JSON（JavaScript Object Notation）是一種輕量級的資料交換格式。它基於 ECMAScript（European Computer Manufacturers Association, 歐洲計算機協會制定的js規範）的一個子集，採用完全獨立於程式語言的文字格式來儲存和表示資料。簡潔和清晰的層次結構使得 JSON 成為理想的資料交換語言。 易於人閱讀和編寫，同時也易於機器解析和生成，並有效地提升網路傳輸效率。其實JSON作為一種資料規範和標準，在用於介面交換，系統設定，資料儲存方面擁有得天獨厚的一席之地。

       在儲存技術已經高速發達的今天，對於json資料的儲存和使用，有多重方式。比如在快取界的一哥Redis,檔案資料庫的佼佼者Mongodb等等。上述兩者的基礎資料結構也是JSON，基於json豐富的開發介面和支援。

      隨著技術和業務的發展，除了上述兩者之外，許多傳統的關係型資料庫，如MySQL、PostgreSQL（以下簡稱PG）等等都開始支援json資料的儲存和高效查詢。在不增加額外的技術棧學習成本之下，基於統一的資料庫庫知識來滿足日常的業務需求，也是一種合理的技術選型（滿足了日常業務即可）。本文重點講述在PG中關於json型別的介紹，json和jsonb的區別，json和jsonb的基本操作、輸出區別、包含測試等內容，讓各位對json型別有一個基本直觀的認識。理解最基礎的資料庫操作。

一、PG資料庫中JSON的型別

       json資料也可以被儲存為text，但是 與text資料型別相比，JSON 資料型別的優勢在於能強制要求每個被儲存的值符合 JSON 規則。也有很多 JSON 相關的函 數和操作符可以用於儲存在這些資料型別中的資料。基於text型別的資料，無法直接利用資料庫的查詢技術來提高查詢效率。而且需要在應用程式中進行相關的轉換。眾所周知，PostgreSQL 提供儲存JSON資料的兩種型別：json 和 jsonb。

1、json和jsonb的區別

       json 和 jsonb資料型別接受幾乎完全相同的值集合作為輸入。 主要的實際區別之一是效率。json資料型別儲存輸入文字的精準拷貝，處理常式必須在每 次執行時必須重新解析該資料。而jsonb資料被儲存在一種分解好的 二進位制格式中，它在輸入時要稍慢一些，因為需要做附加的轉換。但是 jsonb在處理時要快很多，因為不需要解析。jsonb也支 持索引，這也是一個令人矚目的優勢（言外之意，json型別對索引程度不是特別友好）。

       由於json型別儲存的是輸入文字的準確拷貝，其中可能會保留在語法 上不明顯的、存在於記號之間的空格，還有 JSON 物件內部的鍵的順序。還有， 如果一個值中的 JSON 物件包含同一個鍵超過一次，所有的鍵/值對都會被保留（ 處理常式會把最後的值當作有效值）。相反，jsonb不保留空格、不 保留物件鍵的順序並且不保留重複的物件鍵。如果在輸入中指定了重複的鍵，只有 最後一個值會被保留。

2、專案開發中的選擇

        從資料插入更新處理速度上，json>jsonb。在資料查詢效能上jsonb>json。在資料的整體空間佔用上，json>jsonb。

        因此，通常在一般的技術開發過程中，除非有特別特殊的需要（歷史遺留問題等），大多數應用應該 更願意把 JSON 資料儲存為jsonb（通過json函數和函數索引的加持下，jsonb的查詢能力得到了大大的增強）。

3、json資料型別

        在pg中的json資料型別可以分為：String，Number,boolean,Null。下面給出一個表格，是關於json的基本資料型別和pg資料型別的一個對比和對照。

        這裡關於編碼有一個需要解釋的地方，就是Unicode的跳脫問題。這裡涉及到資料庫在建立的時候是不是使用utf-8的編碼儲存。在json型別的輸入函數中，不管資料庫 編碼如何都允許 Unicode 跳脫，並且只檢查語法正確性（即，跟在u 後面的四個十六進位制位）。但是，jsonb的輸入函數更加嚴格：它不允 許非 ASCII 字元的 Unicode 跳脫（高於U+007F的那些），除非資料 庫編碼是 UTF8。jsonb型別也拒絕u0000（因為 PostgreSQL的text型別無法表示 它），並且它堅持使用 Unicode 代理對來標記位於 Unicode 基本多語言平面之外 的字元是正確的。合法的 Unicode 跳脫會被轉換成等價的 ASCII 或 UTF8 字元進 行儲存，這包括把代理對摺疊成一個單一字元。在把文字 JSON 輸入轉換成jsonb時，RFC 7159描述 的基本型別會被有效地對映到原生的 PostgreSQL型別（如 上表描述）。因此，在合法 jsonb資料的組成上有一些次要額外約束，它們不適合 json型別和抽象意義上的 JSON，這些約束對應於有關哪些東西不 能被底層資料型別表示的限制。尤其是，jsonb將拒絕位於 PostgreSQL numeric資料型別範 圍之外的數位，而json則不會。不過，實際上這類問題更可能發生在其他實 現中，因為把 JSON 的number基本型別表示為 IEEE 754 雙精度浮點 是很常見的（這也是RFC 7159 明確期待和允許的）。當在這類系 統間使用 JSON 作為一種交換格式時，應該考慮丟失數位精度的風險。

二、PG中json的簡單操作

1、基礎json資料操作

-- 簡單標量/基本值
-- 基本值可以是數位、帶引號的字串、true、false或者null
SELECT '5'::json;
 
-- 有零個或者更多元素的陣列（元素不需要為同一型別）
SELECT '[1, 2, "foo", null]'::json;
 
-- 包含鍵值對的物件
-- 注意物件鍵必須總是帶引號的字串
SELECT '{"name": "張三", "age": 39, "active": false,"sex":"男"}'::json;
 
-- 陣列和物件可以被任意巢狀
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

2、json和jsonb輸出對比

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)
 
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

        通過這裡輸出可以看到，將目標物件作為json輸出時，輸出結果和輸入基本保持一致。 對於第二條語句而言，內容上似乎沒有什麼太大的變化，但是輸出結果的順序與第一條有明顯的區別。

再來看一組有意思的輸出，依然是關於jsonb和json的number結果的展示。

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

        很明顯的區別是jsonb被資料庫的執行引擎給優化了，展示結果與json也不同。

3、jsonb包含測試

        在很多的場景中，我們會使用API對兩個json進行是否包含的判斷，因為在json型別中，使用包含判斷也是比較耗費時間的，在pg資料庫中，天然提供了資料庫層的包含函數，以此來提高查詢匹配能力。在jsonb的查詢中，使用@>進行包含的查詢操作。

-- 右邊具有一個單一鍵值對的物件被包含在左邊的物件中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;
 
-- 右邊的陣列不會被認為包含在左邊的陣列中，
-- 即使其中嵌入了一個相似的陣列：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- 得到假
 
-- 但是如果同樣也有巢狀，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;
 
-- 類似的，這個例子也不會被認為是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- 得到假
 
-- 包含一個頂層鍵和一個空物件：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

總結

以上就是本文的基本內容，本文首先介紹了通用的json相關知識，然後重點講述在PG中關於json型別的介紹，json和jsonb的區別，最後以案例的形式詳細說明json和jsonb的基本操作、輸出區別、包含測試等內容，讓各位對json型別有一個基本直觀的認識，理解最基礎的資料庫操作。

到此這篇關於PostgreSQL中json資料型別的文章就介紹到這了,更多相關PostgreSQL json型別內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！