Filter on nested field

Elastic Stack Elasticsearch
1

Hello!

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

Mapping

{
    "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

{
    "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

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

but still returns all shifts instead of filtered

2

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.

3

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

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

4

finally i have solved it by using inner_hits + _source excluded!

5

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