Filter on 2 indices

[dgastudio (alex)]

Hello!

i have 2 indices, users and employee_shift
users
"created": "1618153891",
"changed": null,
"uuid": "",
"uid:uuid": "1629cbdf-e7fa-41e6-b3a2-ab29a1a77401",
"acc": 57,
"name": "spashogolut",
"role": "General manager",
"role:weight": 12,
"experience": null,
"description": null,
"birthday": null,
"hire_date": null,
"pay_rate": "",
"pay_rate_hour": null,
"pay_rate_overtime": null,
"gender": "",
"supervisor": "",
"status": "1"

employee_shift
"created": "1623760728",
"uuid": "5aecaedc-1122-4a70-8dbb-81eac595425c",
"uid:uuid": "f359396f-eedc-442c-8fbb-7a5e28a5482b",
"acc": "",
"date_from": "1623618000",
"date_to": "0",
"notes": "asdasd",
"ref_type": "",
"ref_employee": "spashogolut",
"ref_employee:uuid": "1629cbdf-e7fa-41e6-b3a2-ab29a1a77401",
"status": "1"

i need to create a query to apply 3 filters

1. global one, which one will filter the ACC field
2. for users only, which one will filter the role
3. for shift only, which one will filter the ref_type

my query looks like

GET users,employee_shift/_search
{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "acc": 57
          }
        }
      ],
      "should": [
        {
          "bool": {
            "filter": {
              "term": {
                "_index": "users"
              }
            },
            "must": [
              {
                "bool": {
                  "must": [
                    {
                      "match": {
                        "role": "Houseman"
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "filter": {
              "term": {
                "_index": "employee_shift"
              }
            },
            "must": [
              {
                "bool": {
                  "must": [
                    {
                      "match": {
                        "ref_type": "Work"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

But, it's return me all the users instead of only users with Houseman role

if i replace should by filter, query results is empty

any advice? thank you

[spinscale (Alexander Reelsen)]

You cannot join data from two indices at query time. There are two solutions to this:

First, merge data at index. time. Move all the user data into the shifts index, so that all the data is available for filtering.

Second, merge data at query time: Move data from both indices into a single index and use the has_parent/has_child queries in combination with the join type. See Join field type | Elasticsearch Guide [7.14] | Elastic

hope this helps!