Elasticsearch query on specific key

Suppose I have these 3 docs in Elasticsearch

{
    id: "1",
    eventName: "ShipmentLoaded"
},
{
    id: "1",
    eventName: "ShipmentPlanned"
},
{
    id: "2",
    eventName: "ShipmentLoaded"
}

I want to retrieve the docs where ids are having docs with eventName=ShipmentLoaded and Not having other docs with eventName=ShipmentPlanned
so the query will return only the last document with id=2 because it does have doc with eventName=ShipmentLoaded and does not have other doc with eventName=ShipmentPlanned

what kind of query that we can use to achieve this?

Hi @mostafaelsayed

I don't know how your mapping but if eventName is a keyword you can use Term query.

{
  "query": {
    "term": {
      "eventName": {
        "value": "ShipmentLoaded"
      }
    }
  }
}

Hi @RabBit_BR This will not work because it will also retrieve the doc with id=1 but I don't want to retrieve it because it has another doc with eventName=ShipmentPlanned

Sorry, I read it wrong.

Do you need the doc with id:2 or the docs "eventName": "ShipmentLoaded"?
Perhaps Field Collapsing could be an alternative.

What you are describing would be a join between records, which Elasticsearch does not support. You could however create a separate index using a transform where there is one document per id, which could likely support your query.

Hi @RabBit_BR as mentioned by @Christian_Dahlqvist I basically want a join query where eventName: ShipmentLoaded and for those documents ids we don't have another documents with eventName: ShipmentPlanned

Thanks @Christian_Dahlqvist I guess this will be a way to achieve this or another way from the application side by making two queries

That may work as well.