快捷導(dǎo)航

關(guān)于PostgreSQL JSONB的匹配和交集問題

更新時間：2021年09月13日 15:46:37 作者：ccat

這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下

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

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

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

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

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

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

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

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

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

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

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

下面這個例子比較有意思：

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

這里要注意的是，比較一個 JSON 數(shù)組是否匹配另一個時，它并不要求兩個數(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)

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

create index idx_xxx_meta on xxx using(gin);

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

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

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

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

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

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

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

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)

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

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)有十余年，每一個版本都在積極的增強其 JSON 數(shù)據(jù)處理能力，即使我近十年來的積極探索和學(xué)習(xí)，也沒有全面的了解。這個交集運算也是近期在 NLP 組的工作過程中才注意到的。

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

您可能感興趣的文章: