PolarDB：JSON類型

根據 RFC 7159 中的說明，JSON 資料類型是用來儲存 JSON（JavaScript Object Notation） 資料的。這種資料也可以被儲存為text，但是 JSON 資料類型的優勢在於能強制要求每個被儲存的值符合 JSON 規則。也有很多 JSON 相關的函數和操作符可以用於儲存在這些資料類型中的資料。

本資料庫提供儲存 JSON 資料的兩種類型：json 和 jsonb。 為了實現這些資料類型高效的查詢機制，還提供了jsonpath資料類型描述。

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

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

通常，除非有特別特殊的需要（例如遺留的對象鍵順序假設），大多數應用應該更願意把 JSON 資料存放區為jsonb。

RFC 7159 指定 JSON 字串應以 UTF8 編碼。因此 JSON 類型不可能嚴格遵守 JSON 規範，除非資料庫編碼是 UTF8。嘗試直接包括資料庫編碼中無法表示的字元將會失敗。反過來，能在資料庫編碼中表示但是不在 UTF8 中的字元是被允許的。

RFC 7159 允許 JSON 字串包含\u``XXXX 所標記的 Unicode 逸出序列。在json類型的輸入函數中，不管資料庫編碼如何都允許 Unicode 轉義，並且只檢查文法正確性（即，跟在\u 後面的四個十六進位位）。但是，jsonb的輸入函數更加嚴格：它不允許對無法在資料庫編碼中表示的字元進行 Unicode 轉義。jsonb類型也拒絕\u0000（因為本資料庫的text類型無法表示它），並且它堅持使用 Unicode 代理對來標記位於 Unicode 基本多語言平面之外的字元是正確的。合法的 Unicode 轉義會被轉換成等價的單個字元進行儲存，這包括把代理對摺疊成一個單一字元。

在把文本 JSON 輸入轉換成jsonb時，RFC 7159 描述的基本類型會被有效地映射到原生的本資料庫類型。因此，在合法 jsonb資料的組成上有一些次要額外約束，它們不適合 json類型和抽象意義上的 JSON，這些約束對應於有關哪些東西不能被底層資料類型表示的限制。尤其是，jsonb將拒絕位於本資料庫 numeric資料類型範圍之外的數字，而json則不會。這類實現定義的限制是 RFC 7159 所允許的。不過，實際上這類問題更可能發生在其他實現中，因為把 JSON 的number基本類型表示為 IEEE 754 雙精確度浮點是很常見的（這也是 RFC 7159 明確期待和允許的）。當在這類系統間使用 JSON 作為一種交換格式時，應該考慮丟失數字精度的風險。

相反地，如表中所述，有一些 JSON 基本類型輸入格式上的次要限制並不適用於相應的本資料庫類型。

JSON 基本類型和相應的本資料庫類型

JSON 輸入和輸出文法

RFC 7159 中定義了 JSON 資料類型的輸入/輸出文法。

下列都是合法的json（或者jsonb）運算式：

    -- 簡單標量/基本值
    -- 基本值可以是數字、帶引號的字串、true、false或者null
    SELECT '5'::json;

    -- 有零個或者更多元素的數組（元素不需要為同一類型）
    SELECT '[1, 2, "foo", null]'::json;

    -- 包含索引值對的對象
    -- 注意對象鍵必須總是帶引號的字串
    SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

    -- 數組和對象可以被任意嵌套
    SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

如前所述，當一個 JSON 值被輸入並且接著不做任何附加處理就輸出時， 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)

值得一提的一種語義上無意義的細節是，在jsonb中資料會被按照底層 numeric類型的行為來列印。實際上，這意味著用E記號輸入的數字被列印出來時就不會有該記號，例如：

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

不過，如這個例子所示，jsonb將會保留拖尾的小數點後的零，即便這對於等值檢查等目的來說是語義上無意義的。

對於可用於構造和處理 JSON 值的內建函數和運算子的列表。

設計JSON文檔

