How to get only matched documents from ElasticSearch for a nested array field

Question

I am using an array field in elastic search and this array contains multiple JSON documents. Like this:

{
  "imei": 358739050280669,
  "date": "2018-02-20",
  "id": 86739126,
  "totalData": [
    {
      "gpsdt": "2018-02-20",
      "satno": 0,
      "analog3": -1,
      "digital1": 0,
      "digital2": 1,
      "digital3": 1,
      "digital4": 2,
      "lastgpsdt": "2018-02-20T11:54:00",
      "longitude": 78.081218,
      "odometer": 0,
      "intbatlevel": 6,
      "odo": 0,
      "latitude": 29.951449,
      "srtangle": 62,
      "analog4": 13,
      "speed": 0,
      "analog2": -1,
      "analog1": 9,
      "extbatlevel": 0
    },
    {
      "gpsdt": "2018-02-20",
      "speed": 22,
      "satno": 0,
      "digital1": 0,
      "digital2": 1,
      "digital3": 1,
      "digital4": 2,
      "lastgpsdt": "2018-02-20T22:48:00",
      "longitude": 78.062898,
      "odometer": 0,
      "intbatlevel": 6,
      "odo": 113,
      "latitude": 29.948898,
      "srtangle": 67,
      "analog4": 12,
      "analog3": -1,
      "analog2": -1,
      "analog1": 8,
      "extbatlevel": 0
    }
  ]
}

Now I want to apply a filter on "imei" and a date range filter on "lastgpsdt" field and in output I want only those documents which matches to applied filter.

For example: I have to get data for imei no 358739050280669 and date range (field name is lastgpsdt) between 2018-02-20T10:54:00 and 2018-02-20T12:54:00

So it should return only one document (according to given data) from totalData array field.

Please suggest me a query to achieve this.

Output should be like below:

{
  "imei": 358739050280669,
  "date": "2018-02-20",
  "id": 86739126,
  "totalData": [
    {
      "gpsdt": "2018-02-20",
      "satno": 0,
      "analog3": -1,
      "digital1": 0,
      "digital2": 1,
      "digital3": 1,
      "digital4": 2,
      "lastgpsdt": "2018-02-20T11:54:00",
      "longitude": 78.081218,
      "odometer": 0,
      "intbatlevel": 6,
      "odo": 0,
      "latitude": 29.951449,
      "srtangle": 62,
      "analog4": 13,
      "speed": 0,
      "analog2": -1,
      "analog1": 9,
      "extbatlevel": 0
    }
  ]
}

Thanks In Advance.

Raj, can you please share the mapping? it's very important to check whether you are using a nested type or just a regular object for the totalData field. — panchicore
– panchicore, Commented Jul 6, 2018 at 13:04
Thanks @panchicore Sir for your response , I am using totalData field as a nested type. — Rj1
– Rj1, Commented Jul 6, 2018 at 15:49

Nikolay Vasiliev · Accepted Answer · 2018-07-08 08:59:45Z

You can achieve this by using inner hits parameter of nested search query:

In many cases, it’s very useful to know which inner nested objects (in the case of nested) or children/parent documents (in the case of parent/child) caused certain information to be returned. The inner hits feature can be used for this. This feature returns per search hit in the search response additional nested hits that caused a search hit to match in a different scope.

If you have a mapping like this:

PUT /mygps
{
  "mappings": {
    "doc": {
      "properties": {
        "date": {
          "type": "date"
        },
        "id": {
          "type": "long"
        },
        "imei": {
          "type": "long"
        },
        "totalData": {
          "type": "nested", 
          "properties": {
            "analog1": {
              "type": "long"
            },
            "analog2": {
              "type": "long"
            },
            "analog3": {
              "type": "long"
            },
            "analog4": {
              "type": "long"
            },
            "digital1": {
              "type": "long"
            },
            "digital2": {
              "type": "long"
            },
            "digital3": {
              "type": "long"
            },
            "digital4": {
              "type": "long"
            },
            "extbatlevel": {
              "type": "long"
            },
            "gpsdt": {
              "type": "date"
            },
            "intbatlevel": {
              "type": "long"
            },
            "lastgpsdt": {
              "type": "date"
            },
            "latitude": {
              "type": "float"
            },
            "longitude": {
              "type": "float"
            },
            "odo": {
              "type": "long"
            },
            "odometer": {
              "type": "long"
            },
            "satno": {
              "type": "long"
            },
            "speed": {
              "type": "long"
            },
            "srtangle": {
              "type": "long"
            }
          }
        }
      }
    }
  }
}

The query might look like this:

POST /mygps/doc/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "imei": "358739050280669"
          }
        },
        {
          "nested": {
            "path": "totalData",
            "query": {
              "range": {
                "totalData.lastgpsdt": {
                  "gte": "2018-02-20T10:54:00",
                  "lte": "2018-02-20T12:54:00"
                }
              }
            },
            "inner_hits": {}
          }
        }
      ]
    }
  },
  "_source": ["imei", "date", "id"]
}

Which will produce the following output:

{
  "hits": {
    "total": 1,
    "max_score": 2,
    "hits": [
      {
        "_index": "mygps",
        "_type": "doc",
        "_id": "AWR5Em0m6jWoKaNfOwDA",
        "_score": 2,
        "_source": {
          "date": "2018-02-20",
          "imei": 358739050280669,
          "id": 86739126
        },
        "inner_hits": {
          "totalData": {
            "hits": {
              "total": 1,
              "max_score": 1,
              "hits": [
                {
                  "_nested": {
                    "field": "totalData",
                    "offset": 0
                  },
                  "_score": 1,
                  "_source": {
                    "gpsdt": "2018-02-20",
                    "satno": 0,
                    "analog3": -1,
                    "digital1": 0,
                    "digital2": 1,
                    "digital3": 1,
                    "digital4": 2,
                    "lastgpsdt": "2018-02-20T11:54:00",
                    "longitude": 78.081218,
                    "odometer": 0,
                    "intbatlevel": 6,
                    "odo": 0,
                    "latitude": 29.951449,
                    "srtangle": 62,
                    "analog4": 13,
                    "speed": 0,
                    "analog2": -1,
                    "analog1": 9,
                    "extbatlevel": 0
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

Note that since you are not interested in non-matching items of totalData, I used Source Filtering to exclude that field from response at all. Instead, the matching items of totalData can be found under inner_hits.

Hope that helps!

Collectives™ on Stack Overflow

How to get only matched documents from ElasticSearch for a nested array field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related