Querying¶

AkasicDB text search searches indexed text with the @@ search operator and ranks relevance with BM25 scores. The right operand cannot be a bare string; it must be wrapped in akasicdb.text_query(...) or a query DSL builder function.

Note

The examples on this page assume docs_texton_idx built with the mapping from the Fast Fields section of Indexing: highlighting needs stored on body, and aggregation needs the title.raw keyword sub-field. If you ran the examples in Tokenization, they replaced that index under the same name — re-run the CREATE INDEX from the Fast Fields section.

The `@@` Search Operator¶

@@ takes a whole-row left operand and a texton.query right operand. akasicdb.text_query(text) builds a query value from a parser-syntax string.

SELECT * FROM docs
 WHERE docs @@ akasicdb.text_query('공항');

Fields mapped with lindera ko-dic are indexed and searched with Korean morphological analysis. 국제공항에서 splits into 국제 / 공항 / 에서, so the query above also finds rows where 공항 sits inside a larger word. Note that a field with no analyzer in its mapping is indexed with standard (see Tokenization).

Query DSL¶

Compose structured queries with the DSL builder functions that produce a texton.query. Builders can be nested within one another.

SELECT * FROM docs
 WHERE docs @@ akasicdb.text_bool(
   must     => ARRAY[ akasicdb.text_match('body', '공항') ],
   should   => ARRAY[ akasicdb.text_match_phrase('title', '도쿄 여행', 1) ],
   must_not => ARRAY[ akasicdb.text_term('title', '광고') ]
 );

Builder	Use
`akasicdb.text_match(field, text)`	Token match over analyzer-processed tokens.
`akasicdb.text_match_phrase(field, text, slop)`	Phrase match. `slop` (default `0`) sets the allowed gap between tokens.
`akasicdb.text_term(field, value)`	Exact, unanalyzed term match.
`akasicdb.text_prefix(field, text)`	Prefix match.
`akasicdb.text_wildcard(field, pattern)`	Wildcard pattern match.
`akasicdb.text_fuzzy(field, text, distance)`	Edit-distance fuzzy match. `distance` defaults to `1` (max `2`).
`akasicdb.text_range(field, lower, upper, include_lower, include_upper)`	Range match (int / float / timestamptz). Bounds default to `include_lower=true`, `include_upper=false`.
`akasicdb.text_phrase_prefix(field, text, max_expansions)`	Phrase-prefix match for search-as-you-type. `max_expansions` defaults to `50`.
`akasicdb.text_bool(must, should, must_not, minimum_should)`	Combines `must` / `should` / `must_not` clauses. `minimum_should` sets the minimum number of `should` clauses that must match.
`akasicdb.text_boost(query, factor)`	Adjusts the score weight of a subquery.

The clause arrays passed to text_bool are texton.query[]; every builder above returns a texton.query, so builders compose directly.

BM25 Score¶

akasicdb.text_score(t) returns the BM25 score of the current row within a @@ search scan. Calling it outside a search scan raises an ERROR.

SELECT t.title, akasicdb.text_score(t) AS score
  FROM docs t
 WHERE t @@ akasicdb.text_query('여행')
 ORDER BY akasicdb.text_score(t) DESC;

Sorting & Cursor Pagination¶

For stable sorting of large result sets and deep pagination, use the akasicdb.text_search() SRF. Paginate with sort keys (score and/or fast fields) and an after cursor (the cursor of the last row on the previous page). text_search() returns ctid, so join it back to the source table on ctid. The default limit is 10.

SELECT d.title, s.rank, s.cursor
  FROM akasicdb.text_search('docs_texton_idx', akasicdb.text_match('body', '공항'),
                     sort => ARRAY['score desc'], "limit" => 20) AS s
  JOIN docs d ON d.ctid = s.ctid
 ORDER BY s.rank;

For the next page, pass the cursor value of the last row on the previous page as after.

SELECT d.title, s.rank, s.cursor
  FROM akasicdb.text_search('docs_texton_idx', akasicdb.text_match('body', '공항'),
                     sort => ARRAY['score desc'], "limit" => 20,
                     after => '<last cursor of previous page>') AS s
  JOIN docs d ON d.ctid = s.ctid
 ORDER BY s.rank;

A sort key must be declared fast in the mapping and must also be an integer, float, bool, or date field. text and keyword fields cannot be sort keys even when declared fast (see Indexing).

Highlighting¶

akasicdb.text_highlight() returns match snippet fragments from a search-scan row as text[]. Use field for the target field, and max_fragments and fragment_chars to tune the number and length of fragments. The target field must be declared stored in the mapping (see Indexing).

SELECT akasicdb.text_highlight(t, akasicdb.text_query('공항'), field => 'body',
                        max_fragments => 3, fragment_chars => 150)
  FROM docs t
 WHERE t @@ akasicdb.text_query('공항');

Aggregation¶

akasicdb.text_aggregate() runs ElasticSearch (ES)-style aggregations over the document set filtered by a query. Both the request and the response are jsonb. Aggregation target fields must be fast in the mapping.

A terms aggregation must target an unanalyzed field. The example below aggregates the title.raw keyword sub-field declared in Indexing; targeting the analyzed title field instead would count morphemes rather than titles.

SELECT akasicdb.text_aggregate('docs_texton_idx', akasicdb.text_match('body', '공항'),
  '{"by_title":{"terms":{"field":"title.raw","size":10}}}'::jsonb);