Elasticsearch query Based on multiple conditions

Question

I am trying to search based on multiple parameters. For example, my doc structure is following,

{
"id": "101",
"start_time": "2021-12-13T06:57:29.420198Z",
"end_time": "2021-12-13T07:00:23.511722Z",
"data": [{
        "starttimestamp": "2022-01-03T11:21:22.107413Z",
        "user": "John",
        "speech": "Thanks. I’m just happy that it’s over. I was really nervous about it.",
        "endtimestamp": "2022-01-03T11:21:22.247482Z"
    },
    {
        "starttimestamp": "2022-01-03T11:21:26.905401Z",
        "user": "Tom",
        "speech": "Well, I’m sure you did great. where is the university",
        "endtimestamp": "2022-01-03T11:21:27.065316Z"
    },
    {
        "starttimestamp": "2022-01-03T11:21:33.165617Z",
        "user": "John",
        "speech": "university is in canada.",
        "endtimestamp": "2022-01-03T11:21:33.165900Z"
    }
]

}

Now I need to search for a particular user,

Ex: Positive case: search for the doc where user is "John" who spoke about "canada" at starttimestamp "2022-01-03T11:21:33.165617Z" output : we should get result for above query

Negative case: search for a doc where user "Tom" who spoke about "canada" at starttimestamp "2022-01-03T11:21:33.165617Z" output : the result should be empty here, as "Tom" did not talk about "canada"

I tried to achieve expected o/p using following queries,

{
"query": {
    "bool": {
        "must": [
            {
                "term": {"data.user": "Tom"}
            },
            {
                "bool": {
                    "must": [
                        {"term": {"data.speech": "canada"}}
                    ]
                }
            }
        ]
    }
}

}

in the above query, we should be getting an empty list but we are getting the above doc as result.

I have referred some other resources: Conditional based Elastic Search Query (if else or Union case) Query with multiple fields and conditions in ElasticSearch

I didn't find the exact info which can fix my issue.

Answer 1

Please check this documentation for how array field work in Elasticsearch. As you are using array of object, you can not query each object individual.

Arrays of objects do not work as you would expect: you cannot query each object independently of the other objects in the array. If you need to be able to do this then you should use the nested data type instead of the object data type.

You can create index using below sample:

PUT index_name
{
  "mappings": {
    "properties": {
      "data":{
        "type": "nested"
      }
    }
  }
}

Index below document:

POST index_name/_doc
{
  "id": "101",
  "start_time": "2021-12-13T06:57:29.420198Z",
  "end_time": "2021-12-13T07:00:23.511722Z",
  "data": [
    {
      "starttimestamp": "2022-01-03T11:21:22.107413Z",
      "user": "John",
      "speech": "Thanks. I’m just happy that it’s over. I was really nervous about it.",
      "endtimestamp": "2022-01-03T11:21:22.247482Z"
    },
    {
      "starttimestamp": "2022-01-03T11:21:26.905401Z",
      "user": "Tom",
      "speech": "Well, I’m sure you did great. where is the university",
      "endtimestamp": "2022-01-03T11:21:27.065316Z"
    },
    {
      "starttimestamp": "2022-01-03T11:21:33.165617Z",
      "user": "John",
      "speech": "university is in canada.",
      "endtimestamp": "2022-01-03T11:21:33.165900Z"
    }
  ]
}

Sample Query:

POST index_name/_search
{
  "query": {
    "nested": {
      "path": "data",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "data.user": "John"
              }
            },
            {
              "match": {
                "data.speech": "canada"
              }
            }
          ]
        }
      }
    }
  }
}

Please check this blog for more clear understanding of object and nested type.