This is a document for PGroonga 2.X and 3.X. See PGroonga 1.x document when you're using old PGroonga.
&@ operatorSince 1.2.0.
&@ operator performs full text search by one keyword.
There are three signatures:
column &@ keyword
column &@ (keyword, weights, index_name)::pgroonga_full_text_search_condition
column &@ (keyword, weights, scorers, index_name)::pgroonga_full_text_search_condition_with_scorers
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.
Here is the description of the first signature.
column &@ keyword
column is a column to be searched. It's text type, text[] type or varchar type.
keyword is a keyword for full text search. It's text type for text type or text[] type column. It's varchar type for varchar type column.
Here is the description of the second signature.
column &@ (keyword, weights, index_name)::pgroonga_full_text_search_condition
column is a column to be searched. It's text type, text[] type or varchar type.
keyword is a keyword 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 &@ (keyword, weights, scorers, index_name)::pgroonga_full_text_search_condition
column is a column to be searched. It's text type, text[] type or varchar type.
keyword is a keyword 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.
You need to specify one of the following operator classes to use this operator:
pgroonga_text_full_text_search_ops_v2: Default for text
pgroonga_text_array_full_text_search_ops_v2: Default for text[]
pgroonga_varchar_full_text_search_ops_v2: For varchar
pgroonga_text_full_text_search_ops: For text
pgroonga_text_array_full_text_search_ops: For text[]
pgroonga_varchar_full_text_search_ops: For varchar
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 one keyword by &@ operator:
SELECT * FROM memos WHERE content &@ 'engine';
-- id | content
-- ----+------------------------------------------------------------------------
-- 2 | Groonga is a fast full text search engine that supports all languages.
-- (1 row)
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 "Groonga" word with pgroonga_score function:
SELECT *, pgroonga_score(tableoid, ctid) AS score
FROM memos
WHERE ARRAY[title, content] &@
('Groonga',
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
-- PGroonga | PGroonga is a PostgreSQL extension that uses Groonga as index. | 1
-- CLI | There is groonga command. | 1
-- -- (3 rows)
You can confirm that the record which has "Groonga" in title column has more high score than "Groonga" 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',
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
-- (1 row)
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',
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
-- PGroonga | PGroonga is a PostgreSQL extension that uses Groonga as index. | 0.5
-- CLI | There is groonga command. | 0.5
-- (3 rows)
If you want to perform full text search with multiple keywords or AND/OR search, use &@~ operator.
If you want to perform full text search with multiple keywords OR search, use &@| operator.
&@~ operator: Full text search by easy to use query language
&@| operator: Full text search by an array of keywords