Loading...
Area: Optimizely B2B Commerce

Add a custom field to a search product document

Recommended reading 

As an exercise, let’s say you want to add a new field to the product document called MyCustomField that will be set to the number of order lines that included each product. Once this field is added, it could be used in product searches to modify the sort order. 

NOTE: Your extension project will need to reference NEST – a strongly typed interface to Elasticsearch. 

The product indexing process has 3 main pipelines. These are used by the ElasticsearchProductRepository to transfer data from database into the Elasticsearch index:

  • GetIndexableProducts - This pipeline makes optimized SQL queries to fetch all of the products and associated data that is needed for searching and returns them as a list of IndexableProduct objects. 
  • CreateElasticsearchProduct - This pipeline converts an IndexableProduct into an ElasticsearchProduct. ElasticsearchProduct is the form of the product on elasticsearch and includes Nest attributes which define the field names and indexing parameters. After all of the data is converted into a list of ElasticsearchProduct objects, the data is posted to the Elasticsearch server.
  • MapElasticSearchProductProperty - This pipeline handles the auto indexing of product custom properties and will not be used in this example.

Add classes to the extension project

In your extension project you will need to add two new classes:

1.  A class inherited from IndexableProduct. This will allow you to add new fields but keep the base fields. In this example we add a new int field called MyCustomField

namespace Extensions.Plugins.Search.Elasticsearch.DocumentTypes.Product
{
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Index;

    public class IndexableProductCustom : IndexableProduct
    {
        public int MyCustomField { get; set; }
    }
}

2. A class inherited from ElasticsearchProduct. The constructor should copy the data in all of the fields in the base class.

namespace Extensions.Plugins.Search.Elasticsearch.DocumentTypes.Product
{
    using Nest;

    using Insite.Search.Elasticsearch.DocumentTypes.Product;

    [ElasticsearchType(Name = "product")]
    public class ElasticsearchProductCustom : ElasticsearchProduct
    {
        public ElasticsearchProductCustom(ElasticsearchProduct elasticsearchProduct)
        {
            this.BasicListPrice = elasticsearchProduct.BasicListPrice;
            this.BasicSaleEndDate = elasticsearchProduct.BasicSaleEndDate;
            this.BasicSalePrice = elasticsearchProduct.BasicSalePrice;
            this.BasicSaleStartDate = elasticsearchProduct.BasicSaleStartDate;
            this.BrandFacet = elasticsearchProduct.BrandFacet;
            this.BrandId = elasticsearchProduct.BrandId;
            this.BrandIsSponsored = elasticsearchProduct.BrandIsSponsored;
            this.BrandManufacturer = elasticsearchProduct.BrandManufacturer;
            this.BrandName = elasticsearchProduct.BrandName;
            this.BrandNameFirstCharacter = elasticsearchProduct.BrandNameFirstCharacter;
            this.BrandNameSort = elasticsearchProduct.BrandNameSort;
            this.BrandProductLineFacet = elasticsearchProduct.BrandProductLineFacet;
            this.BrandSearchBoost = elasticsearchProduct.BrandSearchBoost;
            this.BrandUrlSegment = elasticsearchProduct.BrandUrlSegment;
            this.Boost = elasticsearchProduct.Boost;
            this.Categories = elasticsearchProduct.Categories;
            this.CategoryNames = elasticsearchProduct.CategoryNames;
            this.CategoryTree = elasticsearchProduct.CategoryTree;
            this.Content = elasticsearchProduct.Content;
            this.CustomProperties = elasticsearchProduct.CustomProperties;
            this.CustomerNames = elasticsearchProduct.CustomerNames;
            this.Customers = elasticsearchProduct.Customers;
            this.DefaultVisibility = elasticsearchProduct.DefaultVisibility;
            this.DocumentNames = elasticsearchProduct.DocumentNames;
            this.ErpDescription = elasticsearchProduct.ErpDescription;
            this.ErpNumber = elasticsearchProduct.ErpNumber;
            this.ErpNumberWithoutSpecialCharacters = elasticsearchProduct.ErpNumberWithoutSpecialCharacters;
            this.FilterNames = elasticsearchProduct.FilterNames;
            this.Filters = elasticsearchProduct.Filters;
            this.Id = elasticsearchProduct.Id;
            this.ImageAltText = elasticsearchProduct.ImageAltText;
            this.IsSponsored = elasticsearchProduct.IsSponsored;
            this.LanguageCode = elasticsearchProduct.LanguageCode;
            this.ManufacturerItem = elasticsearchProduct.ManufacturerItem;
            this.ManufacturerItemWithoutSpecialCharacters = elasticsearchProduct.ManufacturerItemWithoutSpecialCharacters;
            this.MediumImagePath = elasticsearchProduct.MediumImagePath;
            this.MetaDescription = elasticsearchProduct.MetaDescription;
            this.MetaKeywords = elasticsearchProduct.MetaKeywords;
            this.ModelNumber = elasticsearchProduct.ModelNumber;
            this.ModifiedOn = elasticsearchProduct.ModifiedOn;
            this.Name = elasticsearchProduct.Name;
            this.PackDescription = elasticsearchProduct.PackDescription;
            this.PageTitle = elasticsearchProduct.PageTitle;
            this.Price = elasticsearchProduct.Price;
            this.PriceFacet = elasticsearchProduct.PriceFacet;
            this.ProductCode = elasticsearchProduct.ProductCode;
            this.ProductId = elasticsearchProduct.ProductId;
            this.ProductUrlSegment = elasticsearchProduct.ProductUrlSegment;
            this.RestrictionGroups = elasticsearchProduct.RestrictionGroups;
            this.SearchLookup = elasticsearchProduct.SearchLookup;
            this.ShippingWeight = elasticsearchProduct.ShippingWeight;
            this.ShortDescription = elasticsearchProduct.ShortDescription;
            this.ShortDescriptionSort = elasticsearchProduct.ShortDescriptionSort;
            this.Sku = elasticsearchProduct.Sku;
            this.SmallImagePath = elasticsearchProduct.SmallImagePath;
            this.SortOrder = elasticsearchProduct.SortOrder;
            this.Specifications = elasticsearchProduct.Specifications;
            this.SpellingCorrection = elasticsearchProduct.SpellingCorrection;
            this.StyledChildren = elasticsearchProduct.StyledChildren;
            this.UnitOfMeasure = elasticsearchProduct.UnitOfMeasure;
            this.UnitOfMeasureDescription = elasticsearchProduct.UnitOfMeasureDescription;
            this.Unspsc = elasticsearchProduct.Unspsc;
            this.UpcCode = elasticsearchProduct.UpcCode;
            this.Vendor = elasticsearchProduct.Vendor;
            this.Version = elasticsearchProduct.Version;
            this.Websites = elasticsearchProduct.Websites;
        }

