PostgreSQL 自從支持 JSONB 到現(xiàn)在，已經(jīng)有十余年，這十多年來，社區(qū)為 JSONB 提供了很多強(qiáng)大的功能。就我個(gè)人而言，其實(shí)最常用的還是匹配操作 @> 。

把JSON數(shù)據(jù)看作一個(gè)抽象語法樹（AST）的話，這個(gè)操作符判斷右參數(shù)是不是左參數(shù)的子圖。

這里本來應(yīng)該有個(gè)圖示， 但是周末的時(shí)候臨時(shí)有個(gè)數(shù)據(jù)集在處理，所以沒有時(shí)間去找合適的工具了。簡(jiǎn)單舉幾個(gè)例子，下面這個(gè)例子得到true，這應(yīng)該很好理解：

select '{"a": 1, "b": 2, "c": 3}'::jsonb @> '{"b":2}' ;
--------------
t

而它也可以匹配更復(fù)雜的情況，下面這個(gè)例子也是 true：

select '{"a": 1, "b": 2, "c": {"value": 3}}'::jsonb @> '{"c":{"value": 3}}';
 ?column?
----------
 t
(1 row)

下面這個(gè)例子可能新用戶會(huì)有點(diǎn)兒迷惑，但是其實(shí)也很好的契合了這個(gè)規(guī)則：

select '{"a": 1, "b": 2, "c": {"value": 3}}'::jsonb @> '{"c":{}}';
 ?column?
----------
 t
(1 row)

但是應(yīng)該注意的是，下面這個(gè)例子結(jié)果是 false：

select '{"a": 1, "b": 2, "c": {"value": 3}}'::jsonb @> '{"c":[]}';
 ?column?
----------
 f
(1 row)

這也不難理解，{} 和 [] 不相等。

下面這個(gè)例子比較有意思：

select '{"a": 1, "b": 2, "c": {"value": [1, 2, 3]}}'::jsonb @> '{"c":{"value": [2]}}';
 ?column?
----------
 t
(1 row)

這里要注意的是，比較一個(gè) JSON 數(shù)組是否匹配另一個(gè)時(shí)，它并不要求兩個(gè)數(shù)組的順序相等，只要右邊是左邊的真子集就可以：

select '{"a": 1, "b": 2, "c": {"value": [1, 2, 3]}}'::jsonb @> '{"c":{"value": [2]}}';
 ?column?
----------
 t
(1 row)
 
select '{"a": 1, "b": 2, "c": {"value": [1, 2, 3]}}'::jsonb @> '{"c":{"value": [5, 2]}}';
 ?column?
----------
 f
(1 row)
 
select '{"a": 1, "b": 2, "c": {"value": [1, 2, 3]}}'::jsonb @> '{"c":{"value": [3, 2]}}';
 ?column?
----------
 t
(1 row)

這個(gè)規(guī)則契合了PostgreSQL的倒排索引，PostgreSQL的gin索引，JSONB 字段類型和匹配操作 @> 成為了一個(gè)非常有力的組合。在過去幾年里，我習(xí)慣為一些重要的業(yè)務(wù)表加上一個(gè)類型為 JSONB 的meta 字段，并對(duì)其建立 gin 索引

create index idx_xxx_meta on xxx using(gin);

需要注意的是指定索引類型時(shí)的 create index 語法。

這樣的設(shè)計(jì)可以解決很多傳統(tǒng)上難以解決的問題，例如我可以給每個(gè)條目打上一個(gè) tag 列表，取帶有某幾個(gè) tag 的條目就是一個(gè)簡(jiǎn)單的匹配查詢：

select xxx from data_table where meta @> '{"tags": ["tag1", "tagx", "tagy"]}'

因?yàn)橛術(shù)in索引的幫助，這個(gè)搜索的性能足夠常規(guī)的互聯(lián)網(wǎng)應(yīng)用所需。

甚至我的在 CSDN NLP 組的同事還挖掘出了新的用法。我們?cè)谝粋€(gè)存儲(chǔ)樹節(jié)點(diǎn)的表里，保存了一個(gè) meta 字段，其中有一個(gè) path 列表，存儲(chǔ)當(dāng)前字段在樹中的路徑，它的每一項(xiàng)都是 {"id": node_id, "title": something}這樣的結(jié)構(gòu)，而我們搜索某一個(gè)節(jié)點(diǎn)下面的所有子節(jié)點(diǎn)，包括其隔代的子節(jié)點(diǎn)時(shí)，僅需要執(zhí)行這樣一個(gè)查詢：

select xxx from tree_node where meta @> '{"path": [{"id": node_id}]}'

當(dāng)然這個(gè)匹配操作也有它的限制，它在右邊是左邊的真子圖的情況下才會(huì)匹配成功。例如我希望查找 tags 列表中包含我搜索項(xiàng)中的任何一個(gè)（即兩者存在非空交集）的情況，用這種方法就不行了。此時(shí)我們需要另一個(gè)運(yùn)算符 ?|

select '["tag1", "tag2", "tag3"]'::jsonb ?| '{tag2, tag3}';
 ?column?
----------
 t
(1 row)
 
select '["tag1", "tag2", "tag3"]'::jsonb ?| '{tag2, tag3, tag5}';
 ?column?
----------
 t
(1 row)
 
select '["tag1", "tag2", "tag3"]'::jsonb ?| '{tag5}';
 ?column?
----------
 f
(1 row)

注意這幾個(gè)例子，首先右邊的運(yùn)算符不再是jsonb，而必須是 text[]，其次它其實(shí)是檢查 key 值——也就是可以通過 gin 索引存儲(chǔ)的值：

select '{"tag1":1, "tag2":2, "tag3":3}'::jsonb ?| '{tag5}';
 ?column?
----------
 f
(1 row)
 
select '{"tag1":1, "tag2":2, "tag3":3}'::jsonb ?| '{tag3}';
 ?column?
----------
 t
(1 row)
 
select '{"tag1":1, "tag2":2, "tag3":3}'::jsonb ?| '{tag3, tag1}';
 ?column?
----------
 t
(1 row)

PostgreSQL 支持 JSON 和 JSONB 已經(jīng)有十余年，每一個(gè)版本都在積極的增強(qiáng)其 JSON 數(shù)據(jù)處理能力，即使我近十年來的積極探索和學(xué)習(xí)，也沒有全面的了解。這個(gè)交集運(yùn)算也是近期在 NLP 組的工作過程中才注意到的。

到此這篇關(guān)于PostgreSQL JSONB的匹配和交集的文章就介紹到這了,更多相關(guān)PostgreSQL JSONB內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論