'Elasticsearch query Based on multiple conditions

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.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source