This document explains the various methods you can use to create a ZomboDB index.
For all these examples below, lets assume we have a table defined as:
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, manufacturer_id bigint );
Indexing All Columns
The simplest way to create a ZomboDB index is to simply index all the columns in the table. Indexing all columns allows you to query any of them in Elasticsearch queries and also use any of them in ZomboDB's aggregate functions.
CREATE INDEX idxproducts ON products USING zombodb ((products.*));
Indexing Specific Columns
If you only wish to index, and as such only be able to query/aggregate, specific columns, you can use the Postgres
ROW() constructor in a custom function to return a custom type that represents the shape of the row you wish to index.
First however, you must define a custom Postgres composite data type in which to cast the columns you wish to index.\ This is necessary to - define the field names you'll use for searching - change data types if you wish.
-- our custom type -- note we've changed the type of 'name' from "text" to "varchar" to have it indexed as a keyword CREATE TYPE products_idx_type AS ( id bigint, name varchar, description text ); -- package up what we want to index as a ROW returned by a custom function CREATE FUNCTION products_idx_func(products) RETURNS products_idx_type IMMUTABLE STRICT LANGUAGE sql AS $$ SELECT ROW ( $1.id, $1.name, COALESCE($1.short_summary, '') || ' ' || COALESCE($1.long_description, '') )::products_idx_type; $$; -- create a USING zombodb index which uses the above function CREATE INDEX idxproducts ON products USING zombodb ((products_idx_func(products.*))); -- and now, whenever we query, we need to reference our function SELECT * FROM products WHERE products_idx_func(products) ==> 'box'
With this form, you'll only be able to search, using ZomboDB, the
description fields (which come
products_idx_type type. You'll also notice that the
name column's type has been changed from
varchar, which (if you read the documentation/TYPE-MAPPING documentation, will cause it be indexed as a
keyword in Elasticsearch.
Additionally, we concatenate the
long_description columns (guarding against NULL values
and separating with a space) into the field named
Advanced Functional Indexing
If you want to build more complex indices than the above options allow, the process is similar to the above.
The function must be a one-argument function that takes the table as its only argument and returns whatever your custom type is.
For this example, lets assume we also have a table called
CREATE TABLE manufacturer ( id SERIAL8 NOT NULL PRIMARY KEY, name text NOT NULL, address1 text, address2 text, city varchar, state varchar(2), zip_code varchar, phone varchar, fax varchar, support_email varchar );
And lets say when we create our ZomboDB index on
products we want to include the product's manufacturer data with each
CREATE TYPE products_idx_type AS ( id bigint, name varchar, short_summary text, manufacturer jsonb );
Now we create a function that will convert the related manufacturer information for each product into a jsonb blob:
CREATE FUNCTION products_with_manufacturer(products) RETURNS products_idx_type IMMUTABLE STRICT LANGUAGE SQL AS $$ SELECT ROW($1.id, $1.name, $1.short_summary, (SELECT row_to_json(m) FROM manufacturer m WHERE m.id = $1.manufacturer_id))::products_idx_type; $$;
And finally, we can create the ZomboDB index:
CREATE INDEX idxproducts ON products USING zombodb (products_with_manufacturer(products));
With this, the backing Elasticsearch index will have a
nested_object field named
manufacturer that can be queried:
SELECT * FROM products WHERE products_with_manufacturer(products) ==> 'manufacturer.name:Sears';
It's important to understand that with this example, INSERTs/UPDATEs/DELETEs to the
manufacturer table WILL NOT be
reflected in the ZomboDB index on
products until the corresponding row(s) in
products are later modified in some
You can manually solve this situation by applying
ON INSERT/UPDATE/DELETE triggers on
manufacturer that somehow
"touch" all the rows in
products that match on
manufacturer.id = products.manufacturer_id.
- the function you create can be implemented in any supported Postgres
LANGUAGE, not just
SQL-- it could be implemented in
- in order for Postgres to decide to use the functional index, it must be referenced in the query's
- changes to the function's implementation (via
CREATE OR REPLACE FUNCTION) will require that the index be reindexed using Postgres'
These are just some simple examples. It's up to you to decide what you want to index/query, and how.