Combining result sets from two or more queries

Elastic Stack Elasticsearch
1

I'm trying to compile a DSL query to satisfy the following requirement
(test case) but I can't:

Model:

Customer documents stored in ES, each customer has nested objects of
type Invoice and each invoice has nested objects of type InvoiceLine
.

The only relevant fields are Invoice.InvoiceDate and
InvoiceLine.ProductNumber.

The query I'm trying to write must return all customers who:

  1. Does not have an Invoice in the last year (simple range filter)
    AND
  2. Have bought ProductNumber 123,456 at some point in time (simple in
    filter)

I managed to construct a query (both in Java API and in DSL JSON) which
will find customer who bought the specified products in the last year
(which is not what I describe above).
In traditional SQL, I would write a query like:

Select * from Customer C
Where
Exists (Select 1 from Invoice I Where I.InvoiceDate Between 'xx' and 'yy'
AND I.CustomerId = C.CustomerId)
And
Exists (Select 1 from Invoice I INNER JOIN InvoiceLine IL ON I.InvoiceId =
IL.InvoiceId Where I.CustomerId = C.CustomerId AND IL.ProductNumber IN
('123','456'))

Can this be achieved in ES query/filter DSL?

My only alternative would be to perform two queries and perform the
intersection client-side, but I'd like to avoid this solution.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/f232cc5e-947a-4eb5-b492-abe50b05b1b3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

1 Like