One Index, Two Indexers, Populate Array

Question

My index looks like this:

public class Content
{
    [SimpleField(IsFilterable = true, IsKey = true)]
    public required string Id { get; set; }

    [SimpleField(IsFilterable = true, IsSortable = true, IsFacetable = true)]
    public required string ContentType { get; set; }

    [SearchableField(IsSortable = true, AnalyzerName = LexicalAnalyzerName.Values.StandardLucene)]
    public required string Name { get; set; }

    [SearchableField(IsSortable = true, AnalyzerName = LexicalAnalyzerName.Values.StandardLucene)]
    public string? Description { get; set; }

    [SimpleField(IsFilterable = true, IsFacetable = true)]
    public ICollection<string> Subscriptions { get; set; } = [];
}

The first indexer is properly working and populates all of the properties except Subscriptions from a content SQL database.

The second indexer is not working as I would like it to. Our subscription data is in a separate database and links via ContentId. So, the query I'm using on the indexer is:

SELECT SC.ContentId AS ContentId
    , S.SubscriptionName AS SubscriptionName
    , SC.ModifyDate AS ModifyDate
FROM dbo.SubscriptionContent SC
INNER JOIN dbo.Subscription S ON S.SubscriptionId = SC.SubscriptionId

My FieldMappings on this index is:

[new FieldMapping("ContentId") { TargetFieldName = nameof(Content.Id) }]

I've attempted to use a Skillset to group by the ContentId and put all of the SubscriptionNames in the Subscriptions Collection(Edm.String).

var shaperInputs = new List<InputFieldMappingEntry>
{
    new InputFieldMappingEntry("inputs")
    {
        Source = "/document/SubscriptionName"
    },
    new InputFieldMappingEntry("groupBy")
    {
        Source = "/document/ContentId"
    }
};

var shaperOutputs = new List<OutputFieldMappingEntry>
{
    new OutputFieldMappingEntry("output")
    {
        TargetName = "groupedSubscriptionNames"
    }
};

var skillset = new SearchIndexerSkillset("content-index-subscription-skillset", new List<SearchIndexerSkill>
{
    new ShaperSkill(shaperInputs, shaperOutputs)
    {
        Name = "groupSubscriptionNames",
        Description = "Group SubscriptionNames by ContentId",
        Context = "/document"
    }
});

Then I add OutputFieldMappings of:

[new FieldMapping("/document/groupedSubscriptionOptionNames") { TargetFieldName = nameof(Content.Subscriptions) }]

None of the Subscriptions get populated. What am I doing wrong?

ScubaSteve · Accepted Answer · 2025-02-10 19:27:36Z

I was able to figure out a solution for this. Instead of using any sort of Skillset, I utilized the jsonArrayToStringCollectionFunction mapping function in the FieldMapping. First I needed to alter my query to get the SubscriptionNames into a string representing an array of strings. Ex: "['Basic', 'Pro', 'Ultimate']".

So, my query became:

SELECT SC.ContentId AS ContentId
  ,'[' + STRING_AGG('"' + S.SubscriptionName + '"', ',') + ']' AS SubscriptionsArr
  ,MAX(SC.ModifyDate) AS ModifyDate
FROM dbo.SubscriptionContent SC
INNER JOIN dbo.Subscription S ON S.SubscriptionId = SC.SubscriptionId
GROUP BY SC.ContentId

Then, in the C#, the field mappings on the indexer became:

new FieldMapping("ContentId") { TargetFieldName = nameof(Content.Id) },
new FieldMapping("SubscriptionsArr") { TargetFieldName = nameof(Content.Subscriptions),
    MappingFunction = new FieldMappingFunction("jsonArrayToStringCollection") }

The "jsonArrayToStringCollection" is a built in Azure mapping function as I linked above. Now my Subscriptions array is getting populated in the index.

UPDATE

While what I have above was working initially, the indexer would fail on subsequent runs if I set the HighWaterMarkChangeDetectionPolicy to be "ModifyDate". I was able to resolve this by wrapping the above query in a database view and altering the datasource to point to this view instead of the table.

Collectives™ on Stack Overflow

One Index, Two Indexers, Populate Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related