@@ operator for jsonb type

Summary

@@ operator is a PGroonga original operator. You can use complex condition that can't be written by @> operator such as range search.

If you know JsQuery, you can understand like "PGroonga provides jsonb type related search features that are similar to JsQuery with different syntax".

Syntax

Here is the syntax of this operator:

jsonb_column @@ condition

jsonb_column is a column that its type is jsonb.

condition is a text value used as query. It uses Groonga's script syntax.

The operator returns true when condition matches jsonb_column value, false otherwise.

Usage

Here are sample schema and data for examples:

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

Disable sequential scan:

SET enable_seqscan = off;

You need to understand how PGroonga creates index against jsonb type value to create search condition.

PGroonga splits a jsonb type value into values and then creates indexes against these values. In SQL, think about the following schema:

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

Here are descriptions of column:

Here is a sample JSON:

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

The JSON is split to the following values. (They are part of all split values.)

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  

You specify condition that matches split value to @@ operator. If there is one or more split values that match specified condition in jsonb type value, the jsonb type value is matched.

Here is a condition that searches jsonb type value that has www.example.com string:

(It uses jsonb_pretty() function provided since PostgreSQL 9.5 for readability.)

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)

Here is a condition that searches jsonb type value that has number between 200 to 299 as code column value. The condition uses paths @ "..." syntax to use simple path format (.code) to specify path.

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)

Here is a condition for full text search from all text values in jsonb value type:

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)

You can use Groonga's query syntax (a OR b can be used) for full text search by query("string", "...") syntax:

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)

See also