        [Keyword(Name = "numberOfOrders", Index = true)]
        public int NumberOfOrders { get; set; }
    }
}

Define the pipes

 Next you will need to define three pipes in order to actually modify the indexing process.

1.  Make a GetIndexableProducts pipe which will set the CustomFields property on the result object. This property should be set to a SQL fragment which will be inserted into the main indexing SELECT query and will fetch the additional data that is needed. Alias the data with the name of the custom field, in this case MyCustomField.

  • This pipe should run between Order 100 and 200 so that it is after the GetIndexableProductsSqlStatementParts pipe (which sets the SqlStatement field to the large SQL block) and before the pipe CombineIndexableProductsSqlStatementParts (which builds the final query.)
  • If you need to make more radical changes to the main indexing SQL query you can modify the result.SqlStatement field directly (or replace GetIndexableProductsSqlStatementParts entirely), but this should be avoided in order to prevent upgradability issues.
  • Also note that doing large subqueries can adversely affect indexing performance. This is example is more for instructional purposes than anything you would want to do in an actual site.
namespace Extensions.Plugins.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Pipes.GetIndexableProducts
{
    using Insite.Core.Interfaces.Data;
    using Insite.Core.Plugins.Pipelines;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Parameters;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Results;

    public class AddCustomFieldsToSqlStatement : IPipe<GetIndexableProductsParameter, GetIndexableProductsResult>
    {
        public int Order => 150;

        public GetIndexableProductsResult Execute(
            IUnitOfWork unitOfWork,
            GetIndexableProductsParameter parameter,
            GetIndexableProductsResult result)
        {
            result.CustomFields = @"
                (select count(*) from OrderLine ol with (nolock)
                inner join CustomerOrder o on o.id = ol.CustomerOrderId 
                where p.Id = ol.ProductId and o.Status = 'Submitted') 
                as MyCustomField";

            return result;
        }
    }
}

2. Also in the GetIndexableProducts pipeline, replace the pipe PerformIndexableProductsSqlQuery at Order 300, in order to deserialize the results of the SQL query into the IndexableProductCustom type.

namespace Extensions.Plugins.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Pipes.GetIndexableProducts
{
    using Insite.Core.Interfaces.Data;
    using Insite.Core.Plugins.Pipelines;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Parameters;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Results;

