AnalyticDB:Configure a synonym dictionary

Synonyms are crucial in search. Synonym search and vector search are complementary to each other and can greatly improve the search recall rate when used together. Synonyms provide the following benefits for search:

• Enhanced search accuracy. When you enter a search keyword, a different keyword or phrase may be included in the results related to the entered keyword. Synonyms allow search engines to identify related keywords or phrases and return more accurate matching results.

• Multilingual support. For applications that support multiple languages or dialects, synonyms can help narrow or eliminate lexical differences. For example, an English word and its Chinese counterpart are synonyms.

• Better matching for industry-specific terms. Specific terms are used in various industries or fields. Synonyms can help search engines understand the relationships between the terms and general keywords and return more accurate matching results.

• Improved user experience. If you do not know or temporarily forget a specific keyword, you can use synonyms to obtain the desired results.

Prerequisites

• An AnalyticDB for PostgreSQL V7.0 instance of V7.1.1.0 or later is created.

• The pgsearch extension is installed on the instance.

  Note

  If the pgsearch extension is not installed on the instance, submit a ticket to ask technical support to install the extension. After the extension is installed, you must restart the instance.

Manage a synonym dictionary

Create a synonym dictionary

Invoke the pgsearch.synonyms_create() function to create a synonym dictionary by using a unique synonym dictionary ID specified by the synonyms_id parameter.

SELECT pgsearch.synonyms_create('<synonyms_id>');

Example

SELECT pgsearch.synonyms_create('warehouse');

Delete a synonym dictionary

Invoke the pgsearch.synonyms_drop() function to delete a synonym dictionary by using a unique synonym dictionary ID.

SELECT pgsearch.synonyms_drop('<synonyms_id>');

Example

SELECT pgsearch.synonyms_drop('warehouse');

Insert data into and update a synonym dictionary

Invoke the pgsearch.synonyms_set() function to insert data into or update (overwrite the existing data of) a synonym dictionary. You can insert only data in the JSON format. The value of the jsonb_data parameter consists of key-value pairs. Each key is a term and the corresponding value is a list of synonyms of the term.

SELECT pgsearch.synonyms_set('<synonyms_id>', '<jsonb_data>');

Examples

-- Insert data into a synonym dictionary.
SELECT pgsearch.synonyms_set('warehouse', 
    '{"pc": ["personal computer", "laptop"], 
      "phone": ["telephone", "mobile"], 
      "apple": ["mac", "iphone"], 
      "shoes": ["socks", "boots"]}'
);

-- Update the synonym dictionary by replacing the synonyms (mac and iphone) of the term apple with apples. 
SELECT pgsearch.synonyms_set('warehouse', 
    '{"apple": ["apples"]}'
);

Query synonym dictionaries

• Invoke the pgsearch.synonyms_show() function to query all synonym dictionaries.

  SELECT * FROM pgsearch.synonyms_show();

• Invoke the synonyms_list() function to query information about a synonym dictionary by using a unique synonym dictionary ID. To query the synonyms of a specific term, specify the term by using the synonym dictionary parameter.

  -- Query information about a specific synonym dictionary.
  SELECT * FROM pgsearch.synonyms_list('<synonyms_id>') ORDER BY word;
  
  -- Query the synonyms of a specific term.
  SELECT * FROM pgsearch.synonyms_list('<synonyms_id>', '<word>')

Use synonym dictionaries

• Invoke the research.synonym_terms() function to perform a query by using a synonym dictionary. After the query is complete, results that contain the term specified by the value parameter and the synonyms of the term are returned.

  pgsearch.synonym_terms(synonym_id => '<synonym_id>', field => '<field>', VALUE => '<value>')

  Parameters

  • synonym_id: The unique ID of the synonym dictionary.

  • field: The column from which you want to perform a query.

  • value: The term that you want to query.

  Example

  The synonyms of the term shoes in the warehouse synonym dictionary are socks and boots. You can execute the following statement to query results that contain shoes, socks, and boots from the description column.

  SELECT * FROM mock_items WHERE description @@ pgsearch.config(
      query => pgsearch.synonym_terms(synonym_id => 'warehouse', field => 'description', VALUE => 'shoes')
  )

• You can use the pgsearch.synonym_terms() function in advanced queries such as Boolean search and set search. For more information about advanced queries, see the "Advanced query" section of the Usage guide topic.

  Example 1

  Retrieve results that contain book, shoes, or any synonym of shoes but do not contain speaker from the description column.

  SELECT * FROM mock_items ORDER BY description @@ pgsearch.config(
      query => pgsearch.boolean(
          should => ARRAY[
              pgsearch.term('description:book'),
              pgsearch.synonym_terms(synonym_id => 'warehouse', field => 'description', VALUE => 'shoes')
          ],
          must_not => ARRAY[
              pgsearch.term(field => 'description', VALUE => 'speaker')
          ]
      )
  ) ;

  Example 2

  Retrieve results that contain book, shoes, or any synonym of shoes from the description column.

  SELECT * FROM mock_items WHERE description @@ pgsearch.config(
      query =>  pgsearch.term_set(
          terms => ARRAY[
              pgsearch.synonym_terms(synonym_id => 'warehouse', field => 'description', VALUE => 'shoes'),
              pgsearch.term(field => 'description', VALUE => 'book')
          ]
      )
  )