PostgreSQL JSON 類(lèi)型

RFC 7159 中定義了 JSON 數(shù)據(jù)類(lèi)型的輸入/輸出語(yǔ)法。

下列都是合法的json（或者jsonb）表達(dá)式：

-- 簡(jiǎn)單標(biāo)量/基本值
-- 基本值可以是數(shù)字、帶引號(hào)的字符串、true、false或者null
SELECT '5'::json;

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

-- 包含鍵值對(duì)的對(duì)象
-- 注意對(duì)象鍵必須總是帶引號(hào)的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

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

如前所述，當(dāng)一個(gè) JSON 值被輸入并且接著不做任何附加處理就輸出時(shí)， json會(huì)輸出和輸入完全相同的文本，而jsonb 則不會(huì)保留語(yǔ)義上沒(méi)有意義的細(xì)節(jié)（例如空格）。例如，注意下面的不同：

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)

值得一提的一種語(yǔ)義上無(wú)意義的細(xì)節(jié)是，在jsonb中數(shù)據(jù)會(huì)被按照底層 numeric類(lèi)型的行為來(lái)打印。實(shí)際上，這意味著用E記號(hào) 輸入的數(shù)字被打印出來(lái)時(shí)就不會(huì)有該記號(hào)，例如：

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

不過(guò)，如這個(gè)例子所示，jsonb將會(huì)保留拖尾的小數(shù)點(diǎn)后的零，即便這 對(duì)于等值檢查等目的來(lái)說(shuō)是語(yǔ)義上無(wú)意義的。

對(duì)于可用于構(gòu)造和處理 JSON 值的內(nèi)置函數(shù)和運(yùn)算符的列表，參見(jiàn) 第 9.16 節(jié)。

將數(shù)據(jù)表示為 JSON 比傳統(tǒng)關(guān)系數(shù)據(jù)模型要靈活得多，在需求不固定時(shí) 這種優(yōu)勢(shì)更加令人感興趣。在同一個(gè)應(yīng)用里非常有可能有兩種方法共存 并且互補(bǔ)。不過(guò)，即便是在要求最大靈活性的應(yīng)用中，我們還是推薦 JSON 文檔有固定的結(jié)構(gòu)。該結(jié)構(gòu)通常是非強(qiáng)制的（盡管可能會(huì)強(qiáng)制一 些業(yè)務(wù)規(guī)則），但是有一個(gè)可預(yù)測(cè)的結(jié)構(gòu)會(huì)使書(shū)寫(xiě)概括一個(gè)表中的 “文檔”（數(shù)據(jù)）集合的查詢(xún)更容易。

當(dāng)被存儲(chǔ)在表中時(shí)，JSON 數(shù)據(jù)也像其他數(shù)據(jù)類(lèi)型一樣服從相同的并發(fā) 控制考慮。盡管存儲(chǔ)大型文檔是可行的，但是要記住任何更新都在整行 上要求一個(gè)行級(jí)鎖。為了在更新事務(wù)之間減少鎖爭(zhēng)奪，可考慮把 JSON 文檔限制到一個(gè)可管理的尺寸。理想情況下，JSON 文檔應(yīng)該每個(gè)表示 一個(gè)原子數(shù)據(jù)，業(yè)務(wù)規(guī)則命令不會(huì)進(jìn)一步把它們劃分成更小的可獨(dú)立修 改的數(shù)據(jù)。

測(cè)試包含是jsonb的一種重要能力。對(duì) json類(lèi)型沒(méi)有平行的功能集。包含測(cè)試會(huì)測(cè)試一個(gè) jsonb文檔是否被包含在另一個(gè)文檔中。除了特別注解 之外，這些例子都會(huì)返回真：

-- 簡(jiǎn)單的標(biāo)量/基本值只包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

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

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

-- 重復(fù)的數(shù)組元素也沒(méi)有關(guān)系：
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- 右邊具有一個(gè)單一鍵值對(duì)的對(duì)象被包含在左邊的對(duì)象中：
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- 右邊的數(shù)組不會(huì)被認(rèn)為包含在左邊的數(shù)組中，
-- 即使其中嵌入了一個(gè)相似的數(shù)組：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- 得到假

