Filter on nested field with Elasticsearch

user_807964 0
Viewed 30

I have 2 entities in my project, users and schedule

I need to create a page on which, in the form of a weekly calendar, I can display all employees and their shift for each day

Example: https://monosnap.com/file/tEb3rUYNRmredPWOdfxRBTBpqkh36H

For this, I created a new index in which I indexed all employees. Each employee has a nested field, where his shifts are stored

The problem is that I can't figure out aggregations and filters.

I need to

  1. there is always a filter by date that refers to the shift field property.
  2. It doesn't matter if there are suitable shifts or not, we show ALL employees
  3. the following 2 aggregations, user role and type of shift are also displayed.
  • user role filters the list of employees
  • type of shift, shows or hides associated shifts

An example of my request

{

"aggs": {
    "shifts.ref_type": {
        "nested": {
            "path": "shifts"
        },
        "aggs": {
            "shifts.ref_type": {
                "terms": {
                    "field": "shifts.ref_type",
                    "size": 1000
                }
            }
        }
    },
    "role": {
        "terms": {
            "field": "role",
            "size": 1000
        }
    },
    "name": {
        "terms": {
            "field": "name",
            "size": 1000
        }
    }
},
"query": {
    "bool": {
        "must": [
            {
                "term": {
                    "_routing": "1"
                }
            }
        ],
        "should": [
            {
                "range": {
                    "shifts.date_from": {
                        "lte": 1636923600,
                        "gte": 1636318800
                    }
                }
            }
        ]
    }
},
"sort": [
    {
        "created": "ASC"
    }
],
"size": 1

}

Sample response

