Sitecore XC stores entities in SQL server in a JSON format. It is possible to query the data that is stored in JSON, but it is a little more difficult and there really is nothing in the XC framework that helps you.
The reason there is no support for flexible querying is that XC gives you the option to index entities in SOLR which gives you really flexible querying.

In XC 10 there are a number of entities that are indexed out-of-the-box:

  • Catalog items (Category, Sellable Item, Catalog)
  • Orders
  • Customer
  • Price Cards
  • Promotions

The catalog items, orders and customer indexes are mainly there to facilitate searching from the Business tools. The price cards and promotions indexes are used by the business tools as well, but also play a major part in the operations of the Shops role: they are used to get the latest prices and promotions, so you need to make sure these are always up to date, otherwise your promotion or price card will not be applied.

But what if you want to index another identity for instance the gift card entity. What do you need to do to make gift cards searchable?

In this article I will go into the different steps and will find out it is surprisingly easy to do.

Note that this article is specifically about SOLR.

You can find the accompanying repository here: https://github.com/ewerkman/indexed-giftcard

Step 1: Create a new core in SOLR

Note: this might not be the best way to create new SOLR cores. Ask someone that actually knows about SOLR for advice :-).

Every index in XC 10 uses two cores, one active one and one for rebuilding the index. For your new index:

  1. Create two new cores in SOLR by copying them from an existing core, for instance the OrdersScope and OrdersScope-Rebuild folders. Copy them to GiftcardsScope and GiftcardsScope-Rebuild respectively.
  2. Delete all files from the data folder in the GiftcardsScope and GiftcardsScope-Rebuild folders. When you do a full index, this folder will be filled again with the correct data.
  3. Change the name of the cores by editing the core.properties file in both core. Open core.properties and change name to GiftcardsScope and GiftcardsScope-Rebuild respectively.
  4. In each core, the conf folder contains the configuration of the core. A file called managed-schema contains the schema for the new core. You need to take out some of the fields that are specific to the index you copied the configuration from and replace them with fields specific for the gift card index.
    The GitHub repository contains the file that you should end up with for my example:
    https://github.com/ewerkman/indexed-giftcard/blob/main/solr/managed-schema.

The most important part there is this section:

    <!-- CommerceEngine Order Unique Id Field -->
    <field name="entityuniqueid" type="string" indexed="true" stored="true" required="true" />
    <field name="entityid" type="string" indexed="true" stored="true" required="true" />
    <field name="entityversion" type="pint" indexed="true" stored="true" />
    
    <!-- CommerceEngine Order -->
    <field name="giftcardcode" type="string" indexed="true" stored="true" />
    <field name="activationdate" type="pdate" indexed="true" stored="true" />
    <field name="balance" type="pfloat" indexed="true" stored="true" />
    <field name="originalamount" type="pfloat" indexed="true" stored="true" />
    <field name="artifactstoreid" type="string" indexed="true" stored="true" />
    <field name="datecreated" type="pdate" indexed="true" stored="true" />
    <field name="dateupdated" type="pdate" indexed="true" stored="true" />

Here the fields specific to the gift card index are defined such as giftcardcode and balance. You want this index to contain all the fields you want to query on.

  1. You can now restart SOLR and your new cores should be visible in the admin interface.

Step 2: Configure the new index

Next you need to configure the new index in XC. You can find all of the necessary configuration in this file:

https://github.com/ewerkman/indexed-giftcard/blob/main/policies/Plugin.Giftcard.PolicySet.json

There are 3 things you need to configure:

FullIndexMinion

This is the minion responsible for doing a full index. You configure it by:

  • specifying the Sitecore.Commerce.Plugin.Search.FullIndexMinion as the minion to run;
  • configuring the GiftCards list as the list to watch for indexing;
  • configuring Sitecore.Commerce.Plugin.GiftCards.GiftCard as the entity to index;

Note that the full index minion is not configured to run on a set interval. You only run it if necessary, for instance by using Postman to start the minion.

IncrementalIndexMinion

The incremental index minion is run on a schedule (by default, every 5 minutes) and watches the GiftcardsIndex list. If something changes on a GiftCard it is automatically added to the specified list. The incremental index minion goes through this list and updates the index.

SearchScopePolicy

The SearchScopePolicy configures the index:

 {
    "$type": "Sitecore.Commerce.Plugin.Search.SearchScopePolicy, Sitecore.Commerce.Plugin.Search",
    "Name": "GiftcardsScope",
    "CurrentIndexName": "GiftcardsScope",
    "SwitchOnRebuild": true,
    "SwitchOnRebuildReset": false,
    "SwitchOnRebuildClearPreviousIndex": true,
    "SwitchOnRebuildPrimaryIndexName": "GiftcardsScope",
    "SwitchOnRebuildSecondaryIndexName": "GiftcardsScope-Rebuild",
    "IncrementalListName": "GiftcardsIndex",
    "DeletedListName": "DeletedGiftcardsIndex",
    "FullListNames": [
        "Giftcards"
    ],
    "EntityTypeNames": {
        "$type": "System.Collections.Generic.List`1[[System.String, mscorlib]], mscorlib",
        "$values": [
            "Sitecore.Commerce.Plugin.GiftCards.GiftCard"
        ]
    },
    "ResultDetailsTags": {
        "$type": "System.Collections.Generic.List`1[[Sitecore.Commerce.Core.Tag, Sitecore.Commerce.Core]], mscorlib",
        "$values": [
            {
                "$type": "Sitecore.Commerce.Core.Tag, Sitecore.Commerce.Core",
                "Name": "GiftcardTable"
            }
        ]
    }
}

Here you specify which cores to use, if you want to do a switch-on-rebuild, which list to use for the incremental index and which one for deleted entities etc.

IndexablePolicy

In the IndexablePolicy you configure which properties are indexed.

Step 3: Create a block to turn an entity into a document

The last step is to create a block that you add to the IFullIndexMinionPipeline and IIncrementalIndexMinionPipeline to create the SOLR document based on a GiftCard entity.
You can find the code for this block here: InitializeGiftcardsIndexingViewBlock.cs

This block takes a list of GiftCard entities and for each gift card it adds the properties to be indexed to an EntityView.

How do I query the index?

The repository contains a sample command that show how you can query the index:

// Filter order on activation date
var filterQuery = new FilterQuery(new AndFilterNode(new LessThanFilterNode(
        new FieldNameFilterNode("activationdate"),
        new FieldValueFilterNode(endDate
                .ToString(SearchConstants.DateTimeSearchFormat, CultureInfo.InvariantCulture),
            FilterNodeValueType.Date)),
    new GreaterThanFilterNode(new FieldNameFilterNode("activationdate"),
        new FieldValueFilterNode(
            startDate
                .ToString(SearchConstants.DateTimeSearchFormat, CultureInfo.InvariantCulture),
            FilterNodeValueType.Date))));

var scope = SearchScopePolicy.GetPolicyByType(commerceContext, commerceContext.Environment,
    typeof(GiftCard));

var searchResults = await _commander.Command<SearchEntitiesCommand>()
    .Process<GiftCard>(commerceContext, scope.Name, new SearchQuery(), filterQuery)
    .ConfigureAwait(false);

It's easy: create a query, get the relevant scope you want to query and search for entities. This will automatically give you a list of entities.

In Summary

Creating an index for entities gives you much more flexibility in querying.