PostgreSQLのJSONBのマッチングと交差の問題について

PostgreSQLがJSONBをサポートし始めてから10年以上になりますが、この10年ほどの間に、コミュニティはJSONBに対して多くの強力な機能を提供してきました。個人的には、今でもmatchオペレーションを最もよく使っています  @>  .

JSONデータを抽象構文木（AST）と考えると、この演算子は右引数が左引数の部分グラフであるかどうかを判断する。

本来ならここにグラフがあるはずなのですが、週末に一時的なデータセットがあったので、適切なツールを探す時間がなかったのです。いくつかの簡単な例として、次の例は真になる、これは理解しやすいはずだ。

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

また、次の例のように、より複雑なケースにもマッチすることができ、これもまた正しい。

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

次の例は、初めての人には少しわかりにくいかもしれませんが、実はこのルールにとてもよく合っています。

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

ただし、以下の例では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配列と別のJSON配列を比較する場合、右側が左側の真のサブセットであれば、2つの配列が同じ順番である必要はないことに注意してください。

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)

このルールは、PostgreSQLの転置インデックス、PostgreSQLのジンインデックス、JSONBフィールド型、マッチ演算@>を非常に強力な組み合わせにするものです。ここ数年、私はいくつかの重要なビジネステーブルにJSONB型のメタフィールドを追加し、それらにジンインデックスを作成するために使用していました。

create index idx_xxx_meta on xxx using(gin);

インデックスの種類を指定する際の create index 構文に注意してください。

例えば、各エントリーにタグのリストを付けておけば、特定のタグを持つエントリーを取り出すのは簡単なマッチングクエリーになります。

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

ジン・インデックスの助けを借りているので、この検索のパフォーマンスは通常のインターネット・アプリケーションでは十分なものです。

CSDN NLPグループの仲間も、新しい使い方を掘り起こしてくれました。ツリーノードを格納するテーブルにメタフィールドを保持し、ツリー内の現在のフィールドのパスを格納するパスリストがあり、その各項目は  {"id": node_id, "title": something} このような構造で、次のようなクエリを1回実行するだけで、あるノードの下のすべての子ノードを、その代替子も含めて検索するのです。

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

もちろん、このマッチ操作には制限があり、右辺が左辺の真の部分グラフである場合にのみマッチする。たとえば、検索している項目のいずれかを含むタグのリストを見つけたい場合（つまり、2つの間に空でない交差点がある場合）、この方法は機能しません。この場合、別の演算子が必要です 

? |
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)

text[]

これらの例では、まず右側の演算子がjsonbでなくなっており、必ず  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) そして第二に、実際にキーとなる値、つまりジンインデックスに格納できる値をチェックします。

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 は 10 年以上前から JSON と JSONB をサポートしており、各バージョンで JSON データ処理機能を積極的に強化してきましたが、10 年近く積極的に探求し学習しても、完全には理解できていません。この交差点演算も、NLPグループでの仕事の中で、つい最近、注目するようになったものです。

PostgreSQLのJSONBマッチングと交差に関する記事は、このような内容です。PostgreSQL JSONB の関連コンテンツは、過去の記事を検索するか、以下の関連記事を引き続きご覧ください。