How to create query to match child collection attributes using NEST .net client library for ElasticSearch?

I have following 4 documents located at http://xyzserver:9200/mydocs/brands in ElasticSearch 7.3
Each document represents some details of a Brand. A brand can be associated with multiple Groups and can be active for 0 or more groups.

I have 4 documents loaded.

{
	Id: 100,
	Name: 'Diet Coke',
	Groups:
	[{
		GroupId: 200,
		GroupName: 'US East',
		IsActive: true
	 },
	 {
		GroupId: 201,
		GroupName: 'US West',
		IsActive: true
	 },
	 {
		GroupId: 202,
		GroupName: 'US South',
		IsActive: false
	 }
	]
}
{
	Id: 110,
	Name: 'Coke',
	Groups:
	[{
		GroupId: 200,
		GroupName: 'US East',
		IsActive: false
	 },
	 {
		GroupId: 201,
		GroupName: 'US West',
		IsActive: true
	 },
	 {
		GroupId: 202,
		GroupName: 'US South',
		IsActive: true
	 }
	]
}
{
	Id: 120,
	Name: 'Coke with Lime',
	Groups:
	[{
		GroupId: 200,
		GroupName: 'US East',
		IsActive: true
	 },
	 {
		GroupId: 201,
		GroupName: 'US West',
		IsActive: true
	 },
	 {
		GroupId: 202,
		GroupName: 'US South',
		IsActive: true
	 }
	]
}
{
	Id: 130,
	Name: 'Cola',
	Groups:
	[{
		GroupId: 300,
		GroupName: 'Europe East',
		IsActive: true
	 },
	 {
		GroupId: 400,
		GroupName: 'Mexico',
		IsActive: true
	 },
	 {
		GroupId: 410,
		GroupName: 'Brazile',
		IsActive: true
	 }
	]
}

I am searching for "Coke" and which is "Active" for both "200 - US East Group" && "201 - US West Group" groups. Assume that I have a search criteria type

public class BrandSearchCriteria {
    public string Keyword {get; set;}
	public IEnumerable<int> GroupIds {get; set;} = new List<int>();
	public bool? IsActive {get; set;} //true if looking for only active items, false if looking for inactive items, null if looking for both
}

searchCriteria = new BrandSearchCriteria
{
	Keyword = "Coke",
	GroupIds = new List<int> { 200, 201 },
	IsActive = true
}

How do I create a query using NEST library? This is what I have so far

QueryContainerDescriptor<Brand> queryDescriptor = new QueryContainerDescriptor<Brand>();
queryDescriptor.Bool(b =>
	b.Must(q =>
		q.Match(m => m.Field(f => f.Name).Fuzziness(Fuzziness.Auto).Query(searchCriteria.KeyWord) &&
		q.Terms(t => t.Field("Groups.GroupId").Terms<int>(searchCriteria.GroupIds)) &&
		q.Term(t => t.Field("Groups.IsActive").Value(searchCriteria.IsActive.ToString()))
	  )
	);

I am suppose to get only two documents back with Id 100 (Diet Coke) and 120 (Coke with Lime) as these two documents are the only two active for both Groups "200 - US East Group" && "201 - US West Group".

But above query is bring me 3 documents back 100 (Diet Coke), 110 (Coke), 120 (Coke with Lime). Even thou document 110 (Coke) is inactive for Group "201 - US West Group", it is still getting included in the results.

I just started learning NEST library usage and could not figure out how to formulate the query to retrieve the results. Any help will be greatly appreciated.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.