Need help implementing multiple Or conditions in Nest query

Question

I am attempting to implement the following SQL pseudo-code in Nest ElasticSearch.

I haven't found any similar StackOverflow questions matching this question or in Nest documentation. Appreciate any direction you can provide.

select * 
from curator..published
where accountId = 10
  and ( 
        (publishStatusId = 3 and scheduledDT > '2015-09-01')
        or
        (publishStatusId = 4 and publishedDT > '2015-09-01')
      )

I've created the following ElasticSearch query but am unable to successfully translate it to Nest syntax.

GET curator/published/_search
{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "accountID": 1781
              }
            }
          ],
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "publishStatusID": 4
                    }
                  },
                  {
                    "range": {
                      "publishedDT": {
                        "gte": "2015-09-01T00:00:00.000"
                      }
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "publishStatusID": 3
                    }
                  },
                  {
                    "range": {
                      "scheduleDT": {
                        "gte": "2015-09-01T00:00:00.000"
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

This Nest query is passes a syntax check but only the last "should" condition appears in the resulting ElasticSearch query.

var results = this.Client.Count<Data>(c => c
    .Query(q => q
        .Filtered(f1 => f1
            .Filter(f2 => f2
                .Bool(b => b
                    .Must(
                        f => f.Term(FieldName.AccountID, "10")
                    )
                    .Should(s => s
                        .Bool(b1 => b1
                            .Must(
                                f => f.Term(FieldName.PublishStatusID, "3"),
                                f => f.Range(m => m.OnField(FieldName.ScheduleDT).GreaterOrEquals("2015-09-01"))
                            )
                        )
                    )
                    .Should(s => s
                        .Bool(b1 => b1
                            .Must(
                                f => f.Term(FieldName.PublishStatusID, "4"),
                                f => f.Range(m => m.OnField(FieldName.PublishedDT).GreaterOrEquals("2015-09-01"))
                            )
                        )
                    )
                )
            )
        )
    )
);

This Nest query better matches the original ElasticSearch query but raises the following error on the 2nd Bool: Error 51 'Nest.FilterContainer' does not contain a definition for 'Bool' and no extension method 'Bool' accepting a first argument of type 'Nest.FilterContainer' could be found (are you missing a using directive or an assembly reference?)

var results = this.Client.Count<Data>(c => c
    .Query(q => q
        .Filtered(f1 => f1
            .Filter(f2 => f2
                .Bool(b => b
                    .Must(
                        f => f.Term(FieldName.AccountID, AccountID)
                    )
                    .Should(s => s
                        .Bool(b1 => b1
                            .Must(
                                f => f.Term(FieldName.PublishStatusID, "3"),
                                f => f.Range(m => m.OnField(FieldName.ScheduleDT).GreaterOrEquals("2015-09-01"))
                            )
                        )
                        .Bool(b2 => b2
                            .Must(
                                f => f.Term(FieldName.PublishStatusID, "4"),
                                f => f.Range(m => m.OnField(FieldName.PublishedDT).GreaterOrEquals("2015-09-01"))
                            )
                        )
                    )
                )
            )
        )
    )
);

Russ Cam · Accepted Answer · 2015-09-30 00:08:19Z

Your first query is not far off, just the expression passed to the second Should() needs to be another expression passed to the first Should() (Should() takes a params Func[] filters). Here's the query (I've used dynamic here as the generic type):

void Main()
{
    var settings = new ConnectionSettings(new Uri("http://localhost:9200"));
    var connection = new InMemoryConnection(settings);
    var client = new ElasticClient(connection: connection);

    var docs = client.Count<dynamic>(c => c
        .Query(q => q
            .Filtered(f1 => f1
                .Filter(f2 => f2
                    .Bool(b => b
                        .Must(
                            f => f.Term(FieldName.AccountID, "10")
                        )
                        .Should(s => s
                            .Bool(b1 => b1
                                .Must(
                                    f => f.Term(FieldName.PublishStatusID, "3"),
                                    f => f.Range(m => m.OnField(FieldName.ScheduleDT).GreaterOrEquals("2015-09-01"))
                                )
                            ),
                                s => s
                            .Bool(b1 => b1
                                .Must(
                                    f => f.Term(FieldName.PublishStatusID, "4"),
                                    f => f.Range(m => m.OnField(FieldName.PublishedDT).GreaterOrEquals("2015-09-01"))
                                )
                            )
                        )
                    )
                )
            )
        )
    );

    Console.WriteLine(Encoding.UTF8.GetString(docs.RequestInformation.Request));
}

public static class FieldName
{
    public static string AccountID = "AccountID";
    public static string ScheduleDT = "ScheduleDT";
    public static string PublishedDT = "PublishedDT";
    public static string PublishStatusID = "PublishStatusID";
}

produces

{
  "query": {
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "AccountID": "10"
              }
            }
          ],
          "should": [
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "PublishStatusID": "3"
                    }
                  },
                  {
                    "range": {
                      "ScheduleDT": {
                        "gte": "2015-09-01"
                      }
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "must": [
                  {
                    "term": {
                      "PublishStatusID": "4"
                    }
                  },
                  {
                    "range": {
                      "PublishedDT": {
                        "gte": "2015-09-01"
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

This matches your query DSL above

Collectives™ on Stack Overflow

Need help implementing multiple Or conditions in Nest query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related