將資料表示為 JSON 比傳統關係資料模型要靈活得多，在需求不固定時這種優勢更加令人感興趣。在同一個應用裡非常有可能有兩種方法共存並且互補。不過，即便是在要求最大靈活性的應用中，我們還是推薦 JSON 文檔有固定的結構。該結構通常是非強制的（儘管可能會強制一些商務規則），但是有一個可預測的結構會使書寫概括一個表中的 “文檔”（資料）集合的查詢更容易。

當被儲存在表中時，JSON 資料也像其他資料類型一樣服從相同的並發控制考慮。儘管儲存大型文檔是可行的，但是要記住任何更新都在整行上要求一個行級鎖。為了在更新事務之間減少鎖爭奪，可考慮把 JSON 文檔限制到一個可管理的尺寸。理想情況下，JSON 文檔應該每個表示一個原子資料，商務規則命令不會進一步把它們劃分成更小的可獨立修改的資料。

jsonb包含和存在

測試包含是jsonb的一種重要能力。對 json類型沒有平行的功能集。包含測試會測試一個 jsonb文檔是否被包含在另一個文檔中。除了特別註解之外，這些例子都會返回真：

    -- 簡單的標量/基本值只包含相同的值：
    SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

    -- 右邊的數字被包含在左邊的數組中：
    SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

    -- 數組元素的順序沒有意義，因此這個例子也返回真：
    SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

    -- 重複的數組元素也沒有關係：
    SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::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;  -- 得到假

    -- 包含一個頂層鍵和一個Null 物件：
    SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

一般原則是被包含的對象必須在結構和資料內容上匹配包含對象，這種匹配可以是從包含對象中丟棄了不匹配的數組元素或者對象索引值對之後成立。但是記住做包含匹配時數組元素的順序是沒有意義的，並且重複的數組元素實際也只會考慮一次。

結構必須匹配的一般原則有一種特殊情況，一個數組可以包含一個基本值：

    -- 這個數組包含基底字元串值：
    SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

    -- 反之不然，下面的例子會報告“不包含”：
    SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- 得到假

jsonb還有一個存在操作符，它是包含的一種變體：它測試一個字串（以一個text值的形式給出）是否出現在jsonb值頂層的一個對象鍵或者數組元素中。除非特別註解， 下面這些例子返回真：

    -- 字串作為一個數組元素存在：
    SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

    -- 字串作為一個對象鍵存在：
    SELECT '{"foo": "bar"}'::jsonb ? 'foo';

    -- 不考慮對象值：
    SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- 得到假

    -- 和包含一樣，存在必須在頂層匹配：
    SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- 得到假

    -- 如果一個字串匹配一個基本 JSON 字串，它就被認為存在：
    SELECT '"foo"'::jsonb ? 'foo';

當涉及很多鍵或元素時，JSON 對象比數組更適合於做包含或存在測試， 因為它們不像數組，進行搜尋時會進行內部最佳化，並且不需要被線性搜尋。

由於 JSON 的包含是嵌套的，因此一個恰當的查詢可以跳過對子物件的明確選取。 例如，假設我們在頂層有一個doc列包含著對象，大部分對象包含著tags域，其中有子物件的數組。這個查詢會找到其中出現了同時包含"term":"paris"和"term":"food"的子物件的項，而忽略任何位於tags數組之外的這類鍵：

    SELECT doc->'site_name' FROM websites
      WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';

可以用下面的查詢完成同樣的事情：

    SELECT doc->'site_name' FROM websites
      WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';

但是後一種方法靈活性較差，並且常常也效率更低。

在另一方面，JSON 的存在操作符不是嵌套的：它將只在 JSON 值的頂層尋找指定的鍵或數組元素。

jsonb索引

GIN 索引可以被用來有效地搜尋在大量jsonb文檔（資料）中出現的鍵或者索引值對。提供了兩種 GIN “操作符類”，它們在效能和靈活性方面做出了不同的平衡。

