Elasticsearch multiple filter conditions for nested array

Question

Pobably this question was answered somewhere but i cannot find it . so I am asking for help I have a nested list of properties for my product model. something like this

[
    {
        ID: "Product1",
        ...
        Properties: [
            { "Source": "Color", Value: "green"},
            { "Source": "Size",  Value: "2"},
        ]
    },
    {
        ID: "Product2",
        ...
        Properties: [
            { "Source": "Color", Value: "blue"},
            { "Source": "Size", Value: "2"},
        ]
    },
    {
        ID: "Product3",
        ....
        Properties: [
            { "Source": "Color", Value: "red"},
            { "Source": "Size", Value: "1"},
        ]
    },
]

Index mapping:

"properties" : {
        "type" : "nested",
        "properties" : {
          "source" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          },
          "value" : {
            "type" : "text",
            "fields" : {
              "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
              }
            }
          }
        }
      },

And I need my search query to find only the products that have color green or blue and size 2.

i created this query for my Search request but it will return an empty result.

       new Nest.NestedQuery
            {
                Path = new Nest.Field("properties"),
                Query = new Nest.BoolQuery() {
                    Must = new Nest.QueryContainer[] {
                      new Nest.BoolQuery()
                      {
                          Must = new Nest.QueryContainer[] {
                            new Nest.TermQuery()
                            {
                                Field = new Nest.Field("properties.source.keyword"),
                                Value = "Color"
                            },
                            new Nest.TermsQuery()
                            {
                                Field = new Nest.Field("properties.value.keyword"),
                                Terms = new[] { "green", "blue"}
                            }
                         }
                      }
                   },
                   new Nest.BoolQuery()
                      {
                          Must = new Nest.QueryContainer[] {
                            new Nest.TermQuery()
                            {
                                Field = new Nest.Field("properties.source.keyword"),
                                Value = "Size"
                            },
                            new Nest.TermsQuery()
                            {
                                Field = new Nest.Field("properties.value.keyword"),
                                Terms = new[] { "2"}
                            }
                         }
                      }
                   }
                }
            }

Can you please help me what did I used wrong by building this kind of query? thank you

Rob · Accepted Answer · 2019-08-08 19:38:05Z

Try to wrap each must clause with the nested query:

var query = new BoolQuery
{
    Must = new QueryContainer[]
    {
        new NestedQuery
        {
            Path = "properties",
            Query = new BoolQuery()
            {
                Must = new QueryContainer[]
                {
                    new TermQuery()
                    {
                        Field = new Nest.Field("properties.source.keyword"),
                        Value = "Color"
                    },
                    new TermsQuery()
                    {
                        Field = new Nest.Field("properties.value.keyword"),
                        Terms = new[] { "green", "blue"}
                    }
                }
            }
        },
        new NestedQuery
        {
            Path = "properties",
            Query = new BoolQuery()
            {
                Must = new QueryContainer[]
                {
                    new TermQuery()
                    {
                        Field = new Nest.Field("properties.source.keyword"),
                        Value = "Size"
                    },
                    new TermsQuery()
                    {
                        Field = new Nest.Field("properties.value.keyword"),
                        Terms = new[] {"2"}
                    }
                }
            }
        }
    }
};

full working example

class Program
{
    public class Document
    {
        public int Id { get; set; }
        [Nested]
        public List<Property> Properties { get; set; }
    }

    public class Property
    {
        public string Source { get; set; }
        public string Value { get; set; }

        public override string ToString() => $"Source: {Source} Value: {Value}";
    }

    static async Task Main(string[] args)
    {
        var pool = new SingleNodeConnectionPool(new Uri("http://localhost:9200"));
        var connectionSettings = new ConnectionSettings(pool);
        connectionSettings.DefaultIndex("documents");
        connectionSettings.DisableDirectStreaming();
        connectionSettings.PrettyJson();

        var client = new ElasticClient(connectionSettings);

        var deleteIndexResponse = await client.Indices.DeleteAsync("documents");
        var createIndexResponse = await client.Indices.CreateAsync("documents", d => d
            .Map(m => m.AutoMap<Document>()));

        var indexDocument = await client
            .IndexDocumentAsync(new Document
            {
                Id = 1, 
                Properties = new List<Property>
                {
                    new Property {Source = "Color", Value = "green"},
                    new Property {Source = "Size", Value = "2"},
                }
            });
        indexDocument = await client
            .IndexDocumentAsync(new Document
            {
                Id = 2, 
                Properties = new List<Property>
                {
                    new Property {Source = "Color", Value = "blue"},
                    new Property {Source = "Size", Value = "2"},
                }
            });
        indexDocument = await client
            .IndexDocumentAsync(new Document
            {
                Id = 3, 
                Properties = new List<Property>
                {
                    new Property {Source = "Color", Value = "red"},
                    new Property {Source = "Size", Value = "1"},
                }
            });

        var refreshAsync = client.Indices.RefreshAsync();

        var query = new BoolQuery
        {
            Must = new QueryContainer[]
            {
                new NestedQuery
                {
                    Path = "properties",
                    Query = new BoolQuery()
                    {
                        Must = new QueryContainer[]
                        {
                            new TermQuery()
                            {
                                Field = new Nest.Field("properties.source.keyword"),
                                Value = "Color"
                            },
                            new TermsQuery()
                            {
                                Field = new Nest.Field("properties.value.keyword"),
                                Terms = new[] {"green", "blue"}
                            }
                        }
                    }
                },
                new NestedQuery
                {
                    Path = "properties",
                    Query = new BoolQuery()
                    {
                        Must = new QueryContainer[]
                        {
                            new TermQuery()
                            {
                                Field = new Nest.Field("properties.source.keyword"),
                                Value = "Size"
                            },
                            new TermsQuery()
                            {
                                Field = new Nest.Field("properties.value.keyword"),
                                Terms = new[] {"2"}
                            }
                        }
                    }
                }
            }
        };

        var response = client.Search<Document>(s => s.Query(q => query));

        foreach (var document in response.Documents)
        {
            Console.WriteLine($"Id: {document.Id}");
            document.Properties.ForEach(Console.WriteLine);
            Console.WriteLine();
        }
    }
}

Prints:

Id: 1
Source: Color Value: green
Source: Size Value: 2

Id: 2
Source: Color Value: blue
Source: Size Value: 2

Hope that helps.

Thank you, this solution looks like to work as i wanted. will give it a few test runs

Collectives™ on Stack Overflow

Elasticsearch multiple filter conditions for nested array

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related