How to express the logic to let nested field match multiple groups condition

Elasticsearch version 6.8.0

mappings like

{
  "properties": {
    "id": {
      "type": "long"
    },
    "authentication_worker": {
      "type": "nested",
      "properties": {
        "authorized_id": {
          "type": "long"
        },
        "authorized_role": {
          "type": "keyword"
        },
        "authorized_type": {
          "type": "long"
        }
      }
    }
  }
}

a document example

   "id":123000,
   "authentication_worker":[
      {
         "authorized_id":0001,
         "authorized_role":"customer-agency-team",
         "authorized_type":1
      },
      {
         "authorized_id":0002,
         "authorized_role":"customer-agency-team",
         "authorized_type":1
      },
      {
         "authorized_id":1001,
         "authorized_role":"developer",
         "authorized_type":0
      },
      {
         "authorized_id":1002,
         "authorized_role":"developer",
         "authorized_type":0
      },
      {
         "authorized_id":2001,
         "authorized_role":"manager",
         "authorized_type":0
      },
      {
         "authorized_id":2002,
         "authorized_role":"manager",
         "authorized_type":0
      },
            {
         "authorized_id":3001,
         "authorized_role":"QA",
         "authorized_type":0
      },
      {
         "authorized_id":3002,
         "authorized_role":"QA",
         "authorized_type":0
      },
   ],

"authentication_worker" is a nested field.Firstly, I query the index with some user provided basic conditions. For this part I use should of a bool query to implement it, it works well, here for simple I just use match_all to represent it. Thus we focus on the scond part where I need to narrow the hits with some user provided logic, it can be 0 or at most 4 logic condition, each of them will apply to the nested authentication_worker field

1.Given a list of authorized_id. A document will be a hit if there is at least one record of its nested authentication_worker field match

(authentication_worker.authorized_type==0) &&  
(authentication_worker.authorized_id in the given list) 

2.Given a list of authorized_id. A document will be a hit if there is at least one record of its nested authentication_worker field match

(authentication_worker.authorized_type==0) && 
(authentication_worker.authorized_role=="developer") &&
(authentication_worker.authorized_id in the given list)
  1. Given a list of authorized_id. A document will be a hit if there is at least one record of its nested authentication_worker field match
(authentication_worker.authorized_type==0) && 
(authentication_worker.authorized_role=="manager") &&
(authentication_worker.authorized_id in the given list)

4.Given a list of authorized_id. A document will be a hit if there is at least one record of its nested authentication_worker field match

(authentication_worker.authorized_type==0) && 
(authentication_worker.authorized_role=="QA")  &&
(authentication_worker.authorized_id in the given list)

I use filter to do it with the same bool query

"query": {
    "bool" : {
        "must" : {
        "match_all": {}
        },
        "filter": {
          // focus here
        }
    }
}

The issue here is, any one of the 4 condition works well if I only use it only. But use using any 2 or more of them, I will get empty hits.

e.g.

{
  "bool": {
    "must" : {
        "match_all": {}
    },
    "filter": [
      {
        "nested": {
          "query": {
            "bool": {
              "must": [
                {
                  "bool": {
                    "must": [
                      {
                        "term": {
                          "authentication_worker.authorized_type": {
                            "value": 0 
                          }
                        }
                      },
                      {
                        "terms": {
                          "authentication_worker.authorized_id": [
                            1001,
                            1002,
                            1003
                          ] 
                        }
                      },
                      {
                        "term": {
                          "authentication_worker.authorized_role": {
                            "value": "developer" 
                          }
                        }
                      }
                    ]  
                  }
                },
                {
                  "bool": {
                    "must": [
                      {
                        "term": {
                          "authentication_worker.authorized_type": {
                            "value": 0 
                          }
                        }
                      },
                      {
                        "terms": {
                          "authentication_worker.authorized_id": [
                            3001,
                            3002,
                            3003
                          ] 
                        }
                      },
                      {
                        "term": {
                          "authentication_worker.authorized_role": {
                            "value": "QA" 
                          }
                        }
                      }
                    ] 
                  }
                }
              ] 
            }
          },
          "path": "authentication_worker",
        }
      }
    ]
  }
}

Expected : the above example document with "id":123000 should return in hits result.
Actual hits: empty.

I expect Elasticsearch to run each 3-'must' in an atomic group. In total, there are 2 groups. The returned document should match both of them.

Note, both group use the same field with different values. Seems Elasticsearch run all of 6 must together and can not figure out the logic?
If so, how I should express my logic to let Elasticsearch understand it correctly?

Thank you!

How to express the logic to let nested field match multiple groups condition

Fixed by: move the outer layer multi must out of nested query.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.