これはPGroonga 1.X用のドキュメントです。新しいPGroongaを使っているならPGroonga 2.xのドキュメントを見てください。

jsonb型用の&`演算子

1.2.1で追加。

概要

&`演算子はPGroonga独自の演算子です。@>演算子では書けない範囲検索のような複雑な条件を書くことができます。

もしJsQueryを知っているなら、「PGroongaはJsQueryが提供しているようなjsonb型用の検索機能を違う構文で提供している」と理解してください。

構文

この演算子の構文は次の通りです。

jsonb_column &` condition

jsonb_columnはjsonb型のカラムです。

conditionはクエリーとして使うtext型の値です。Groongaのスクリプト構文を使います。

この演算子はconditionがjsonb_columnの値にマッチしたらtrueを返し、マッチしなかったらfalseを返します。

演算子クラス

この演算子を使うには次のどれかの演算子クラスを指定する必要があります。

使い方

例に使うサンプルスキーマとデータは次の通りです。

CREATE TABLE logs (
  record jsonb
);

CREATE INDEX pgroonga_logs_index ON logs USING pgroonga (record);

INSERT INTO logs
     VALUES ('{
                "message": "Server is started.",
                "host":    "www.example.com",
                "tags": [
                  "web",
                  "example.com"
                ]
              }');
INSERT INTO logs
     VALUES ('{
                "message": "GET /",
                "host":    "www.example.com",
                "code":    200,
                "tags": [
                  "web",
                  "example.com"
                ]
              }');
INSERT INTO logs
     VALUES ('{
                "message": "Send to <info@example.com>.",
                "host":    "mail.example.net",
                "tags": [
                  "mail",
                  "example.net"
                ]
              }');

シーケンシャルスキャンを無効にします。

SET enable_seqscan = off;

検索条件を作るためにはPGroongaがjsonb型のデータ用のインデックスをどのように作るかを理解する必要があります。

PGroongaはjsonb型の値を複数の値に分割し、それらの分割した値に対してインデックスを作成します。SQLで言うと次のスキーマを作っていると考えてください。

CREATE TABLE values (
  key text PRIMARY KEY,
  path text,
  paths text[],
  type text,
  boolean boolean,
  number double precision,
  string text,
  size numeric
);

各カラムの説明は次の通りです。

以下はサンプルJSONです。

{
  "message": "GET /",
  "host":    "www.example.com",
  "code":    200,
  "tags": [
    "web",
    "example.com"
  ]
}

このJSONを次の値に分割します。（これは分割した値の一部です。）

key path paths type boolean number string size
.|object . [.] object        
.["message"]|string|GET / .["message"] [.message, .["message"], message, ["message"]] string     GET /  
.["tags"][]|string|web .["tags"] [.tags, .["tags"], .["tags"][], tags, ["tags"], ["tags"][], []] string     web  

&`演算子を使って、分割した値にマッチする条件を指定します。もし、jsonb型の値の中に条件にマッチする分割した値が1つ以上ある場合はそのjsonb型の値はマッチしたことになります。

次はwww.example.comという文字列値を含むjsonb型の値を検索する条件です。

（読みやすくするためにPostgreSQL 9.5以降で使えるjsonb_pretty()関数を使っています。）

SELECT jsonb_pretty(record) FROM logs WHERE record &` 'string == "www.example.com"';
--             jsonb_pretty             
-- -------------------------------------
--  {                                  +
--      "host": "www.example.com",     +
--      "tags": [                      +
--          "web",                     +
--          "example.com"              +
--      ],                             +
--      "message": "Server is started."+
--  }
--  {                                  +
--      "code": 200,                   +
--      "host": "www.example.com",     +
--      "tags": [                      +
--          "web",                     +
--          "example.com"              +
--      ],                             +
--      "message": "GET /"             +
--  }
-- (2 rows)

以下はcodeカラムの値として200から299の間の数値を持っているjsonb型の値を検索する条件です。この条件はパスの指定に簡易パスフォーマット（.code）を使うため、paths @ "..."という構文を使っています。

SELECT jsonb_pretty(record) FROM logs WHERE record &` 'paths @ ".code" && number >= 200 && number < 300';
--           jsonb_pretty          
-- --------------------------------
--  {                             +
--      "code": 200,              +
--      "host": "www.example.com",+
--      "tags": [                 +
--          "web",                +
--          "example.com"         +
--      ],                        +
--      "message": "GET /"        +
--  }
-- (1 row)

jsonb型の値の中のすべてのテキスト値に対して全文検索をする条件は次の通りです。

SELECT jsonb_pretty(record) FROM logs WHERE record &` 'string @ "started"';
--             jsonb_pretty             
-- -------------------------------------
--  {                                  +
--      "host": "www.example.com",     +
--      "tags": [                      +
--          "web",                     +
--          "example.com"              +
--      ],                             +
--      "message": "Server is started."+
--  }
-- (1 row)

全文検索用にGroongaのクエリー構文（a OR bという構文を使えます）を使うにはquery("string", "...")という構文を使います。

SELECT jsonb_pretty(record) FROM logs WHERE record &` 'query("string", "send OR server")';
--                  jsonb_pretty                 
-- ----------------------------------------------
--  {                                           +
--      "host": "www.example.com",              +
--      "tags": [                               +
--          "web",                              +
--          "example.com"                       +
--      ],                                      +
--      "message": "Server is started."         +
--  }
--  {                                           +
--      "host": "mail.example.net",             +
--      "tags": [                               +
--          "mail",                             +
--          "example.net"                       +
--      ],                                      +
--      "message": "Send to <info@example.com>."+
--  }
-- (2 rows)

参考