Representing relational data in Lucene/Solr index for Segmented lists

Question

Scenario: In Sitecore Experience platform (8.1+) we have a xProfile/Contact, with zero-to-many Sitecore Certifications relation, as an example

The contact Arthur Dent has over the years has earned three Sitecore certifications. In Sitecore his contact profile is therefore represented as follows:

• Contact, [Arthur Dent, identifier: arthur@milliways.com]
  • Cerificate 1, [Sitecore XP 8 Website Development, WDN, 2016]
  • Cerificate 2, [Sitecore Context Marketing Fundamentals, CMF, 2015]
  • Cerificate 3, [Sitecore OMS 1.1 .NET Developer, OMS, 2013]

The datamodel should be used for creating a Segmented list using List Manager, i.e. from using the custom condition: "where the contact has earned a WDN certificate wihtin 2016"

Behind this rule, we are planning on using the Lucene/Solr index to ensure performance and compliancy.

Question: What are the recommended practices for indexing/representing/querying relational (one-to-many) data in Lucene/Solr?

Answer 1

First of all, I don't think it matters whether or not data complies to the relational model. What you need here is just a list of passed certifications associated with a contact record.

In terms of Solr and Lucene, every contact should be represented by a separate document in the index. The list of certification IDs should be stored in a field that you'll be able to query later.

Luckily, Sitecore has a predefined way of achieving this. You don't even have to use a custom index. Instead, you can store custom fields in the contact documents of the Analytics Index.

1. Define a custom contact facet

Certifications should be represented by contact facets—the out-of-the-box storage for custom contact data. Follow this official documentation page:

Create a custom contact facet

2. Index your custom facet

You'll need to implement a custom contactindexable.loadfields pipeline processor and inherit it from ContactIndexableLoadFieldsProcessor. You'll be able to add custom data fields to the index. You'll also need to configure the custom field to be stored in the Analytics index—I suggest that you define an index field contact.Certifications with type="System.GUID". Follow this guide for more details:

Index custom contact facets

Here's an approximate implementation:

public class IndexCertifications : ContactIndexableLoadFieldsProcessor
{
    protected override IEnumerable<IIndexableDataField> GetFields(ContactIndexableLoadFieldsPipelineArgs args)
    {
        var fields = new List<IIndexableDataField>();

        var certifications = // get the list of certifications

        foreach (var certification in certifications)
        {
            fields.Add(new IndexableDataField<Guid>("contact.Certifications", certification.Id));
        }

        return fields;
    }
}

Note that, since Lucene and Solr allow to store several values in the same field, in the code above I am returning multiple field objects with the same name.

3. Query the Analytics index

To be able to query your custom field, you first need to extend the class Sitecore.ContentSearch.Analytics.Models.IndexedContact:

public class BrandIndexedContact : IndexedContact
{
    [IndexField("contact.Certifications")]
    public Guid Certifications { get; set; }
}

Then you can query it like this:

ISearchIndex index = ContentSearchManager.GetAnalyticsIndex();

using (IProviderSearchContext context = index.CreateSearchContext())
{
    var certificationId = new Guid("{certification-id-here}");

    IQueryable<BrandIndexedContact> query =
        context.GetQueryable<BrandIndexedContact>()
            .Where(_ => _.Certifications == certificationId);

    SearchResults<BrandIndexedContact> results = query.GetResults();
}