-- 但是如果同樣也有嵌套，包含就成立：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- 類(lèi)似的，這個(gè)例子也不會(huì)被認(rèn)為是包含：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- 得到假

-- 包含一個(gè)頂層鍵和一個(gè)空對(duì)象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

一般原則是被包含的對(duì)象必須在結(jié)構(gòu)和數(shù)據(jù)內(nèi)容上匹配包含對(duì)象，這種匹配 可以是從包含對(duì)象中丟棄了不匹配的數(shù)組元素或者對(duì)象鍵值對(duì)之后成立。但 是記住做包含匹配時(shí)數(shù)組元素的順序是沒(méi)有意義的，并且重復(fù)的數(shù)組元素實(shí) 際也只會(huì)考慮一次。

結(jié)構(gòu)必須匹配的一般原則有一種特殊情況，一個(gè)數(shù)組可以包含一個(gè)基本值：

-- 這個(gè)數(shù)組包含基本字符串值：
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

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

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

-- 字符串作為一個(gè)數(shù)組元素存在：
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- 字符串作為一個(gè)對(duì)象鍵存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

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

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

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

當(dāng)涉及很多鍵或元素時(shí)，JSON 對(duì)象比數(shù)組更適合于做包含或存在測(cè)試， 因?yàn)樗鼈儾幌駭?shù)組，進(jìn)行搜索時(shí)會(huì)進(jìn)行內(nèi)部?jī)?yōu)化，并且不需要被線性搜索。

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"}]';

第 9.16 節(jié)中記錄了多個(gè)包含和存在操作符，以及 所有其他 JSON 操作符和函數(shù)。

