I have a requirement for filter aggregations using NEST. But since I don't know much about this, I have made the below:
class Program
{
static void Main(string[] args)
{
ISearchResponse<TestReportModel> searchResponse =
ConnectionToES.EsClient()
.Search<TestReportModel>
(s => s
.Index("feedbackdata")
.From(0)
.Size(50000)
.Query(q =>q.MatchAll())
);
var testRecords = searchResponse.Documents.ToList<TestReportModel>();
result = ComputeTrailGap(testRecords);
}
private static List<TestModel> ComputeTrailGap(List<TestReportModel> testRecords)
{
var objTestModel = new List<TestModel>();
var ptpDispositionCodes = new string[] { "PTP" };
var bptpDispositionCodes = new string[] { "BPTP","SBPTP" };
int gapResult = testRecords.Where(w => w.trailstatus == "Gap").Count();
var ptpResult = testRecords.Where(w => ptpDispositionCodes.Contains(w.lastdispositioncode)).ToList().Count();
var bptpResult = testRecords.Where(w => bptpDispositionCodes.Contains(w.lastdispositioncode)).ToList().Count();
objTestModel.Add(new TestModel { TrailStatus = "Gap", NoOfAccounts = gapResult });
objTestModel.Add(new TestModel { TrailStatus = "PTP", NoOfAccounts = ptpResult });
objTestModel.Add(new TestModel { TrailStatus = "BPTP", NoOfAccounts = bptpResult });
return objTestModel;
}
}
DTO
public class TestReportModel
{
public string trailstatus { get; set; }
public string lastdispositioncode { get; set; }
}
public class TestOutputAPIModel
{
public List<TestModel> TestModelDetail { get; set; }
}
public class TestModel
{
public string TrailStatus { get; set; }
public int NoOfAccounts { get; set; }
}
This program works but as can be figure out that we are only accessing the Elastic Search via NEST and the rest of the Aggregations /Filter are done using Lambda.
I would like to perform the entire operation (Aggregations /Filter etc) using NEST framework and put it in the TestModel using filter aggregation.
How can I construct the DSL query inside NEST?
I have been able to make the below so far but the count is zero. What is wrong in my query construction?
var ptpDispositionCodes = new TermsQuery
{
IsVerbatim = true,
Field = "lastdispositioncode",
Terms = new string[] { "PTP" },
};
var bptpDispositionCodes = new TermsQuery
{
IsVerbatim = true,
Field = "lastdispositioncode",
Terms = new string[] { "BPTP" },
};
ISearchResponse<TestReportModel> searchResponse =
ConnectionToES.EsClient()
.Search<TestReportModel>
(s => s
.Index("feedbackdata")
.From(0)
.Size(50000)
.Query(q =>q.MatchAll())
.Aggregations(fa => fa
.Filter("ptp_aggs", f => f.Filter(fd => ptpDispositionCodes))
.Filter("bptp_aggs", f => f.Filter(fd => bptpDispositionCodes))
)
);
I see that you are trying to perform a search on the type of TestReportModel
. The overall structure of your approach seems good enough. However, there is a trouble with the queries that are being attached to your filter containers.
Your TestReportModel
contains two properties trialStatus
and lastdispositioncode
. You are setting the Field
property as description inside your terms
query. This is the reason that you are seeing the counts as zero. The model on which you are performing the search on (in turn the index that you are performing the search on) does not have a property description
and hence the difference. NEST
or Elasticsearch, in this case does not throw any exception. Instead, it returns count zero. Field
value should be modified to lastdispositioncode
.
// Type on which the search is being performed.
// Response is of the type ISearchResponse<TestReportModel>
public class TestReportModel
{
public string trailstatus { get; set; }
public string lastdispositioncode { get; set; }
}
Modified terms
queries are as follows
// Field is "lastdispositioncode" and not "description"
// You may amend the Terms field as applicable
var ptpDispositionCodes = new TermsQuery
{
IsVerbatim = true,
Field = "lastdispositioncode",
Terms = new string[] { "PTP" },
};
var bptpDispositionCodes = new TermsQuery
{
IsVerbatim = true,
Field = "lastdispositioncode",
Terms = new string[] { "BPTP", "SBPTP" },
};
Since it seems that the values to lastdispositioncode
seem to take a single word value (PTP or BPTP from your examples), I believe, it is not going to matter if the field in the doc is analyzed or not. You can further obtain the counts from the ISearchResponse<T>
type as shown below
var ptpDocCount = ((Nest.SingleBucketAggregate)response.Aggregations["ptp_aggs"]).DocCount;
var bptpDocCount = ((Nest.SingleBucketAggregate)response.Aggregations["bptp_aggs"]).DocCount;
QueryContainer qc1 = new QueryContainerDescriptor<TestReportModel>()
.Bool(b => b.Must(m => m.Terms(t => t.Field(f => f.lastdispositioncode.Suffix("keyword"))
.Terms(new string[]{"ptp"}))));
QueryContainer qc2 = new QueryContainerDescriptor<TestReportModel>()
.Bool(b => b.Must(m => m.Terms(t => t.Field(f => f.lastdispositioncode.Suffix("keyword"))
.Terms(new string[]{"bptp", "sbptp"}))));
Now these query containers can be hooked to your aggregation as shown below
.Aggregations(aggs => aggs
.Filter("f1", f => f.Filter(f => qc1))
.Filter("f2", f => f.Filter(f => qc2)))
The queries for aggregations that are generated by the NEST
client in this case look like below
"f1": {
"filter": {
"bool": {
"must": [
{
"terms": {
"lastdispositioncode.keyword": [
"bptp"
]
}
}
]
}
}
}
Also, coming back to the case of search being case-insensitive, Elasticsearch deals with search in a case-insensitive fashion. However, it varies depending on analyzed vs non-analyzed fields. Analyzed fields are tokenized and text fields are by default tokenized. We use the suffix
extension method of NEST
on analyzed fields ideally and to get an exact match on the analyzed field. More about them here
I have made the Terms values to lowercase now to get the result like "ptp" or "bptp". Is there anyway to make this case insensitive. I have tried with the below but to luck var ptpDispositionCodes = new TermsQuery { IsVerbatim = true, Field = "lastdispositioncode".Suffix("keyword"), Terms = new string[] { "ptp" } }; error : Cannot implicitly convert type 'object' to 'Nest.Field'. An explicit conversion exists (are you missing a cast?)
Looks like it is something to do with the mapping. Elastic is case-insensitive (varies between analyzed and non-analyzed fields) and text fields are analyzed by default. What is the mapping in your case? Please show the mapping. Also, for the error you spoke of, you can explicitly typecast (will be adding that as an edit to the answer).
Amended the answer to include keyword search. Notice the use of a query container in the case. You should be able to see the query generated by
NEST
within theDebugInformation
field of the response. Also, you can see if you are able to just query through kibana with whateverterms
query you are trying to filter. Additionally added a reference for context aroundanalyzed
andnon-analyzed
fields.great help....1 have 1 more question. Suppose , I want to first apply Group by (e.g. field1), then sort on field2, and then after taking the latest record(top 1 from every group , I would like to apply the avobe code. I've attempted to make so but somehow could not be able to. will you please help me. I've updated the question
@priyanka.sarkar - Given that the scope (and so the length) of the question is increasing, to keep the post legible and understandable for the other audience, I suggest you post the latest query with respect to sorting as a separate question. What do you think of it?