Doing a Range Query over particular Nested Document

Question

I have a document structure like this. For this below two documents, we have nested documents called interaction info. I just need to get only the documents that have title duration and their value is greater than 60

  {
    "key": "f07ff9ba-36e4-482a-9c1c-d888e89f926e",
    "interactionInfo": [
      {
        "title": "duration",
        "value": "11"
      },
      {
        "title": "timetaken",
        "value": "9"
      },
      {
        "title": "talk_time",
        "value": "145"
      }
    ]
  },
  {
    "key": "f07ff9ba-36e4-482a-9c1c-d888e89f926e",
    "interactionInfo": [
      {
        "title": "duration",
        "value": "120"
      },
      {
        "title": "timetaken",
        "value": "9"
      },
      {
        "title": "talk_time",
        "value": "60"
      }
    ]
  }
]

Is it possible to get only the document that has title: duration and their value is greater than 60.Value Property in the nested Document is text and keyword.

Answer 1

There are few basic mistakes in your solution, in order to utilize the range query(ie find a document which has more than 60 value, you need to store them as an integer in your case).

Also please refer this official guide which has a similar example.

Let me show you a step-by-step example on how to do it.

Index def

{
    "mappings" :{
        "properties" :{
            "interactionInfo" :{
                "type" : "nested"
            },
            "key" : {
                "type" : "keyword"
            }
        }
    }
}

Index sample docs

{
    "key": "f07ff9ba-36e4-482a-9c1c-d888e89f926e",
    "interactionInfo": [
      {
        "title": "duration",
        "value": 120. --> note, not using `""` double quotes which would store them as integer
      },
      {
        "title": "timetaken",
        "value": 9
      },
      {
        "title": "talk_time",
        "value": 60
      }
    ]
  }


{
    "key": "f07ff9ba-36e4-482a-9c1c-d888e89f926e",
    "interactionInfo": [
        {
            "title": "duration",
            "value": 11
        },
        {
            "title": "timetaken",
            "value": 9
        },
        {
            "title": "talk_time",
            "value": 145
        }
    ]
}

Search query

{
    "query": {
        "nested": {
            "path": "interactionInfo",
            "query": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "interactionInfo.title": "duration"
                            }
                        },
                        {
                            "range": {
                                "interactionInfo.value": {
                                    "gt": 60
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}

And your expected search result

"hits": [
      {
        "_index": "nestedsoint",
        "_type": "_doc",
        "_id": "2",
        "_score": 2.0296195,
        "_source": {
          "key": "f07ff9ba-36e4-482a-9c1c-d888e89f926e",
          "interactionInfo": [
            {
              "title": "duration",
              "value": 120
            },
            {
              "title": "timetaken",
              "value": 9
            },
            {
              "title": "talk_time",
              "value": 60
            }
          ]
        }
      }
    ]