Elastic Search Aggregation and Complex Query

Question

I have created the index

PUT ten2
{
    "mappings": {
        "documents": {
            "properties": {
                "title": {
                    "type": "text",
                    "fields": {
                        "raw": {
                            "type": "keyword"
                        }
                    }
                },"uid": {
                    "type": "text",
                    "fields": {
                        "raw": {
                            "type": "keyword"
                        }
                    }
                },
                "publish_details": {
                    "type": "nested",
                    "properties": {
                        "environment": {
                            "type": "keyword"
                        },
                        "locale": {
                            "type": "keyword"
                        },
                        "time": {
                            "type": "date"
                        },
                        "version": {
                            "type": "integer"
                        }
                    }
                }
            }
        }
    }
}

and added documents into it. here is the list of documents:

   [{
    "_index": "ten2",
    "_type": "documents",
    "_id": "blt69b62b48bbed1fb6_en-us",
    "_source": {
        "publish_details": [{
                "environment": "blt603fe91adbdcff66",
                "time": "2020-06-24T12:11:25.276Z",
                "locale": "en-us",
                "user": "bltaadab2f531206e9d",
                "version": 1
            },
            {
                "environment": "blt603fe91adbdcff66",
                "time": "2020-06-24T12:11:25.276Z",
                "locale": "hi-in",
                "user": "bltaadab2f531206e9d",
                "version": 1
            }
        ],
        "title": "Entry 1",
        "uid": "blt69b62b48bbed1fb6"
    }
},
{
    "_index": "ten2",
    "_type": "documents",
    "_id": "blt69b62b48bbed1fb6_mr-in",
    "_source": {
        "publish_details": [{
            "environment": "blt603fe91adbdcff66",
            "time": "2020-06-24T12:12:35.467Z",
            "locale": "mr-in",
            "user": "bltaadab2f531206e9d",
            "version": 1
        }],
        "title": "Entry 3",
        "uid": "blt69b62b48bbed1fb6"
    }
},
{
    "_index": "ten2",
    "_type": "documents",
    "_id": "blt4044c5198122a3ed_en-us",
    "_source": {
        "publish_details": [{
            "environment": "blt603fe91adbdcff66",
            "time": "2020-06-24T12:10:46.430Z",
            "locale": "en-us",
            "user": "bltaadab2f531206e9d",
            "version": 1
        },{
            "environment": "blt603fe91adbdcff6690",
            "time": "2020-06-24T12:10:46.430Z",
            "locale": "en-us",
            "user": "bltaadab2f531206e9d",
            "version": 1
        }],
        "title": "Entry 10",
        "uid": "blt4044c5198122a3ed"
    }
}

] and I want the following result

    [
 {
    "_index": "ten2",
    "_type": "documents",
    "_id": "blt4044c5198122a3ed_en-us",
    "_source": {
        "publish_details": [{
            "environment": "blt603fe91adbdcff66",
            "time": "2020-06-24T12:10:46.430Z",
            "locale": "en-us",
            "user": "bltaadab2f531206e9d",
            "version": 1
        },{
            "environment": "blt603fe91adbdcff6690",
            "time": "2020-06-24T12:10:46.430Z",
            "locale": "en-us",
            "user": "bltaadab2f531206e9d",
            "version": 1
        }],
        "title": "Entry 10",
        "uid": "blt4044c5198122a3ed"
    }
}

]

I am using the following query to get the result

GET ten2/_search
{
    "query": {
        "bool": {
            "must": [{
                "bool": {
                    "must_not": [{
                        "bool": {
                            "must": [{
                                "nested": {
                                    "path": "publish_details",
                                    "query": {
                                        "term": {
                                        "publish_details.environment": "blt603fe91adbdcff66"
                                        }
                                    }
                                }
                            }, {
                                "nested": {
                                    "path": "publish_details",
                                    "query": {
                                        "term": {
                                            "publish_details.locale": "en-us"
                                        }
                                    }
                                }
                            }, {
                                "nested": {
                                    "path": "publish_details",
                                    "query": {
                                        "term": {
                                            "publish_details.locale": "hi-in"
                                        }
                                    }
                                }
                            }, {
                                "nested": {
                                    "path": "publish_details",
                                    "query": {
                                        "term": {
                                            "publish_details.locale": "mr-in"
                                        }
                                    }
                                }
                            }]
                        }
                    }]
                }
            }
        }
    }
}

kindly help me a query to get expected result. First two dicuemtns having same uid only publish_details.locale is different.I am using must query within must_not to get result, currently I am getting all three documents but I want only last one. I have million documwnts.

Is it related to your other question? can you explain the logic little more? — Gibbs
uid of the first two documents are the same that means uid: blt69b62b48bbed1fb6 is published on all 3 locales. so I don't want that document in the result. — Suraj Dalvi

ESCoder ESCoder · Accepted Answer · 2020-06-25T11:35:59

To know more about Bool queries refer to this official documentation

Adding a working example with your mapping, index data, and with the search query

Search Query:

    {
  "query": {
    "nested": {
      "path": "publish_details",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "publish_details.locale": "en-us"
              }
            }
          ],
          "must_not": [
            {
              "match": {
                "publish_details.environment": "blt603fe91adbdcff66"
              }
            },
            {
              "match": {
                "publish_details.locale": "hi-in"
              }
            },
            {
              "match": {
                "publish_details.locale": "mr-in"
              }
            }
          ]
        }
      },
      "inner_hits": {
        
      }
    }
  }
}

Search Result :

"hits": [
        {
            "_index": "test",
            "_type": "_doc",
            "_id": "3",
            "_score": 0.53899646,
            "_source": {
                "publish_details": [
                    {
                        "environment": "blt603fe91adbdcff66",
                        "time": "2020-06-24T12:10:46.430Z",
                        "locale": "en-us",
                        "user": "bltaadab2f531206e9d",
                        "version": 1
                    },
                    {
                        "environment": "blt603fe91adbdcff6690",
                        "time": "2020-06-24T12:10:46.430Z",
                        "locale": "en-us",
                        "user": "bltaadab2f531206e9d",
                        "version": 1
                    }
                ],
                "title": "Entry 10",
                "uid": "blt4044c5198122a3ed"
            },
            "inner_hits": {
                "publish_details": {
                    "hits": {
                        "total": {
                            "value": 1,
                            "relation": "eq"
                        },
                        "max_score": 0.53899646,
                        "hits": [
                            {
                                "_index": "test",
                                "_type": "_doc",
                                "_id": "3",
                                "_nested": {
                                    "field": "publish_details",
                                    "offset": 1
                                },
                                "_score": 0.53899646,
                                "_source": {
                                    "environment": "blt603fe91adbdcff6690",
                                    "time": "2020-06-24T12:10:46.430Z",
                                    "locale": "en-us",
                                    "user": "bltaadab2f531206e9d",
                                    "version": 1
                                }
                            }
                        ]
                    }
                }
            }
        }
    ]

To know more about inner hits refer to this documentation

The above query returns only the third document, thus satisfying the conditions of the search query. In the Inner Hits, only one part of the third document is returning, and the part which is matching blt603fe91adbdcff66 is discarded.

Elastic Search Aggregation and Complex Query

1 Answers