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.

could you share the mappings for the index in question?

Andrey Borisko
– Andrey Borisko

2019-09-12 14:29:46 +00:00
Commented Sep 12, 2019 at 14:29 — Andrey Borisko
– Andrey Borisko, Commented Sep 12, 2019 at 14:29
@AndreyBorisko, I have just updated the mapping (Update 1)

Vinod
– Vinod

2019-09-12 17:32:24 +00:00
Commented Sep 12, 2019 at 17:32 — Vinod
– Vinod, Commented Sep 12, 2019 at 17:32

Andrey Borisko · Accepted Answer · 2019-09-12 19:49:35Z

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

Thank you @AndreyBorisko. Really appreciate your insights. I am going to explore painless script option.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related