Elasticsearch aggregations on nested inner hits

Question

I got a large amount of data in Elasticsearch. My douments have a nested field called "records" that contains a list of objects with several fields.

I want to be able to query specific objects from the records list, and therefore I use the inner_hits field in my query, but It doesn't help because aggregation uses size 0 so no results are returned.

I didn't succeed to make an aggregation work only for inner_hits, as aggregation returns results for all the objects inside records no matter the query.

This is the query I am using: (Each document has first_timestamp and last_timestamp fields, and each object in the records list has a timestamp field)

curl -XPOST 'localhost:9200/_msearch?pretty' -H 'Content-Type: application/json' -d'    
{
    "index":[
        "my_index"
    ],
    "search_type":"count",
    "ignore_unavailable":true
}
{
    "size":0,
    "query":{
        "filtered":{
             "query":{
                 "nested":{
                     "path":"records",
                     "query":{
                         "term":{
                             "records.data.field1":"value1"
                         }
                     },
                     "inner_hits":{}
                 }
             },
             "filter":{
                 "bool":{
                     "must":[
                     {
                         "range":{
                             "first_timestamp":{
                                 "gte":1504548296273,
                                 "lte":1504549196273,
                                 "format":"epoch_millis"
                             }
                         }
                     }
                     ],
                 }
             }
         }
     },
     "aggs":{
         "nested_2":{
             "nested":{
                 "path":"records"
             },
             "aggs":{
                 "2":{
                     "date_histogram":{
                          "field":"records.timestamp",
                          "interval":"1s",
                          "min_doc_count":1,
                          "extended_bounds":{
                              "min":1504548296273,
                              "max":1504549196273
                          }
                     }
                }
           }
      }
   }
}'

I find funny that the "modern" JSON syntax replaced the "old school" SQL in querying data. Well, writing such JSONs sucks. In SQL, you are looking at one JOIN and one GROUP BY. There is a different underlying technology in ElasticSearch, but sorry, ElasticSearch can be close to minus infinity at the topic "Easy to use". — Razvan P

Eli Eli · Accepted Answer · 2017-09-05T08:04:51

Your query is pretty complex. To be short, here is your requested query:

{
  "size": 0,
  "aggregations": {
    "nested_A": {
      "nested": {
        "path": "records"
      },
      "aggregations": {
        "bool_aggregation_A": {
          "filter": {
            "bool": {
              "must": [
                {
                  "term": {
                    "records.data.field1": "value1"
                  }    
                }
              ]
            }
          },
          "aggregations": {
            "reverse_aggregation": {
              "reverse_nested": {},
              "aggregations": {
                "bool_aggregation_B": {
                  "filter": {
                    "bool": {
                      "must": [
                        {
                          "range": {
                            "first_timestamp": {
                              "gte": 1504548296273,
                              "lte": 1504549196273,
                              "format": "epoch_millis"
                            }
                          }
                        }
                      ]
                    }
                  },
                  "aggregations": {
                    "nested_B": {
                      "nested": {
                        "path": "records"
                      },
                      "aggregations": {
                        "my_histogram": {
                          "date_histogram": {
                            "field": "records.timestamp",
                            "interval": "1s",
                            "min_doc_count": 1,
                            "extended_bounds": {
                              "min": 1504548296273,
                              "max": 1504549196273
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Now, let me explain every step by aggregations' names:

size: 0 -> we are not interested in hits, only aggregations
nested_A -> data.field1 is under records so we dive our scope to records
bool_aggregation_A -> filter by data.field1: value1
reverse_aggregation -> first_timestamp is not in nested document, we need to scope out from records
bool_aggregation_B -> filter by first_timestamp range
nested_B -> now, we scope again into records for timestamp field (located under records)
my_histogram -> finally, aggregate date histogram by timestamp field

Elasticsearch aggregations on nested inner hits

2 Answers