GIN 索引可以被用來(lái)有效地搜索在大量jsonb文檔（數(shù)據(jù)）中出現(xiàn) 的鍵或者鍵值對(duì)。提供了兩種 GIN “操作符類(lèi)”，它們?cè)谛阅芎挽`活 性方面做出了不同的平衡。

jsonb的默認(rèn) GIN 操作符類(lèi)支持使用@>、 ?、?&以及?|操作符的查詢(xún)（這些 操作符實(shí)現(xiàn)的詳細(xì)語(yǔ)義請(qǐng)見(jiàn)表 9.45）。 使用這種操作符類(lèi)創(chuàng)建一個(gè)索引的例子：

CREATE INDEX idxgin ON api USING gin (jdoc);

非默認(rèn)的 GIN 操作符類(lèi)jsonb_path_ops只支持索引 @>操作符。使用這種操作符類(lèi)創(chuàng)建一個(gè)索引的例子：

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

考慮這樣一個(gè)例子：一個(gè)表存儲(chǔ)了從一個(gè)第三方 Web 服務(wù)檢索到的 JSON 文檔，并且有一個(gè)模式定義。一個(gè)典型的文檔：

{
    "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"
    ]
}

我們把這些文檔存儲(chǔ)在一個(gè)名為api的表的名為 jdoc的jsonb列中。如果在這個(gè)列上創(chuàng)建一個(gè) GIN 索引，下面這樣的查詢(xún)就能利用該索引：

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

不過(guò)，該索引不能被用于下面這樣的查詢(xún)，因?yàn)楸M管操作符? 是可索引的，但它不能直接被應(yīng)用于被索引列jdoc：

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

但是，通過(guò)適當(dāng)?shù)厥褂帽磉_(dá)式索引，上述查詢(xún)也能使用一個(gè)索引。 如果對(duì)"tags"鍵中的特定項(xiàng)的查詢(xún)很常見(jiàn)，可能值得 定義一個(gè)這樣的索引：

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

現(xiàn)在，WHERE 子句 jdoc -> 'tags' ? 'qui' 將被識(shí)別為可索引操作符?在索引表達(dá)式jdoc -> 'tags' 上的應(yīng)用（更多有關(guān)表達(dá)式索引的信息可見(jiàn)第 11.7 節(jié)）。

此外, GIN 索引支持 @@ 和 @?運(yùn)算符, 以執(zhí)行 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中提取如下格式的語(yǔ)句： accessors_chain = const。 存取器鏈可能由.key[*], 和 [index] 存取器組成。 jsonb_ops 此外還支持 .* 和 .** 存取器。

另一種查詢(xún)的方法是利用包含，例如：

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

jdoc列上的一個(gè)簡(jiǎn)單 GIN 索引就能支持這個(gè)查詢(xún)。 但是注意這樣一個(gè)索引將會(huì)存儲(chǔ)jdoc列中每一個(gè)鍵 和值的拷貝，然而前一個(gè)例子的表達(dá)式索引只存儲(chǔ)tags 鍵下找到的數(shù)據(jù)。雖然簡(jiǎn)單索引的方法更加靈活（因?yàn)樗С钟嘘P(guān)任 意鍵的查詢(xún)），定向的表達(dá)式索引更小并且搜索速度比簡(jiǎn)單索引更快。

盡管jsonb_path_ops操作符類(lèi)只支持用 @>，@@和@?操作符的查詢(xún)，但它比起默認(rèn)的操作符類(lèi) jsonb_ops有更客觀的性能優(yōu)勢(shì)。一個(gè) jsonb_path_ops索引通常也比一個(gè)相同數(shù)據(jù)上的 jsonb_ops要小得多，并且搜索的專(zhuān)一性更好，特 別是當(dāng)查詢(xún)包含頻繁出現(xiàn)在該數(shù)據(jù)中的鍵時(shí)。因此，其上的搜索操作 通常比使用默認(rèn)操作符類(lèi)的搜索表現(xiàn)更好。

jsonb_ops和jsonb_path_ops GIN 索引之間的技術(shù)區(qū)別是前者為數(shù)據(jù)中的每一個(gè)鍵和值創(chuàng)建獨(dú)立的索引項(xiàng)， 而后者值為該數(shù)據(jù)中的每個(gè)值創(chuàng)建索引項(xiàng)。  基本上，每一個(gè)jsonb_path_ops索引項(xiàng)是其所對(duì)應(yīng)的值和 鍵的哈希。例如要索引{"foo": {"bar": "baz"}}，將創(chuàng)建一個(gè) 單一的索引項(xiàng)，它把所有三個(gè)foo、bar、 和baz合并到哈希值中。因此一個(gè)查找這個(gè)結(jié)構(gòu)的包含查詢(xún)可能 導(dǎo)致極度詳細(xì)的索引搜索。但是根本沒(méi)有辦法找到foo是否作為 一個(gè)鍵出現(xiàn)。在另一方面，一個(gè)jsonb_ops會(huì)創(chuàng)建三個(gè)索引 項(xiàng)分別表示foo、bar和baz。那么要 做同樣的包含查詢(xún)，它將會(huì)查找包含所有三個(gè)項(xiàng)的行。雖然 GIN 索引能夠相當(dāng) 有效地執(zhí)行這種 AND 搜索，它仍然不如等效的 jsonb_path_ops搜索那樣詳細(xì)和快速（特別是如果有大量 行包含三個(gè)索引項(xiàng)中的任意一個(gè)時(shí)）。

jsonb_path_ops方法的一個(gè)不足是它不會(huì)為不包含任何值 的 JSON 結(jié)構(gòu)創(chuàng)建索引項(xiàng)，例如{"a": {}}。如果需要搜索包 含這樣一種結(jié)構(gòu)的文檔，它將要求一次全索引掃描，那就非常慢。 因此jsonb_path_ops不適合經(jīng)常執(zhí)行這類(lèi)搜索的應(yīng)用。

jsonb也支持btree和hash索引。 這通常值用于檢查完整 JSON 文檔等值非常重要的場(chǎng)合。jsonb 數(shù)據(jù)的btree順序很少有人關(guān)系，但是為了完整性其順序是：

對(duì)象 > 數(shù)組 > 布爾 > 數(shù)字 > 字符串 > 空值

帶有 n 對(duì)的對(duì)象 > 帶有 n - 1 對(duì)的對(duì)象

帶有 n 個(gè)元素的數(shù)組 > 帶有 n - 1 個(gè)元素的數(shù)組

具有相同數(shù)量對(duì)的對(duì)象這樣比較：

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

注意對(duì)象鍵被按照它們的存儲(chǔ)順序進(jìn)行比較，特別是由于較短的鍵被存儲(chǔ)在 較長(zhǎng)的鍵之前，這可能導(dǎo)致結(jié)果不直觀，例如：

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

相似地，具有相同元素?cái)?shù)量的數(shù)組按照以下順序比較：

element-1, element-2 ...

基本 JSON 值的比較會(huì)使用低層PostgreSQL 數(shù)據(jù)類(lèi)型相同的比較規(guī)則進(jìn)行。字符串的比較會(huì)使用默認(rèn)的數(shù)據(jù)庫(kù)排序規(guī)則。

有一些附加的擴(kuò)展可以為不同的過(guò)程語(yǔ)言實(shí)現(xiàn)jsonb類(lèi)型的轉(zhuǎn)換。

PL/Perl的擴(kuò)展被稱(chēng)作jsonb_plperl和jsonb_plperlu。如果使用它們，jsonb值會(huì)視情況被映射為Perl的數(shù)組、哈希和標(biāo)量。

PL/Python的擴(kuò)展被稱(chēng)作jsonb_plpythonu、jsonb_plpython2u和jsonb_plpython3u（PL/Python命名習(xí)慣請(qǐng)見(jiàn)第 45.1 節(jié)）。如果使用它們，jsonb值會(huì)視情況被映射為Python的詞典、列表和標(biāo)量。

在這些擴(kuò)展中，jsonb_plperl被認(rèn)為是“trusted”， 也就是說(shuō)，它可以由對(duì)當(dāng)前數(shù)據(jù)庫(kù)具有CREATE權(quán)限的非超級(jí)用戶(hù)安裝。 其余的需要超級(jí)用戶(hù)權(quán)限才能安裝。

在PostgreSQL中，jsonpath類(lèi)型實(shí)現(xiàn)支持SQL/JSON 路徑語(yǔ)言以有效地查詢(xún) JSON 數(shù)據(jù)。 它提供了已解析的SQL/JSON路徑表達(dá)式的二進(jìn)制表示，該表達(dá)式指定路徑引擎從JSON數(shù)據(jù)中檢索的項(xiàng)，以便使用SQL/JSON查詢(xún)函數(shù)進(jìn)行進(jìn)一步處理。

SQL/JSON 路徑謂詞和運(yùn)算符的語(yǔ)義通常遵循 SQL。同時(shí)，為了提供使用 JSON 數(shù)據(jù)的自然方法，SQL/JSON 路徑語(yǔ)法使用一些 JavaScript 約定：

SQL/JSON路徑表達(dá)式通常以SQL字符串文字形式寫(xiě)入SQL查詢(xún)中，因此它必須用單引號(hào)括起來(lái)，并且值中需要的任何單引號(hào)都必須是雙引號(hào)(參見(jiàn) 第 4.1.2.1 節(jié))。 某些形式的路徑表達(dá)式需要其中的字符串文本。這些嵌入的字符串文本遵循JavaScript/ECMAScript約定:它們必須用雙引號(hào)括起來(lái)，并且反斜杠轉(zhuǎn)義可以用于表示其他難以輸入的字符。 特別是，在嵌入字符串文本中編寫(xiě)雙引號(hào)的方法為\"，并且要編寫(xiě)反斜杠本身，你必須寫(xiě)\\。 包括在JSON字符串中識(shí)別的其他特殊的反斜杠序列: \b, \f, \n, \r, \t, \v 對(duì)于各種 ASCII 控制字符，以及由它的4個(gè)六位數(shù)編碼點(diǎn)標(biāo)識(shí)標(biāo)識(shí)的 Unicode 字符\uNNNN。 反斜杠語(yǔ)法還包括 JSON 不允許的兩個(gè)案例: \xNN 對(duì)于只用兩個(gè)十六進(jìn)制數(shù)字編寫(xiě)的字符代碼，以及\u{N...} 對(duì)于用 1 到 6 個(gè)十六進(jìn)制數(shù)字編寫(xiě)的字符代碼。

路徑表達(dá)式由一系列路徑元素組成，可以是以下任何一種：

有關(guān)使用jsonpath具有 SQL/JSON 查詢(xún)函數(shù)的表達(dá)式的詳細(xì)信息，參見(jiàn)第 9.16.2 節(jié)。