jsonb的預設 GIN 操作符類支援使用@>、 ?、?&以及?|操作符的查詢。 使用這種操作符類建立一個索引的例子：

    CREATE INDEX idxgin ON api USING gin (jdoc);

非預設的 GIN 操作符類jsonb_path_ops只支援索引 @>操作符。使用這種操作符類建立一個索引的例子：

    CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

考慮這樣一個例子：一個表格儲存體了從一個第三方 Web 服務檢索到的 JSON 文檔，並且有一個模式定義。一個典型的文檔：

    {
        "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
        "name": "Angela Barton",
        "is_active": true,
        "company": "Magnafone",
        "address": "178 Howard Place, Gulf, Washington, 702",
        "registered": "2009-11-07T08:53:22 +08:00",
        "latitude": 19.793713,
        "longitude": 86.513373,
        "tags": [
            "enim",
            "aliquip",
            "qui"
        ]
    }

我們把這些文檔儲存在一個名為api的表的名為 jdoc的jsonb列中。如果在這個列上建立一個 GIN 索引，下面這樣的查詢就能利用該索引：

    -- 尋找鍵 "company" 有值 "Magnafone" 的文檔
    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

不過，該索引不能被用於下面這樣的查詢，因為儘管操作符? 是可索引的，但它不能直接被應用於被索引列jdoc：

    -- 尋找這樣的文檔：其中的鍵 "tags" 包含鍵或數組元素 "qui"
    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

但是，通過適當地使用運算式索引，上述查詢也能使用一個索引。 如果對"tags"鍵中的特定項的查詢很常見，可能值得定義一個這樣的索引：

    CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));

現在，WHERE 子句 jdoc -> 'tags' ? 'qui' 將被識別為可索引操作符?在索引運算式jdoc -> 'tags' 上的應用。

此外, GIN 索引支援 @@ 和 @?運算子, 以執行 jsonpath 匹配。

    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

GIN 索引從jsonpath中提取如下格式的語句： accessors_chain = const。 存取器鏈可能由.key``[*], 和 [``index``] 存取器組成。 jsonb_ops 此外還支援 . 和 .* 存取器。

另一種查詢的方法是利用包含，例如：

    -- 尋找這樣的文檔：其中鍵 "tags" 包含數組元素 "qui"
    SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

jdoc列上的一個簡單 GIN 索引就能支援這個查詢。 但是注意這樣一個索引將會儲存jdoc列中每一個鍵和值的拷貝，然而前一個例子的運算式索引只儲存tags 鍵下找到的資料。雖然簡單索引的方法更加靈活（因為它支援有關任意鍵的查詢），定向的運算式索引更小並且搜尋速度比簡單索引更快。

儘管jsonb_path_ops操作符類只支援用 @>，@@和@?操作符的查詢，但它比起預設的操作符類 jsonb_ops有更客觀的效能優勢。一個 jsonb_path_ops索引通常也比一個相同資料上的 jsonb_ops要小得多，並且搜尋的專一性更好，特別是當查詢包含頻繁出現在該資料中的鍵時。因此，其上的搜尋操作通常比使用預設操作符類的搜尋表現更好。

jsonb_ops和jsonb_path_ops GIN 索引之間的技術區別是前者為資料中的每一個鍵和值建立獨立的索引項目， 而後者值為該資料中的每個值建立索引項目。基本上，每一個jsonb_path_ops索引項目是其所對應的值和鍵的雜湊。例如要索引{"foo": {"bar": "baz"}}，將建立一個單一的索引項目，它把所有三個foo、bar、 和baz合并到雜湊值中。因此一個尋找這個結構的包含查詢可能導致極度詳細的索引搜尋。但是根本沒有辦法找到foo是否作為一個鍵出現。在另一方面，一個jsonb_ops會建立三個索引項目分別表示foo、bar和baz。那麼要做同樣的包含查詢，它將會尋找包含所有三個項的行。雖然 GIN 索引能夠相當有效地執行這種 AND 搜尋，它仍然不如等效的 jsonb_path_ops搜尋那樣詳細和快速（特別是如果有大量行包含三個索引項目中的任意一個時）。

