# Filter on nested field

**URL:** <https://discuss.elastic.co/t/filter-on-nested-field/283697>\
**Category:** Elasticsearch\
**Created:** [September 8, 2021, 5:08pm UTC](https://discuss.elastic.co/t/filter-on-nested-field/283697 "2021-09-08T17:08:29Z")\
**Posts on this page:** 5\
**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:** [September 8, 2021, 5:08pm UTC](https://discuss.elastic.co/t/filter-on-nested-field/283697/1 "2021-09-08T17:08:29Z")

</div>

Hello!

I can't understand what i'm doing wrong when filtering on nested field.

Mapping

```auto
{
    "acc": {
        "type": "keyword"
    },
    "birthday": {
        "type": "date",
        "format": "epoch_second"
    },
    "created": {
        "type": "date",
        "format": "epoch_second"
    },
    "description": {
        "type": "text"
    },
    "experience": {
        "type": "keyword"
    },
    "gender": {
        "type": "keyword"
    },
    "hire_date": {
        "type": "date",
        "format": "epoch_second"
    },
    "name": {
        "type": "keyword"
    },
    "pay_rate": {
        "type": "keyword"
    },
    "pay_rate_hour": {
        "type": "float"
    },
    "pay_rate_overtime": {
        "type": "text"
    },
    "role": {
        "type": "keyword"
    },
    "role:weight": {
        "type": "integer"
    },
    "shifts": {
        "type": "nested",
        "properties": {
            "date_from": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "date_to": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "notes": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "ref_type": {
                "type": "keyword"
            },
            "ref_type:color": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            },
            "uuid": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            }
        }
    },
    "status": {
        "type": "integer"
    },
    "supervisor": {
        "type": "keyword"
    },
    "uuid": {
        "type": "text"
    }
}

```

My index data

```auto
{
    "took": 0,
    "timed_out": false,
    "_shards": {
        "total": 2,
        "successful": 2,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 1,
            "relation": "eq"
        },
        "max_score": 0.2876821,
        "hits": [
            {
                "_index": "employee_shift",
                "_type": "_doc",
                "_id": "1629cbdf-e7fa-41e6-b3a2-ab29a1a77401",
                "_score": 0.2876821,
                "_source": {
                    "created": "1618153891",
                    "uuid": "1629cbdf-e7fa-41e6-b3a2-ab29a1a77401",
                    "acc": 57,
                    "name": "spashogolut",
                    "role": "General manager",
                    "role:weight": 12,
                    "experience": "",
                    "description": null,
                    "birthday": null,
                    "hire_date": null,
                    "pay_rate": "",
                    "pay_rate_hour": null,
                    "pay_rate_overtime": null,
                    "gender": "",
                    "supervisor": "",
                    "status": "1",
                    "shifts": [
                        {
                            "uuid": "12d6bdf7-0e84-4030-8f3e-1c59f538b379",
                            "ref_type": "Work",
                            "ref_type:color": "",
                            "date_from": "1630876500",
                            "date_to": "1630881000",
                            "notes": "123"
                        },
                        {
                            "uuid": "65328815-9b20-40b7-a8a4-05c922597122",
                            "ref_type": "Work",
                            "ref_type:color": "",
                            "date_from": "1631051100",
                            "date_to": "1631054700",
                            "notes": "gasdsad"
                        },
                        {
                            "uuid": "aca56f49-c81b-4026-b64f-d2d47b963b1f",
                            "ref_type": "Day off",
                            "ref_type:color": "",
                            "date_from": "1630965600",
                            "date_to": "1630968300",
                            "notes": ""
                        },
                        {
                            "uuid": "4ff222f3-1369-4353-90c0-ccabc78718ed",
                            "ref_type": "Day off",
                            "ref_type:color": "",
                            "date_from": "1630877400",
                            "date_to": "1630882800",
                            "notes": ""
                        },
                        {
                            "uuid": "64bbad31-0999-4677-80c3-23c38eef6ad8",
                            "ref_type": "Work",
                            "ref_type:color": "",
                            "date_from": "1630877400",
                            "date_to": "1630882800",
                            "notes": "ad"
                        },
                        {
                            "uuid": "9632f602-4ce7-48b8-abd6-f0f493e250d6",
                            "ref_type": "Day off",
                            "ref_type:color": "",
                            "date_from": "1631135700",
                            "date_to": "1631139300",
                            "notes": ""
                        },
                        {
                            "uuid": "3653c811-042c-4425-af42-02c763135ca3",
                            "ref_type": "Work",
                            "ref_type:color": "",
                            "date_from": "1631223000",
                            "date_to": "1631227500",
                            "notes": "sadsa"
                        },
                        {
                            "uuid": "e96ce19e-5d4a-4f54-b1a7-50b557f24e41",
                            "ref_type": "Day off",
                            "ref_type:color": "",
                            "date_from": "1631049300",
                            "date_to": "1631055600",
                            "notes": "asddas"
                        }
                    ]
                }
            }
        ]
    }
}

```

My query

```auto
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "shifts",
            "query": {
              "match": {
                "shifts.ref_type": "Day off"
              }
            }
          }
        }
      ]
    }
  }
}

```

but still returns all shifts instead of filtered

---

<div class="post-metadata">

**Author:** ![Dan\_Christensen](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dan_christensen/32/94328_2.png) [@Dan\_Christensen](https://discuss.elastic.co/u/Dan_Christensen)\
**Post date:** [September 8, 2021, 5:25pm UTC](https://discuss.elastic.co/t/filter-on-nested-field/283697/2 "2021-09-08T17:25:02Z")

</div>

The nested query will return parent docs for nested docs that match the query criteria. So, because the "1629cbdf-e7fa-41e6-b3a2-ab29a1a77401" doc contains shifts that have a ref\_type of "Day Off", it will return the entire "1629cbdf-e7fa-41e6-b3a2-ab29a1a77401" doc including all shifts are part of that doc. The "shifts" will not be filtered in results to only return shifts with a ref\_type of "Day Off". I don't think that this is possible with a nested query and the index structure that you've defined using nested documents.

It's difficult to make recommendations without fully understanding your requirements, but at a glance it seems that you would be better off having a separate index for shifts and employee details... or to replicate some of the employee details that you might want to query on into shift documents. It all depends on how you might want to query the shift data.

---

<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:** [September 8, 2021, 5:32pm UTC](https://discuss.elastic.co/t/filter-on-nested-field/283697/3 "2021-09-08T17:32:20Z")

</div>

Hello Dan!  
First of all, thank you for your response.

My requirement is to show all the users, but filter shifts.

i have tried to filter on 2 indices [Filter on 2 indices](https://discuss.elastic.co/t/filter-on-2-indices/282098)

but it doesnt work

so, basically, my requirement is:

show users and shifts on same page

and filter data by both doc types

example of ui

> **[monosnap.com](https://monosnap.com/file/9Ntd1PQNLfkROllpvYOxWg0a3hVFXn)**
>
> Monosnap screenshot tool for Mac and PC with own cloud storage. Take screenshots, record videos and upload files directly to the cloud. Just in one click.

---

<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:** [September 8, 2021, 7:25pm UTC](https://discuss.elastic.co/t/filter-on-nested-field/283697/4 "2021-09-08T19:25:24Z")

</div>

finally i have solved it by using inner\_hits + \_source excluded!

---

<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:** [October 6, 2021, 7:25pm UTC](https://discuss.elastic.co/t/filter-on-nested-field/283697/5 "2021-10-06T19:25:33Z")

</div>

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