Elasticsearch/OpensSearch query for product variations

Question

I am using AWS OpenSearch database which is a fork of Elasticsearch. My use case is as follows:

• A product contains an ID, a title, a brand, a productGroupId
• Query: Given a string, find all products that has title OR brand that matches the string. For each returned product, include a variations field which is a list of IDs of all other products within the same productGroupId

My current thinking is I am going to perform the query in 2 steps:

• Query for all products by title and brand
• For each product, query by its productGroupId to populate the variations field (some optimization can be done to minimize the number of productGroupId look ups but that’s beside the point)

Do you have any suggestions on how to design the search index or some queries I can use to make this more efficient?

Answer 1

I would suggest you to create two indexes. One would keep all the product related details, and the other would keep track of all the product variations for a given productGroupId. The documents of these indices will have the following structure.

Product

{
  id: "",
  name: "",
  brand: "",
  productGroupId: ""
}

ProductGroupVariations

{ 
  id: "" //productGroupId,
  variations: [] // list of product ID's
}

Using this structure, when fetching a product, you can fetch variations, using one more call to productGroupVariations index.

Also, make sure to update the variations array, whenever a product is added, updated or deleted.