Match multiple items inside a nested array

I've stored documents within my elasticsearch service that are similar to this:

[
    {
     "first_name": "John",
     "last_name": 'Dow',
     "statuses": [
        {
            "name": "STAT1",
            "start_date":"2022-10-21T21:03:06", 
            "happy": false
        },
        {
            "name": "STAT2",
            "start_date":"2022-10-21T21:03:06", 
            "happy": true
        },
   
     ]
 }
...
]

I've a component within my UI where the user can select the required filters that he wants to apply on the data. For example give me the docs where:

first_name == "John" & last_name== 'Doe'

After the user selecting the desired filters, i'm creating a query similar to this one:

"query": {
    "bool": {
        "must": [
            {
                 "regexp": {
                     "first_name": {
                        "value": ".*John.*"
                      }
                  },
                   "regexp": {
                     "last_name": {
                        "value": ".*Doe.*"
                      }
                  },
            }
        ],
        "should": []
    }
}

Now I've a new requirement where i need to allow to filter the documents as follow:

Show me the document where:

statuses.name === STAT1 & statuses.happy === false

and 

statuses.name === STAT2 & statuses.happy === true

and

first_name === Jhon

I didn't found any example how to achieve that requirement, any help would be appreciated

CodePudding user response：

You can start with this query. Read more about nested queries.

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "first_name": "john"
          }
        }
      ],
      "filter": [
        {
          "nested": {
            "path": "statuses",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "statuses.name": "STAT1"
                    }
                  },
                  {
                    "term": {
                      "statuses.happy": {
                        "value": "false"
                      }
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "statuses",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "statuses.name": "STAT2"
                    }
                  },
                  {
                    "term": {
                      "statuses.happy": {
                        "value": "true"
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}