Use cases

Prerequisites

An AnalyticDB for PostgreSQL V7.0 instance running minor version V7.2.1.7 or later, or V7.3.2.2 or later.

Import the Wikipedia dataset

The Wikipedia dataset (wiki-articles-1w.json) contains 10,000 rows with three fields: url, title, and body.

Because the raw JSON file uses standard quotation marks and commas as content characters, import it through a staging table to parse the data cleanly.

1. Create a staging table to hold the raw JSON.

   CREATE TABLE temp_json(data JSONB);

2. Import the data file into temp_json. Replace /path/to/wiki-articles-1w.json with the actual file path.

   \COPY temp_json(data) FROM '/path/to/wiki-articles-1w.json' CSV QUOTE E'\x01' DELIMITER E'\x02';

   The E'\x01' and E'\x02' byte sequences serve as the quotation mark and delimiter characters, because the file content already contains standard quotation marks and commas.

3. Create the target table wiki.

   CREATE TABLE wiki (
       id SERIAL PRIMARY KEY,
       url TEXT,
       title TEXT,
       body TEXT
   );

4. Parse the JSONB data from temp_json and insert it into wiki.

   INSERT INTO wiki(url, title, body)
   SELECT data->>'url' AS url,
          data->>'title' AS title,
          data->>'body' AS body
   FROM (SELECT data FROM temp_json) A;

Create a BM25 index

Use the create_bm25() function to build a BM25 index on the body field of the wiki table. Indexing the body field enables relevance-ranked search across article content.

CALL pgsearch.create_bm25(
    index_name => 'wiki_idx',
    table_name => 'wiki',
    text_fields => '{body: {}}'
);

Run a full-text search

Query the BM25 index using the @@@ operator with pgsearch.config(). Results are automatically sorted by relevance score, with the most relevant rows first.

SELECT * FROM wiki
ORDER BY body @@@ pgsearch.config('body:"artificial intelligence"')
LIMIT 20;

This query returns the 20 rows whose body field best matches the phrase "artificial intelligence".

Complete example

The following statements combine all steps in sequence.

-- Import a JSON file.
CREATE TABLE temp_json(data JSONB);
\COPY temp_json(data) FROM '/path/to/wiki-articles-1w.json' CSV QUOTE E'\x01' DELIMITER E'\x02';

-- Create a table named wiki.
CREATE TABLE wiki (
    id SERIAL PRIMARY KEY,
    url TEXT,
    title TEXT,
    body TEXT
);

-- Insert the parsed JSONB data into the wiki table.
INSERT INTO wiki(url, title, body)
SELECT values->>'url' AS url,
       values->>'title' AS title,
       values->>'body' AS body
FROM (SELECT data AS values FROM temp_json LIMIT 10000) A;

-- Create a BM25 index.
CALL pgsearch.create_bm25(
    index_name => 'wiki_idx',
    table_name => 'wiki',
    text_fields => '{body: {}}'
);

-- Perform full-text search.
SELECT * FROM wiki
ORDER BY body @@@ pgsearch.config('body:"artificial intelligence"')
LIMIT 20;