jsonb_path_ops方法的一個不足是它不會為不包含任何值的 JSON 結構建立索引項目，例如{"a": {}}。如果需要搜尋包含這樣一種結構的文檔，它將要求一次全索引掃描，那就非常慢。 因此jsonb_path_ops不適合經常執行這類搜尋的應用。

jsonb也支援btree和hash索引。 這通常值用於檢查完整 JSON 文檔等值非常重要的場合。jsonb 資料的btree順序很少有人關係，但是為了完整性其順序是：

    對象 > 數組 > 布爾 > 數字 > 字串 > 空值

    帶有 n 對的對象 > 帶有 n - 1 對的對象

    帶有 n 個元素的數組 > 帶有 n - 1 個元素的數組

具有相同數量對的對象這樣比較：

    key-1, value-1, key-2 ...

注意對象鍵被按照它們的儲存順序進行比較，特別是由於較短的鍵被儲存在較長的鍵之前，這可能導致結果不直觀，例如：

    { "aa": 1, "c": 1} > {"b": 1, "d": 1}

相似地，具有相同元素數量的數組按照以下順序比較：

    element-1, element-2 ...

基本 JSON 值的比較會使用低層本資料庫資料類型相同的比較規則進行。字串的比較會使用預設的資料庫定序。

轉換

有一些附加的擴充可以為不同的過程語言實現jsonb類型的轉換。

在這些擴充中，jsonb_plperl被認為是“trusted”， 也就是說，它可以由對當前資料庫具有CREATE許可權的非超級使用者安裝。 其餘的需要超級使用者權限才能安裝。

jsonpath Type

在本資料庫中，jsonpath類型實現支援 SQL/JSON 路徑語言以有效地查詢 JSON 資料。 它提供了已解析的 SQL/JSON 路徑運算式的二進位表示，該運算式指定路徑引擎從 JSON 資料中檢索的項，以便使用 SQL/JSON 查詢函數進行進一步處理。

SQL/JSON 路徑謂詞和運算子的語義通常遵循 SQL。同時，為了提供使用 JSON 資料的自然方法，SQL/JSON 路徑文法使用一些 JavaScript 約定：

• 小數點 (.) 用於成員訪問.

• 方括弧 ([]) 用於數組訪問.

• 與從 1 開始的常規 SQL 數組不同，SQL/JSON 數組是 0 相對的。

SQL/JSON 路徑運算式通常以 SQL 字串文字形式寫入 SQL 查詢中，因此它必須用單引號括起來，並且值中需要的任何單引號都必須是雙引號。 某些形式的路徑運算式需要其中的字串文本。這些嵌入的字串文本遵循 JavaScript/ECMAScript 約定：它們必須用雙引號括起來，並且反斜線轉義可以用於表示其他難以輸入的字元。 特別是，在嵌入字串文本中編寫雙引號的方法為\"，並且要編寫反斜線本身，你必須寫\\。 包括在 JSON 字串中識別的其他特殊的反斜線序列： \b, \f, \n, \r, \t, \v 對於各種 ASCII 控制字元，以及由它的 4 個六位元編碼點標識的Unicode字元\u``NNNN。 反斜線文法還包括 JSON 不允許的兩個案例： \x``NN 對於只用兩個十六進位數字編寫的字元代碼，以及\u{``N...``} 對於用 1 到 6 個十六進位數字編寫的字元代碼。

路徑運算式由一系列路徑元素組成，可以是以下任何一種：

• JSON 基本類型的路徑文字：Unicode 文本、數字、真、假或空.

• 路徑變數。

• 訪問器運算子。

• jsonpath 運算子和方法。

• 括弧，可用於提供篩選器運算式或定義路徑計算的順序。

有關使用jsonpath具有 SQL/JSON 查詢函數的運算式的詳細資料。

jsonpath 變數

jsonpath Accessors