PolarDB:ZomboDB

Step 1: Create a table

CREATE TABLE products (
    id               SERIAL8 NOT NULL PRIMARY KEY,
    name             text NOT NULL,
    keywords         varchar(64)[],
    short_summary    text,
    long_description zdb.fulltext,
    price            bigint,
    inventory_count  integer,
    discontinued     boolean default false,
    availability_date date
);

Step 2: Create a ZomboDB index

Create an index that points to your Elasticsearch cluster. ZomboDB manages the corresponding remote index and keeps it in sync with your PostgreSQL table.

CREATE INDEX idxproducts
          ON products
       USING zombodb ((products.*))
        WITH (url='localhost:9200/');
        -- Replace localhost:9200 with your Elasticsearch cluster endpoint.
        -- ZomboDB does not support Elasticsearch 7.x and 8.x.

Step 3: Insert sample data

INSERT INTO products (name, keywords, short_summary, long_description, price, inventory_count)
VALUES
  ('Wooden Baseball Bat',  ARRAY['sports', 'outdoor'],
   'Classic wooden bat for recreational play.',
   'A sturdy wooden bat suitable for backyard games and casual leagues.',
   4999, 120),
  ('Boxing Gloves',        ARRAY['sports', 'box', 'training'],
   'Professional boxing gloves for heavy bag training.',
   'Durable leather gloves with wrist support, ideal for gym workouts.',
   14999, 45),
  ('Running Shoes',        ARRAY['footwear', 'running', 'outdoor'],
   'Lightweight shoes for road and trail running.',
   'Breathable mesh upper with cushioned sole for long-distance comfort.',
   8999, 200);

Step 4: Query using ZomboDB

Use the ==> operator—ZomboDB's custom search operator—to run a query expression against the index. The expression supports keyword matching, phrase proximity, and range filters in a single query.

The following query finds products whose keywords include sports or box, or whose long_description contains the phrase wooden away within five words, with a price between 1,000 and 20,000.

SELECT *
  FROM products
 WHERE products ==> '(keywords:(sports OR box) OR long_description:"wooden away"~5) AND price:[1000 TO 20000]';

Expected result: the rows for Wooden Baseball Bat and Boxing Gloves match, because their prices fall within the specified range and their keywords or descriptions satisfy the query expression.