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

Question

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.

UPDATE 1: Here is document mapping

{
    "mydocs": {
        "mappings": {
            "properties": {
                "Groups": {
                    "properties": {
                        "GroupId": {
                            "type": "long"
                        },
                        "GroupName": {
                            "type": "text"
                        },
                        "IsActive": {
                            "type": "boolean"
                        }
                    }
                },
                "Id": {
                    "type": "long"
                },
                "Name": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                }
            }
        }
    }
}

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.

Answer 1

First of all I believe you need to change the mappings. specifically Groups should be nested

{
    "mappings": {
        "properties": {
            "Groups": {
              "type": "nested", 
                "properties": {
                    "GroupId": {
                        "type": "long"
                    },
                    "GroupName": {
                        "type": "text"
                    },
                    "IsActive": {
                        "type": "boolean"
                    }
                }
            },
            "Id": {
                "type": "long"
            },
            "Name": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            }
        }
    }
}

Then the nest code based on yours would look like:

// preparing the query
var groupIds = new[] { 200, 201 };
Func<QueryContainerDescriptor<Brand>, int, QueryContainer> nestedQuery = (q, groupId) => q.Nested(n => n.Path("Groups").Query(nq => nq.Bool(nb => nb.Filter(
        fq => fq.Term(t => t.Field("Groups.GroupId").Value(groupId)),
        fq => fq.Term(t => t.Field("Groups.IsActive").Value(true))))
    )
);
var query = groupIds.Select(groupId => nestedQuery(new QueryContainerDescriptor<Brand>(), groupId)).ToList();
query.Add(new QueryContainerDescriptor<Brand>().Match(m => m.Field("Name").Fuzziness(Fuzziness.Auto).Query("coke")));

var queryDescriptor = new QueryContainerDescriptor<Brand>();
queryDescriptor.Bool(b => b.Must(query.ToArray()));

Few suggestions:

• Use Expression instead of strings in methods like Field

• boolean type on ES side is bool on C#. not sure why you are converting it to string.

• Use filters whenever you don't need scoring, basically yes/no answer.

• I would've used painless script here. Would have looked cleaner