{
"took": 3,
"timed_out": false,
"_shards": {
    "total": 4,
    "successful": 4,
    "skipped": 0,
    "failed": 0
},
"hits": {
    "total": {
        "value": 36,
        "relation": "eq"
    },
    "max_score": null,
    "hits": [
        {
            "_index": "employee_shift",
            "_type": "_doc",
            "_id": "a8abf060-25c8-45ee-a50e-02a2e2ad1c40",
            "_score": null,
            "_routing": "1",
            "_source": {
                "created": 1633967157,
                "type": "user",
                "title": null,
                "description": "",
                "uuid": "a8abf060-25c8-45ee-a50e-02a2e2ad1c40",
                "author": "System",
                "author:name": "System",
                "author:role": "",
                "acc": 1,
                "property": [
                    1
                ],
                "status": "Enabled",
                "class": [
                    ""
                ],
                "weight": "",
                "tags": [],
                "language": "en",
                "ref_source_id": null,
                "ref_source_helper": null,
                "ref_property": [
                    "test hostel2"
                ],
                "ref_property_default": "test hostel2",
                "name": "Housekeeper 1",
                "role": [
                    "Housekeeper"
                ],
                "role:weight": "2",
                "role:id": [
                    5
                ],
                "pay_rate": null,
                "experience": null,
                "supervisor": null,
                "gender": null,
                "units": [
                    "102",
                    "103",
                    "106",
                    "107",
                    "110",
                    "111",
                    "116",
                    "117",
                    "120",
                    "121",
                    "124",
                    "125",
                    "128",
                    "129",
                    "132",
                    "133",
                    "136",
                    "137"
                ],
                "task_inspection": "All tasks",
                "shifts": [
                    {
                        "uuid": "f48ae398-0668-4693-b335-2fee3baa2941",
                        "ref_type": "Work",
                        "ref_type:color": "",
                        "date_from": "1635196500",
                        "date_to": "1635197400",
                        "notes": null
                    },
                    {
                        "uuid": "8b4d8148-2583-4ccf-a1cc-ae5e6d1e728e",
                        "ref_type": "Work",
                        "ref_type:color": "",
                        "date_from": "1635287400",
                        "date_to": "1635289200",
                        "notes": null
                    },
                    {
                        "uuid": "3f5520d8-8108-4abd-8e2a-70c00faf6994",
                        "ref_type": "Work",
                        "ref_type:color": "",
                        "date_from": "1635369300",
                        "date_to": "1635373800",
                        "notes": null
                    },
                    {
                        "uuid": "d4009660-447c-47de-b0f3-3c1f2d8d8f99",
                        "ref_type": "Work",
                        "ref_type:color": "",
                        "date_from": "1635286500",
                        "date_to": "1635288300",
                        "notes": null
                    },
                    {
                        "uuid": "b3d883f0-b71f-4df7-bb63-a50f137528a4",
                        "ref_type": "Work",
                        "ref_type:color": "",
                        "date_from": "1635370200",
                        "date_to": "1635372900",
                        "notes": null
                    }
                ]
            },
            "sort": [
                1633967157000
            ]
        }
    ]
},
"aggregations": {
    "role": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
            {
                "key": "Houseman",
                "doc_count": 4
            },
            {
                "key": "Maintenance",
                "doc_count": 4
            },
            {
                "key": "Supervisor",
                "doc_count": 4
            },
            {
                "key": "Supervisor HSKP",
                "doc_count": 4
            },
            {
                "key": "Supervisor Maintenance",
                "doc_count": 4
            },
            {
                "key": "Administrator",
                "doc_count": 3
            },
            {
                "key": "Concierge dispatcher",
                "doc_count": 3
            },
            {
                "key": "Frontdesk",
                "doc_count": 3
            },
            {
                "key": "General manager",
                "doc_count": 3
            },
            {
                "key": "HKeeper",
                "doc_count": 3
            },
            {
                "key": "Housekeeper",
                "doc_count": 3
            },
            {
                "key": "Manager",
                "doc_count": 3
            }
        ]
    },
    "shifts.ref_type": {
        "doc_count": 21,
        "shifts.ref_type": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "Work",
                    "doc_count": 19
                },
                {
                    "key": "test",
                    "doc_count": 2
                }
            ]
        }
    },
    "name": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
            {
                "key": "Administrator 123",
                "doc_count": 1
            },
            {
                "key": "Administrator 223",
                "doc_count": 1
            },
            {
                "key": "Administrator 3",
                "doc_count": 1
            },
            {
                "key": "Concierge dispatcher 1",
                "doc_count": 1
            },
            {
                "key": "Concierge dispatcher 2",
                "doc_count": 1
            },
            {
                "key": "Concierge dispatcher 3",
                "doc_count": 1
            },
            {
                "key": "Frontdesk 1",
                "doc_count": 1
            },
            {
                "key": "Frontdesk 2",
                "doc_count": 1
            },
            {
                "key": "Frontdesk 3",
                "doc_count": 1
            },
            {
                "key": "General manager 1",
                "doc_count": 1
            },
            {
                "key": "General manager 2",
                "doc_count": 1
            },
            {
                "key": "General manager 3",
                "doc_count": 1
            },
            {
                "key": "HKeeper 1",
                "doc_count": 1
            },
            {
                "key": "HKeeper 2",
                "doc_count": 1
            },
            {
                "key": "HKeeper 3",
                "doc_count": 1
            },
            {
                "key": "Housekeeper 1",
                "doc_count": 1
            },
            {
                "key": "Housekeeper 2",
                "doc_count": 1
            },
            {
                "key": "Housekeeper 3",
                "doc_count": 1
            },
            {
                "key": "Houseman 1",
                "doc_count": 1
            },
            {
                "key": "Houseman 2",
                "doc_count": 1
            },
            {
                "key": "Houseman 3",
                "doc_count": 1
            },
            {
                "key": "Maintenance 1",
                "doc_count": 1
            },
            {
                "key": "Maintenance 2",
                "doc_count": 1
            },
            {
                "key": "Maintenance 3",
                "doc_count": 1
            },
            {
                "key": "Manager 1222",
                "doc_count": 1
            },
            {
                "key": "Manager 2",
                "doc_count": 1
            },
            {
                "key": "Manager 3",
                "doc_count": 1
            },
            {
                "key": "Supervisor 1",
                "doc_count": 1
            },
            {
                "key": "Supervisor 2",
                "doc_count": 1
            },
            {
                "key": "Supervisor 3",
                "doc_count": 1
            },
            {
                "key": "Supervisor HSKP 1",
                "doc_count": 1
            },
            {
                "key": "Supervisor HSKP 2",
                "doc_count": 1
            },
            {
                "key": "Supervisor HSKP 3",
                "doc_count": 1
            },
            {
                "key": "Supervisor Maintenance 1",
                "doc_count": 1
            },
            {
                "key": "Supervisor Maintenance 2",
                "doc_count": 1
            },
            {
                "key": "Supervisor Maintenance 3",
                "doc_count": 1
            }
        ]
    }
}

}

