Compound query in Elasticsearch.NET

Question

Imagine I have an index with a bunch of Order objects in it

class Order
{
    int CustomerId { get; set; }
    DateTime OrderDate { get; set; }
    // Other fields
}

For a particular customer ID, I am able to find out the last order that the customer made with the following query:

IElasticClient client;

async Task<Order> GetLastOrder(int customerId)
{
    var searchResponse = await client.SearchAsync<Order>(s => s
        .Query(q => q
            .Term(f => f
                .Field(e => e.CustomerId)
                .Term(customerId)) && q
            .DateRange(r => r
                .Field(e => e.OrderDate)
                .LessThan(DateMath.Now)))
        .Sort(o => o
            .Descending(e => e.OrderDate))
        .Size(1));

    return searchResponse.ApiCall.Success
        ? searchResponse.Documents.First()
        : null;
}

However, in order to support the data loader pattern, I want to query the last order made by multiple customers, given by a collection of customer IDs. I started off like this:

async Task<IDictionary<int, Order>> GetLastOrders(IEnumerable<int> customerIds)
{
    var searchResponse = await client.SearchAsync<Order>(s => s
        .Query(q => q
            .Terms(f => f
                .Field(e => e.CustomerId)
                .Terms(customerIds)) && q
            .DateRange(r => r
                .Field(e => e.OrderDate)
                .LessThan(DateMath.Now)))
        .Sort(o => o
            .Descending(e => e.OrderDate))
        .Size(1));

    return searchResponse.ApiCall.Success
        ? searchResponse.Documents.ToDictionary(i => i.CustomerId)
        : new Dictionary<string, Order>();
}

Unfortunately this does not work, as it only returns the first record of entire query, which will only return a single Order. How can I modify this query to return one Order per customer ID?

Are you trying to use ElasticSearch as a database? There's a world of pain down that path. I recommend using databases for databases, and ElasticSearch for search. — Stephen Cleary
– Stephen Cleary, Commented Mar 5, 2020 at 1:00
@StephenCleary not really. My actual problem has nothing to do with orders, but because of IT policy I can’t paste my actual code. This is a suitable analog. — Steztric
– Steztric, Commented Mar 5, 2020 at 7:50

jaspreet chahal · Accepted Answer · 2020-03-05 05:15:57Z

1

Collapse can be used to return top most record for a field

await _client.SearchAsync<Order>(s => s
                                        .Query(q => q
                                        .Terms(f => f
                                        .Field(e => e.CustomerId)
                                        .Terms(cuIds)) && q
                                        .DateRange(r => r
                                                         .Field(e => e.OrderDate)
                                                         .LessThan(DateMath.Now)))
                                        .Sort(o => o
                                              .Descending(e => e.OrderDate))
                                        .Collapse(c => c.Field(e => e.CustomerId))
                                        .Size(10)
                                        );

corresponding query in DSL

{
  "collapse": {
    "field": "customerId"
  },
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "customerId": [
              1,
              2
            ]
          }
        },
        {
          "range": {
            "orderDate": {
              "lt": "now"
            }
          }
        }
      ]
    }
  },
  "size": 10,
  "sort": [
    {
      "orderDate": {
        "order": "desc"
      }
    }
  ]
}

edited Mar 5, 2020 at 5:15

answered Mar 5, 2020 at 4:59

jaspreet chahal

9,1392 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steztric Over a year ago

I wanted to test it before I confirm. It does indeed! Thanks so much!

jaspreet chahal Over a year ago

Glad could be of help

Collectives™ on Stack Overflow

Compound query in Elasticsearch.NET

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