Use whpg-cjk to search Korean, Japanese, and Chinese text with the standard WarehousePG full text search features. With whpg-cjk, you can:
- Index and search text in all three languages.
- Rank results, build a search box, search for phrases, and highlight matches.
- Set a default language to omit the configuration name from your queries.
- Search JSON documents.
- Search in more than one language in the same database.
- Inspect how text is segmented to find out why a search matches or doesn't.
- Add your own words to the dictionaries.
Getting started
Install and register whpg-cjk in a database with UTF8 encoding before you start. See Installing whpg-cjk.
Then, identify the data that you want to search. Either create a table and load your data, or locate an existing table with a text column that contains Korean, Japanese, or Chinese text. The syntax in the following sections uses <table_name> and <column_name> as placeholders for your own table and column.
To follow along with sample data instead, create a small table of Korean book titles. In WarehousePG, include the DISTRIBUTED BY clause:
CREATE TABLE books ( id int, title text, body text ) DISTRIBUTED BY (id); INSERT INTO books VALUES (1, '무궁화 꽃이 피었습니다', '조정래 장편소설. 한반도의 운명을 다룬 이야기.'), (2, '데이터베이스 개론', '분산 데이터베이스 시스템의 원리와 실제를 다룬다'), (3, '한국 현대 문학사', '근대부터 현대까지 한국 문학의 흐름을 정리했다');
Indexing and searching text
Search a text column by creating an index on it, then querying it. Full text search converts text into a tsvector, a list of searchable terms that WarehousePG indexes and matches against. The index is a Generalized Inverted Index (GIN), and queries are matched against it using the @@ operator. whpg-cjk works with the standard PostgreSQL full text search functions, such as to_tsvector, to_tsquery, and ts_rank. See the Function reference for the full list.
To segment the text, specify a whpg-cjk text search configuration as the first argument of the text search functions, such as to_tsvector and to_tsquery, the same way you use a built-in configuration such as english. whpg-cjk provides one configuration for each language:
| Configuration | Language | Dictionary |
|---|---|---|
korean | Korean | mecab-ko-dic |
japanese | Japanese | IPADIC |
chinese | Chinese, simplified | jieba |
The dictionaries are embedded in the extension, so there are no dictionary files to deploy. The analyzers reduce words to their base forms. Korean drops particles and endings and keeps the stem, Japanese folds conjugated forms to the dictionary form, and Chinese indexes each word together with its sub-words, so a query for 文学 matches a document that contains 现代文学. Documents and queries are segmented the same way.
Create the index. In the index expression, use the text search configuration that matches the language of your data, which is
korean,japanese, orchinese:CREATE INDEX <index_name> ON <table_name> USING gin (to_tsvector('<config>', <column_name>));
Where
<index_name>is a name for the index,<table_name>is your table,<config>is the text search configuration for the language of your data, eitherkorean,japanese, orchinese, and<column_name>is the text column.To index more than one column, concatenate the columns in the expression, for example
title || ' ' || body.Search the column. Use the same configuration as the index, and repeat the index expression in the query so that the query uses the index:
SELECT <column1>, <column2> FROM <table_name> WHERE to_tsvector('<config>', <column_name>) @@ to_tsquery('<config>', '<search_term>');
Where
<column1>and<column2>are the columns to return and<search_term>is the word to search for.
For example, to index and search the books table from Getting started:
CREATE INDEX books_fts ON books USING gin (to_tsvector('korean', title || ' ' || body)); SELECT id, title FROM books WHERE to_tsvector('korean', title || ' ' || body) @@ to_tsquery('korean', '데이터베이스');
The query returns the one book that matches:
id | title ----+------------------- 2 | 데이터베이스 개론
Any conjugation in the query matches. A query for 피겠다, 피었습니다, or 핀다 finds documents that contain any other form of the verb 피다.
Indexing large tables
Speed up queries on large tables, such as millions of rows with a high query rate, by storing the tsvector in a column instead of recomputing it on every query. The index in Indexing and searching text is an expression index, so WarehousePG computes the tsvector for each row when it builds the index, and each query repeats the same expression. Declare the column as a stored generated column, so that WarehousePG fills it for existing rows and keeps it current on every INSERT and UPDATE:
ALTER TABLE <table_name> ADD COLUMN <tsvector_column> tsvector GENERATED ALWAYS AS (to_tsvector('<config>', <column_name>)) STORED; CREATE INDEX <index_name> ON <table_name> USING gin (<tsvector_column>);
Where <tsvector_column> is a name for the new column, and <index_name> is a name for the index. The other placeholders are the same as in Indexing and searching text.
Search the stored column directly, without repeating the to_tsvector expression:
SELECT <column1>, <column2> FROM <table_name> WHERE <tsvector_column> @@ to_tsquery('<config>', '<search_term>');
Where <column1> and <column2> are the columns to return, and <search_term> is the word to search for.
For example, to give title matches in the books table a higher weight than body matches, combine several tsvector values with setweight. A weight, from A (highest) to D (lowest), labels the part of the document that a term came from, and ts_rank scores a match on a higher weight above a match on a lower weight:
ALTER TABLE books ADD COLUMN fts tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('korean', title), 'A') || setweight(to_tsvector('korean', coalesce(body, '')), 'D')) STORED; CREATE INDEX books_fts_col ON books USING gin (fts);
Then search the weighted fts column:
SELECT id, title FROM books WHERE fts @@ to_tsquery('korean', '문학');
id | title ----+------------------ 3 | 한국 현대 문학사
Note
Generalized Search Tree (GiST) indexes are also supported. They're smaller and faster to update than GIN indexes, but slower to query. Use GIN unless you have a specific reason to use GiST.
Querying text
Find the rows that match a search, order them by relevance, and highlight the matching terms. Pick the query function that fits how people enter their searches, from a single word to free text in a search box. Every query function segments the search text the same way as the indexed documents, so a query matches regardless of the inflection that you use.
Ranking results
Rank results with ts_rank:
SELECT <column1>, <column2>, ts_rank(<tsvector>, <tsquery>) AS rank FROM <table_name> WHERE <tsvector> @@ <tsquery> ORDER BY rank DESC;
Where <column1> and <column2> are the columns to return, <tsvector> is a tsvector column or a to_tsvector('<config>', <column_name>) expression that matches your index, and <tsquery> is a query such as to_tsquery('<config>', '<search_term>').
For example, to rank results with the weighted fts column from Indexing large tables, so that matches in the title rank above matches in the body:
SELECT id, title, ts_rank(fts, q) AS rank FROM books, to_tsquery('korean', '문학') q WHERE fts @@ q ORDER BY rank DESC;
id | title | rank ----+------------------+------------ 3 | 한국 현대 문학사 | 0.06079271
Building a search box
Support an end-user search box with websearch_to_tsquery, which accepts quoted phrases, - to exclude a term, and OR:
SELECT <column1>, <column2> FROM <table_name> WHERE <tsvector> @@ websearch_to_tsquery('<config>', '<search_text>');
Where <search_text> is the text that the user types in the search box.
For example, to search with a quoted word and an excluded term:
SELECT id, title FROM books WHERE to_tsvector('korean', title || ' ' || body) @@ websearch_to_tsquery('korean', '"데이터베이스" -나비');
id | title ----+------------------- 2 | 데이터베이스 개론
Searching for phrases
Search for adjacent words with phraseto_tsquery:
SELECT <column1>, <column2> FROM <table_name> WHERE <tsvector> @@ phraseto_tsquery('<config>', '<phrase>');
Where <phrase> is the phrase to search for.
For example, to search for the adjacent words 무궁화 and 꽃:
SELECT id, title FROM books WHERE to_tsvector('korean', title || ' ' || body) @@ phraseto_tsquery('korean', '무궁화 꽃');
id | title ----+------------------------ 1 | 무궁화 꽃이 피었습니다
Highlighting matches
Highlight matches with ts_headline, which marks fragments inside text that has no spaces between words:
SELECT ts_headline('<config>', <column_name>, <tsquery>) FROM <table_name>;
Where <column_name> is the text column to highlight, and <tsquery> is the query that defines the terms to mark.
For example, to highlight matches in the body of one book. By default, ts_headline wraps each match in <b> and </b> tags, which an application can display as bold text:
SELECT ts_headline('korean', body, to_tsquery('korean', '문학')) FROM books WHERE id = 3;
ts_headline ------------------------------------------------------ 근대부터 현대까지 한국 <b>문학</b>의 흐름을 정리했다
Setting a default language
Omit the configuration name from the text search functions in a single-language database by setting default_text_search_config at the session level or the database level:
-- For the current session SET default_text_search_config = <config>; -- For every new connection to one database ALTER DATABASE <database_name> SET default_text_search_config = <config>;
Where <config> is korean, japanese, or chinese, and <database_name> is the name of the database.
Then call the functions without a configuration name:
SELECT to_tsvector('<text>');
Where <text> is the text to segment. For example:
SELECT to_tsvector('무궁화');
The index definition must keep the explicit configuration name. The short forms depend on the current setting, so WarehousePG rejects CREATE INDEX ... (to_tsvector(body)). Write to_tsvector('korean', body) in both the index and the matching WHERE clause.
Searching JSON documents
Search JSON documents with to_tsvector, which extracts and segments every string value in a json or jsonb document:
SELECT to_tsvector('<config>', <json_column>) FROM <table_name>;
Where <json_column> is a json or jsonb column of the table. For example:
SELECT to_tsvector('korean', '{"title":"무궁화꽃이 피었습니다","author":"김철수","tags":["소설","한국문학"]}'::jsonb);
to_tsvector ----------------------------------------------------------------- '김철수':10 '꽃':7 '무궁화':6 '문학':4 '소설':1 '피':8 '한국':3
Index only certain value types with jsonb_to_tsvector, by listing the types in the third argument:
SELECT jsonb_to_tsvector('<config>', <jsonb_column>, '["<value_type>", ...]') FROM <table_name>;
Where <jsonb_column> is a jsonb column of the table, and <value_type> is a type of value to index, such as string or numeric.
For example, to index only the string and numeric values:
SELECT jsonb_to_tsvector('japanese', '{"name":"寿司を食べた","count":5}'::jsonb, '["string","numeric"]');
jsonb_to_tsvector --------------------------- '5':4 '寿司':1 '食べる':2
Searching in multiple languages
Search in more than one language by using the three configurations in the same database. The configurations are independent, and each one segments text in its own language:
SELECT to_tsvector('korean', '<korean_text>') AS ko, to_tsvector('japanese', '<japanese_text>') AS ja, to_tsvector('chinese', '<chinese_text>') AS zh;
Where <korean_text>, <japanese_text>, and <chinese_text> are the text to segment in each language. For example:
SELECT to_tsvector('korean', '무궁화꽃이 피었습니다.') AS ko, to_tsvector('japanese', '寿司を食べた') AS ja, to_tsvector('chinese', '