At the moment, everything seems to be working correctly, except for one point. Aggregation by the type of shift ALWAYS outputs data, although they should not be found by the filter for the date.

any advice? thank you

elasticsearch
1 Answers

I'm making the answer based on the assumption from my comment:

  • you want your query to return all employees
  • you want the ref_type aggregation to only include shifts matching your date range
  • you want the "shifts" collections under your results to likewise only include shifts matching your date range

Apologies if I misunderstood your question.

One thing to get out of the way first, though you may have been aware: the should part of your query is not restricting the results, it's only affecting the score, since you already have a must.

As a corollary it's not going to affect the aggregated results instead, for that you need to use a filter aggregation:

"aggs": {
        "shifts.ref_type": {
            "nested": {
                "path": "shifts"
            },
            "aggs": {
                "shifts.ref_type": {
                    "filter": { 
                        "range": { 
                            "shifts.date_from": {
                                "gte": 1635370100,
                                "lte": 1635370300
                            }
                        } 
                    },
                    "aggs": {
                        "shifts.ref_type": {
                            "terms": {
                                "field": "shifts.ref_type",
                                "size": 1000
                            }
                        }
                    }
                }
            }
        },

This is going to get you the filtered counts in your aggregation, but that still won't filter your results - you'll get all the "shifts" in your hits. So a thing to be aware of with nested documents, your query is going to restrict the documents that are returned, based on the matches in the nested documents, but it's not going to actually filter out the nested documents that did not match. In order to do that you have another feature, nested inner hits, which lets you figure out exactly which nested documents matched. It's still not enough in your case since you don't want to filter out the results entirely (so even if there is no "inner hit" you still want to return the document, or at least have it be part of the aggregation). So now you have yet another feature, post-filter, which you can use to filter the documents after they have been aggregated. Taking the 2 together:

    "post_filter": {
        "nested": {
            "path": "shifts",
            "query": {
                "bool": {
                    "must": [
                        {
                            "range": {
                                "shifts.date_from": {
                                    "lte": 635370200,
                                    "gte": 635370200
                                }
                            }
                        }
                    ]
                }
            },
            "inner_hits": {}
        }
    },

If you now set _source: false, you won't have the hits, and you just get the shifts that matched (and then you still have the employee name and roles in your aggregation results):

{
    "took": 3,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 1,
            "relation": "eq"
        },
        "max_score": 0.08701137,
        "hits": [
            {
                "_index": "employee_shift",
                "_type": "_doc",
                "_id": "-tRnLn0B5PjpsgKgGXlB",
                "_score": 0.08701137,
                "inner_hits": {
                    "shifts": {
                        "hits": {
                            "total": {
                                "value": 1,
                                "relation": "eq"
                            },
                            "max_score": 1,
                            "hits": [
                                {
                                    "_index": "employee_shift",
                                    "_type": "_doc",
                                    "_id": "-tRnLn0B5PjpsgKgGXlB",
                                    "_nested": {
                                        "field": "shifts",
                                        "offset": 4
                                    },
                                    "_score": 1,
                                    "_source": {
                                        "notes": null,
                                        "ref_type:color": "",
                                        "date_to": 635372900,
                                        "ref_type": "Work",
                                        "uuid": "b3d883f0-b71f-4df7-bb63-a50f137528a4",
                                        "date_from": 635370200
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        ]
    },
    "aggregations": {
        "role": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "Housekeeper",
                    "doc_count": 5
                }
            ]
        },
        "shifts.ref_type": {
            "doc_count": 25,
            "shifts.ref_type": {
                "doc_count": 4,
                "shifts.ref_type": {
                    "doc_count_error_upper_bound": 0,
                    "sum_other_doc_count": 0,
                    "buckets": [
                        {
                            "key": "Work",
                            "doc_count": 3
                        },
                        {
                            "key": "Work2",
                            "doc_count": 1
                        }
                    ]
                }
            }
        },
        "name": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "Housekeeper 4",
                    "doc_count": 2
                },
                {
                    "key": "Housekeeper 1",
                    "doc_count": 1
                },
                {
                    "key": "Housekeeper 2",
                    "doc_count": 1
                },
                {
                    "key": "Housekeeper 3",
                    "doc_count": 1
                }
            ]
        }
    }
}
user_2242850
Related