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 SubscriptionName
s 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?
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 SubscriptionName
s 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?
1 Answer
Reset to default 0I 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 SubscriptionName
s 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.