This is a document for PGroonga 2.X and 3.X. See PGroonga 1.x document when you're using old PGroonga.

&@~ operator for non jsonb types

Since 1.2.2.

&? operator is deprecated since 1.2.2. Use &@~ operator instead.

Summary

&@~ operator performs full text search with query.

Query's syntax is similar to syntax that is used in Web search engine. For example, you can use OR search by KEYWORD1 OR KEYWORD2 in query, AND search by KEYWORD1 KEYWORD2 in query and NOT search by KEYWORD1 -KEYWORD2 in query.

Syntax

There are three signatures:

column &@~ query
column &@~ (query, weights, index_name)::pgroonga_full_text_search_condition
column &@~ (query, weights, scorers, index_name)::pgroonga_full_text_search_condition_with_scorers
column &@~ pgroonga_condition(query,
                              weights,
                              scorers,
                              schema_name,
                              index_name,
                              column_name,
                              fuzzy_max_distance_ratio)

The first signature is simpler than others. The first signature is enough for most cases.

The second signature is useful to optimize search score. For example, you can implement "title is more important than content" for blog application.

The second signature is available since 2.0.4.

The third signature is useful to optimize more search score. For example, you can take measures against keyword stuffing.

The third signature is available since 2.0.6.

The fourth signature uses pgroonga_condition function. You can implement the second and third signatures with pgroonga_condition function. You should use this signature with 3.1.6 or later.

See pgroonga_condition function for details.

The fourth signature is available since 3.1.6.

Here is the description of the first signature.

column &@~ query

column is a column to be searched. It's text type, text[] type or varchar type.

query is a query for full text search. It's text type for text type or text[] type column. It's varchar type for varchar type column.

Groonga's query syntax is used in query.

Here is the description of the second signature.

column &@~ (query, weights, index_name)::pgroonga_full_text_search_condition

column is a column to be searched. It's text type, text[] type or varchar type.

query is a query for full text search. It's text type for text type or text[] type column. It's varchar type for varchar type column.

weights is importance factors of each value. It's int[] type.

If column is text type or varchar type, the first element is used for importance factor of the value. If column is text[] type, the same position value is used as importance factor.

weights can be NULL. Elements of weights can also be NULL. If the corresponding importance factor is NULL, the importance factor is 1.

If importance factor is 0, the value is ignored. For example, ARRAY[1, 0, 1] means the second value isn't search target.

index_name is an index name of the corresponding PGroonga index. It's text type.

index_name can be NULL.

It's for using the same search options specified in PGroonga index in sequential search.

It's available since 2.0.6.

Here is the description of the third signature.

column &@~ (query, weights, scorers, index_name)::pgroonga_full_text_search_condition_with_scorers

column is a column to be searched. It's text type, text[] type or varchar type.

query is a query for full text search. It's text type for text type or text[] type column. It's varchar type for varchar type column.

weights is importance factors of each value. It's int[] type.

If column is text type or varchar type, the first element is used for importance factor of the value. If column is text[] type, the same position value is used as importance factor.

weights can be NULL. Elements of weights can also be NULL. If the corresponding importance factor is NULL, the importance factor is 1.

If importance factor is 0, the value is ignored. For example, ARRAY[1, 0, 1] means the second value isn't search target.

scorers is score compute procedures of each value. It's text[] type. If column is text type or varchar type, the first element is used to compute score for the value. If column is text[] type, the same position value is used to compute score for the value.

scorers can be NULL. Elements of scorers can also be NULL. If the corresponding scorerer is NULL, the scorer is the term count scorer.

See scorer document in Groonga for scorer details.

Note that you must specify $index for the first scorer argument.

Example:

'scorer_tf_at_most($index, 0.25)'

It's replaced with the correct Groonga index name internally.

index_name is an index name of the corresponding PGroonga index. It's text type.

index_name can be NULL.

It's for using the same search options specified in PGroonga index in sequential search.

It's available since 2.0.6.

Groonga's query syntax is used in query.

Operator classes

You need to specify one of the following operator classes to use this operator:

Usage

Here are sample schema and data for examples:

CREATE TABLE memos (
  id integer,
  content text
);

CREATE INDEX pgroonga_content_index ON memos USING pgroonga (content);

INSERT INTO memos VALUES (1, 'PostgreSQL is a relational database management system.');
INSERT INTO memos VALUES (2, 'Groonga is a fast full text search engine that supports all languages.');
INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga as index.');
INSERT INTO memos VALUES (4, 'There is groonga command.');

You can perform full text search with multiple keywords by &@~ operator like KEYWORD1 KEYWORD2. You can also do OR search by KEYWORD1 OR KEYWORD2:

SELECT * FROM memos WHERE content &@~ 'PGroonga OR PostgreSQL';
--  id |                            content                             
-- ----+----------------------------------------------------------------
--   3 | PGroonga is a PostgreSQL extension that uses Groonga as index.
--   1 | PostgreSQL is a relational database management system.
-- (2 rows)

You can also implement "title is more important than content".

Here are sample schema and data for examples:

DROP TABLE IF EXISTS memos;
CREATE TABLE memos (
  title text,
  content text
);

CREATE INDEX pgroonga_memos_index
    ON memos
 USING pgroonga ((ARRAY[title, content]));

