# Filter on 2 indices

**URL:** <https://discuss.elastic.co/t/filter-on-2-indices/282098>\
**Category:** Elasticsearch\
**Created:** [August 20, 2021, 4:20pm UTC](https://discuss.elastic.co/t/filter-on-2-indices/282098 "2021-08-20T16:20:18Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dgastudio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dgastudio/32/90362_2.png) [@dgastudio](https://discuss.elastic.co/u/dgastudio)\
**Post date:** [August 20, 2021, 4:20pm UTC](https://discuss.elastic.co/t/filter-on-2-indices/282098/1 "2021-08-20T16:20:18Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 25, 2021, 9:51am UTC](https://discuss.elastic.co/t/filter-on-2-indices/282098/2 "2021-08-25T09:51:48Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/7.14/parent-join.html)

hope this helps!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 22, 2021, 9:51am UTC](https://discuss.elastic.co/t/filter-on-2-indices/282098/3 "2021-09-22T09:51:59Z")

</div>

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