How to Create Custom Indexes with Computed Properties

In this post, we’ll explore how to define computed properties and index the results for fast and efficient custom search capabilities in HarperDB 4.4.

Computed properties are virtual fields that aren’t stored but are calculated in real time based on other data. This saves storage space and efficiently handles fields that change often, like discounts, shipping costs, or sales stats. 

We will then demonstrate how to index a computed property and store it for efficient retrieval. By creating custom indexes on computed properties, we can efficiently search for specific data patterns or combinations not directly represented in the underlying data structure. This opens up new possibilities for data exploration and analysis like full-text and vector indexing.

New in HarperDB 4.4 

Computed Properties and Custom Indexing are new capabilities available in HarperDB 4.4. For the complete 4.4 release note, click here.

‍

How to Define a Computed Property

Let’s start by defining a simple blog post table and a few computed properties. First, we will define a blog post schema and a computed property in our schema.graphql.

// in schema.graphql:
type BlogPost @table @export {
	id: ID @primaryKey
	title: String @indexed
	author: String
	content: String
	summary: String @computed(from: "title + ' by ' + author")
}

Here, you can see that we defined a computed property that generates a “summary” of the post from the title and author fields. In this example, we defined the computed property and the function to compute the property directly inline. We could alternately do this in JavaScript. Let’s add an authorLastName and compute it in JS.

// in schema.graphql:
type BlogPost @table @export {
	id: ID @primaryKey
	author: String
...
	authorLastName: String @computed
}

// in resrouces.js
tables.BlogPost.setComputedAttribute('authorLastName', (record) =>
	record.author.split(' ')[1]);

‍

Now, we have computed the last name (in a very simplistic/naive way) from the author’s full name.

However, by using JavaScript, we have a broad range of capabilities for how we can compute properties.

With these computed properties in place, we can now retrieve and query for records from HarperDB, and these fields will be available to queries. It is important to note that computed properties are not included in record retrieval or query results by default; only standard stored properties are included. If you want to include them, you must do so explicitly. For example, if we do:

GET /BlogPost/first-post

‍

This will not return any of the computed properties. But we can request the computed properties:

GET /BlogPost/first-post?select(id,title,authorLastName,summary)

‍

Likewise, for queries, we can request the inclusion of computed properties (here we are searching for blog posts with a title that begins with “First”:

GET /BlogPost/?title==First*&select(id,title,authorLastName,summary)

‍

Again, computed properties allow us to define fields at the schema level that are derived from other stored data, allowing us to minimize stored data and provide a flexible and rich set of fields that are available for querying.

‍

How to Index a Computed Property to Unlock Efficient Text and Composite Search Capabilities

‍

Custom indexes are indexed computed properties. By indexing a computed property, we have tremendous flexibility in defining our index and query capabilities. A standard field can only be indexed by the direct literal values in that field, but we can compute our own properties for indexing with a computed property.

To use custom indexes, we simply define a computed property as indexed.

// in schema.graphql:
type BlogPost @table @export {
	id: ID @primaryKey
	author: String
...
	authorLastName: String @computed @indexed
}

‍

In this example, we have indexed the authorLastName, and we can directly search by author’s last name in our queries.

GET /BlogPost/?authorLastName=Smith

‍

Previously, we certainly could have searched by author with an ends_with operator to find any author field that ends with a specific last name (like “Smith”). However, the ends_with operator can not use an index and requires a full table scan to match entries. For any sizable table, this is highly discouraged and should be avoided. However, by creating an index of author’s last names, as in this example, we can efficiently and quickly search by last name.

Computed properties can also yield an array of values and, when combined with indexing, allow us to search for multiple values. This can facilitate broad search queries when combined with normalization to make it easier to find records. Let’s create an example that will allow us to search for any word in the title or author name. We will define a keywords computed property that contains an array list of all words that we might want to search for.

// in schema.graphql:
type BlogPost @table @export {
	id: ID @primaryKey
	author: String
...
	keywords: [String] @computed @indexed
}

// in resources.js:
tables.BlogPost.setComputedAttribute(keywords, (record) => {
	let keywords = [...record.title.split(' '),
	...record.author.split(' ')];
return keywords.map(word => word.toLowerCase());
});

‍

In this example, we define a computed keywords field, defining it as computed and index and then specifying how we will compute the keywords. Here, we are accomplishing several things. First, we are combining the words from both the title and author fields. We then split each of these fields into individual words and put the aggregate of all the individual words from each field into a combined array. Then, we normalize the capitalization of the words in the array to lowercase. We then return this array as the computed property. When this is indexed, each word in the array will be indexed and point back to the stored record. This means we can now search for any part of the author's name or title.

GET /BlogPost/?keywords=smith # can match first or last name
GET /BlogPost/?keywords=first # or any word in the title

‍

This would match blog posts with an author like “John Smith” and blog posts with a title like “My First Post” because all of these words would be indexed. Note that part of our indexing was to normalize it to lowercase. This means we must search using lowercase values. But by doing this, we can match any capitalization of words in the author or title. If the title was “My first post” or “My First Post”, these would both match keywords=first due to the normalization.

As you may have noticed, this is a basic building block for more advanced full-text indexing, which is very feasible with this approach. However, true full-text indexing is undoubtedly more advanced, with more robust word tokenization, diacritic normalization, stemming, spell correction, and other techniques for effective full-text searches that match human expectations of word matching without requiring exact character-for-character matching. Look for full-text indexing to be natively available in an upcoming HarperDB release.

In these examples, we have been using custom indexes to define broader search indexes. However, we can also create composite indexes that allow us to efficiently search with more specific queries that require matching across multiple fields. For example, if we had separate fields for first name and last name, and we want to query for an exact name, we could do so with:

GET /Person/?firstName=John&lastName=Smith

‍

However, if firstName and lastName are separate fields and indexes, this query involves querying one index and filtering the results by the second condition. If we have a large table with many records for people with a first name of “John” and records with a last name of “Smith”, it may be an expensive query to find the records that match both conditions. If we were to create a composite index, we could create an index that consists of both first and last names combined (effectively going in the opposite direction as the example above).

// in schema.graphql:
type Person @table @export {
	id: ID @primaryKey
	firstName: String @indexed
	lastName: String @indexed
	fullName: String @computed(from: "firstName + ' ' + lastName ") @indexed
}

‍

And query:

GET /Person/?fullName=John Smith

‍

This query will be executed directly against the composite index and efficiently find only records with the exact full name match.

As you can see, custom indexing is a powerful way to expand search functionality. This can also open the door for many types of indexing, including composite indexing, full-text indexing, and even more expansive algorithms, such as vector indexing for AI use cases.