    public class PerformIndexableProductsSqlQuery : IPipe<GetIndexableProductsParameter, GetIndexableProductsResult>
    {
        public int Order => 300;

        public GetIndexableProductsResult Execute(IUnitOfWork unitOfWork,
                                                  GetIndexableProductsParameter parameter,
                                                  GetIndexableProductsResult result)
        {
            result.IndexableProducts = unitOfWork.DataProvider.SqlQuery(
                result.FormattedSqlStatement, null, false, parameter.QueryTimeout);

            return result;
        }
    }
}

3. Create a new pipe in the CreateElasticsearchProduct pipeline to copy the custom data field from the IndexableProduct to the ElasticsearchProduct.

namespace Extensions.Plugins.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Pipes.CreateElasticsearchProduct
{
    using Insite.Core.Interfaces.Data;
    using Insite.Core.Plugins.Pipelines;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Parameters;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Index.Pipelines.Results;

    public class SetCustomFields : IPipe<CreateElasticsearchProductParameter, CreateElasticsearchProductResult>
    {
        public int Order => 150;

        public CreateElasticsearchProductResult Execute(
            IUnitOfWork unitOfWork,
            CreateElasticsearchProductParameter parameter,
            CreateElasticsearchProductResult result)
        {
            var elasticsearchProductCustom = new ElasticsearchProductCustom(result.ElasticsearchProduct);
            var indexableProductCustom = parameter.IndexableProduct as IndexableProductCustom;
            elasticsearchProductCustom.MyCustomField = indexableProductCustom?.MyCustomField ?? 0;

            result.ElasticsearchProduct = elasticsearchProductCustom;

            return result;
        }
    }
}

Use the new field

With this code in place, the new field will be added to your elasticsearch index product documents. You can then use this data in the query pipelines however you see fit.

This is an example of using the new field to influence the sort order of results adding a pipe in the RunProductSearch pipeline. This pipe runs after the FormSortOrder pipe (Order 300) and it modifies the SortOrderFields field on RunProductSearchResult, when the user is looking at a category page in the default Relevance sort order.

namespace Extensions.Plugins.Search.Elasticsearch.DocumentTypes.Product.Query.Pipelines.Pipes.RunProductSearch
{
    using System;
    using Insite.Core.Interfaces.Data;
    using Insite.Core.Plugins.Pipelines;
    using Insite.Core.Plugins.Search;
    using Insite.Search.Elasticsearch.DocumentTypes.Product;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Query.Pipelines.Parameters;
    using Insite.Search.Elasticsearch.DocumentTypes.Product.Query.Pipelines.Results;

    public class FormCustomSortOrder : IPipe<RunProductSearchParameter, RunProductSearchResult>
    {
        public int Order => 310;

        public RunProductSearchResult Execute(
            IUnitOfWork unitOfWork,
            RunProductSearchParameter parameter,
            RunProductSearchResult result)
        {

            if (result.SortOrderFields == null)
            {
                return result;
            }

            if (parameter.ProductSearchParameter.SortBy != SortOrderType.Relevance ||
                parameter.ProductSearchParameter.SearchCriteria.IsNotBlank() || 
                parameter.ProductSearchParameter.SearchCriteria.IsNotBlank())
            {
                return result;
            }

            result.SortOrderFields = new[]
            {
                new SortOrderField(nameof(ElasticsearchProduct.SortOrder).ToCamelCase(), true, true),
                new SortOrderField(nameof(ElasticsearchProductCustom.MyCustomField).ToCamelCase(), true, true),
                new SortOrderField(nameof(ElasticsearchProduct.ShortDescriptionSort).ToCamelCase())
            };

            return result;
        }
    }
}

All of the available pipelines on the product query side are:

  • RunProductSearch - The main product query builder.FormProductFilterBuilds up filters for product search over category, language, attribute values, brands, price range, product line, restriction group and website. The generated filter is used by the other search pipelines.
  • RunProductFacetSearch - Performs faceted searches over brand, product line, and category.
  • RunBrandSearch - Searches for brands within the product index.FormRestrictionGroupFilter Builds the restriction group filter for FormProductFilter pipeline.

NOTE: If you have access to the Optimizely Dogfood sample repository in GitHub, it includes an example of adding a multivalue field to the index and using it to filter the products based on which warehouses have stock. It demonstrates how to add a new filter pipe to the FormProductFilter pipeline and how to pass a custom parameter from the external API into the search provider.
Do you find this information helpful? Please log in to provide feedback.

Last updated: Dec 11, 2020

Recommended reading