INSERT INTO memos VALUES ('PostgreSQL', 'PostgreSQL is a relational database management system.');
INSERT INTO memos VALUES ('Groonga', 'Groonga is a fast full text search engine that supports all languages.');
INSERT INTO memos VALUES ('PGroonga', 'PGroonga is a PostgreSQL extension that uses Groonga as index.');
INSERT INTO memos VALUES ('CLI', 'There is groonga command.');

You can find more suitable records against the given query with pgroonga_score function:

SELECT *, pgroonga_score(tableoid, ctid) AS score
  FROM memos
 WHERE ARRAY[title, content] &@~
       ('Groonga OR PostgreSQL',
        ARRAY[5, 1],
        'pgroonga_memos_index')::pgroonga_full_text_search_condition
 ORDER BY score DESC;
--    title    |                                content                                 | score 
-- ------------+------------------------------------------------------------------------+-------
--  Groonga    | Groonga is a fast full text search engine that supports all languages. |     6
--  PostgreSQL | PostgreSQL is a relational database management system.                 |     6
--  PGroonga   | PGroonga is a PostgreSQL extension that uses Groonga as index.         |     2
--  CLI        | There is groonga command.                                              |     1
-- (4 rows)

-- Query with pgroonga_condition().
SELECT *, pgroonga_score(tableoid, ctid) AS score
  FROM memos
 WHERE ARRAY[title, content] &@~
       pgroonga_condition('Groonga OR PostgreSQL',
                          weights => ARRAY[5, 1],
                          index_name => 'pgroonga_memos_index')
 ORDER BY score DESC;
--    title    |                                content                                 | score
-- ------------+------------------------------------------------------------------------+-------
--  Groonga    | Groonga is a fast full text search engine that supports all languages. |     6
--  PostgreSQL | PostgreSQL is a relational database management system.                 |     6
--  PGroonga   | PGroonga is a PostgreSQL extension that uses Groonga as index.         |     2
--  CLI        | There is groonga command.                                              |     1
-- (4 rows)

You can confirm that the record which has "Groonga" or "PostgreSQL" in title column has more high score than "Groonga" or "PostgreSQL" in content column.

You can ignore content column data by specifying 0 as the second weight value:

SELECT *, pgroonga_score(tableoid, ctid) AS score
  FROM memos
 WHERE ARRAY[title, content] &@~
       ('Groonga OR PostgreSQL',
        ARRAY[5, 0],
        'pgroonga_memos_index')::pgroonga_full_text_search_condition
 ORDER BY score DESC;
--    title    |                                content                                 | score 
-- ------------+------------------------------------------------------------------------+-------
--  Groonga    | Groonga is a fast full text search engine that supports all languages. |     5
--  PostgreSQL | PostgreSQL is a relational database management system.                 |     5
-- (2 rows)

-- Query with pgroonga_condition().
SELECT *, pgroonga_score(tableoid, ctid) AS score
  FROM memos
 WHERE ARRAY[title, content] &@~
       pgroonga_condition('Groonga OR PostgreSQL',
                          weights => ARRAY[5, 0],
                          index_name => 'pgroonga_memos_index')
 ORDER BY score DESC;
--    title    |                                content                                 | score 
-- ------------+------------------------------------------------------------------------+-------
--  Groonga    | Groonga is a fast full text search engine that supports all languages. |     5
--  PostgreSQL | PostgreSQL is a relational database management system.                 |     5
-- (2 rows)

You can customize how to compute score. For example, you can limit the score of content column to 0.5.

SELECT *, pgroonga_score(tableoid, ctid) AS score
  FROM memos
 WHERE ARRAY[title, content] &@~
       ('Groonga OR PostgreSQL',
        ARRAY[5, 1],
        ARRAY[NULL, 'scorer_tf_at_most($index, 0.5)'],
        'pgroonga_memos_index')::pgroonga_full_text_search_condition_with_scorers
 ORDER BY score DESC;
--    title    |                                content                                 | score 
-- ------------+------------------------------------------------------------------------+-------
--  Groonga    | Groonga is a fast full text search engine that supports all languages. |   5.5
--  PostgreSQL | PostgreSQL is a relational database management system.                 |   5.5
--  PGroonga   | PGroonga is a PostgreSQL extension that uses Groonga as index.         |     1
--  CLI        | There is groonga command.                                              |   0.5
-- (4 rows)

-- Query with pgroonga_condition().
SELECT *, pgroonga_score(tableoid, ctid) AS score
  FROM memos
 WHERE ARRAY[title, content] &@~
       pgroonga_condition('Groonga OR PostgreSQL',
                          weights => ARRAY[5, 1],
                          scorers => ARRAY[NULL, 'scorer_tf_at_most($index, 0.5)'],
                          index_name => 'pgroonga_memos_index')
 ORDER BY score DESC;
--    title    |                                content                                 | score 
-- ------------+------------------------------------------------------------------------+-------
--  Groonga    | Groonga is a fast full text search engine that supports all languages. |   5.5
--  PostgreSQL | PostgreSQL is a relational database management system.                 |   5.5
--  PGroonga   | PGroonga is a PostgreSQL extension that uses Groonga as index.         |     1
--  CLI        | There is groonga command.                                              |   0.5
-- (4 rows)

See Groonga document for query syntax details.

Note that you can't use syntax that starts with COLUMN_NAME: like COLUMN_NAME:@KEYWORD. It's disabled in PGroonga.

You can't use COLUMN_NAME:^VALUE for prefix search. You need to use VALUE* for prefix search.

See also