Filter products in shared catalogs with price range

[thangld (Thang Le)]

Hi there,
I have a store and use Elasticsearch for storing products.
I need to filter products by price range. It is simple if products' price is the same to all customers, but in my case, customers from different group will see different price. It is called shared catalogs.
Shared catalog can be created or deleted anytime. It has the same products as original catalog but different price.
I have thought of two ways to do this:

1. Everytime a shared catalog is created, I will add a new field price_catalog_x to all the products. When customers from catalog_x get products with price range, I will filter products by price_catalog_x range.
   Fields:

product_name
price_catalog_1
price_catalog_2
...

2. Everytime a shared catalog is created, I will duplicate all the original products with additional catalog ID and new price fields. When customers from catalog X get products with price range, I will filter products by catalog ID and price fields.
   Fields:

product_name
catalog_id
price

What is the most efficient way to implement this case using Elasticsearch? Or Are there any better ways please? Combining with another database to do this is acceptable. Thanks!

P.s: Reason I store products in Elasticsearch because I also need to perform full text search on some fields like name, SKU.

[spinscale (Alexander Reelsen)]

Hey,

this is indeed a tricky problem in ecommerce search, especially as pricing sometimes is extremely dynamic. Check out the following snippet using field collapsing and a priority for custom prices. Maybe that helps a little to get an idea.

DELETE products,products-customer-1

PUT products/_doc/a
{
  "id" : "a",
  "name" : "a product",
  "price": 30,
  "priority": 1
}

PUT products/_doc/b
{
  "id" : "b",
  "name" : "b product name",
  "price": 20,
  "priority": 1
}

PUT products-customer-1/_doc/a?refresh
{
  "id" : "a",
  "name" : "a product name",
  "price": 20,
  "priority": 2
}

GET products,products-customer-1/_search
{
  "query": {
    "range": {
      "price": {
        "lte": 20
      }
    }
  }
}

GET products,products-customer-1/_search
{
  "query": {
    "range": {
      "price": {
        "lte": 30
      }
    }
  },
  "collapse": {
    "field": "id.keyword"
  },
  "sort": [
    {
      "priority": {
        "order": "desc"
      }
    },
    {
      "price": {
        "order": "desc"
      }
    }
  ]
}

Make sure to check out field collapsing and its limitations (i.e. on counts).

hope this helps as a start!