Hipny
Reputation: 749
ElasticSearch (Nest) Terms sub aggregation of Terms - Not working as intended
Taking the following mapping in account :
{
"person": {
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
...
"trainings": {
"properties": {
"attendanceDate": {
"type": "date",
"format": "dateOptionalTime"
},
"providerId": {
"type": "string",
"index": "not_analyzed"
},
"trainingId": {
"type": "string",
"index": "not_analyzed"
}
...
}
}
}
}
}
This is currently my elastic search query
{
"aggs": {
"trainning_agg": {
"terms": {
"field": "trainings.trainingId"
},
"aggs": {
"provider_agg": {
"terms": {
"field": "trainings.providerId"
}
}
}
}
}
}
Unfortunately, elastic search will return to me a list of buckets by trainings containing a list of buckets of all aggregated providers (ignoring the training id).
Instead of a list of bucket by trainings containing a list of buckets of all the providers filtered by the parent training.
Any help would be grateful, and let me know what details I could provide to help you understand my problem!
Thanks!
Edits:
Let me precise that currently every training has currently one, and only provider, so every "person" will currently have one "trainings" which will contains a single element. (This is not always the case, but lets assume so for the sake of the simplicity of the example)
There is currently 12 distinct trainings, each trainings as one provider.
Please note that currently the provider id is the same as the training id (This is not always the case, but lets assume so for the sake of the simplicity of the example) This is currently my response :
{
...
"aggregations": {
"training_agg": {
...
"buckets": [
{
"key": "17effec3-1284-4609-af41-2252b4dde8b5",
"doc_count": 8808,
"provider_agg": {
...
"buckets": [
{
"key": "17effec3-1284-4609-af41-2252b4dde8b5",
"doc_count": 8808
},
{
"key": "560be929-134b-4b67-befd-f38fdbb2f7ad",
"doc_count": 8808
},
{
"key": "96b16133-51ef-413c-afa5-24f74263126b",
"doc_count": 8808
},
{
"key": "96c36f93-90ce-4378-a9f2-8a8aaa780fd2",
"doc_count": 8808
},
{
"key": "e9467886-42e0-467c-a338-a7fcbc66125f",
"doc_count": 8808
},
{
"key": "f05554c2-778b-44b6-972e-c2fea01d924f",
"doc_count": 8808
},
{
"key": "ab40dcda-e253-4ff3-95e9-314e4ce5ba39",
"doc_count": 8807
},
{
"key": "5aaa2f31-6aa6-4d5a-86ea-8a25cb11247e",
"doc_count": 8081
},
{
"key": "d1c45ad7-046b-43de-b65e-012185c17f6a",
"doc_count": 4686
},
{
"key": "c06e0cee-1050-41b6-ac83-ea3a00f0abb7",
"doc_count": 3135
}
]
}
}
]
}
}
}
And this is what i'm trying to get :
{
...
"aggregations": {
"training_agg": {
...
"buckets": [
{
"key": "17effec3-1284-4609-af41-2252b4dde8b5",
"doc_count": 8808,
"provider_agg": {
...
"buckets": [
{
"key": "17effec3-1284-4609-af41-2252b4dde8b5",
"doc_count": 8808
}
]
}
}
]
}
}
}
Documents examples (Had to remove some info, confidentiality agreement :O) N.B.: The fact that the providerId and the trainingId is the same is pure laziness from me when I inserted the data, it could be two UUID completely different.
{
"hits": [
{
...
"_source": {
"id": "d9753b0fd674f9d55b8cd125258b2713b191499828dc5301162dffc17f3ddf4b",
"name": "Do Van Tien",
...
"trainings": [
{
"trainingName": "Certification - 4C",
"providerName": "Hidden Provider",
"trainingId": "c06e0cee-1050-41b6-ac83-ea3a00f0abb7",
"providerId": "c06e0cee-1050-41b6-ac83-ea3a00f0abb7",
"attendanceDate": "2012-07-07T00:00:00"
},
{
"trainingName": "GAP - Safety & Hygiene",
"providerName": "Hidden Provider",
"trainingId": "96c36f93-90ce-4378-a9f2-8a8aaa780fd2",
"providerId": "96c36f93-90ce-4378-a9f2-8a8aaa780fd2",
"attendanceDate": "2014-04-16T00:00:00"
},
{
"trainingName": "GAP - Crop protection",
"providerName": "Hidden Provider",
"trainingId": "ab40dcda-e253-4ff3-95e9-314e4ce5ba39",
"providerId": "ab40dcda-e253-4ff3-95e9-314e4ce5ba39",
"attendanceDate": "2014-04-16T00:00:00"
},
{
"trainingName": "GAP - Post Harvest",
"providerName": "Hidden Provider",
"trainingId": "d1c45ad7-046b-43de-b65e-012185c17f6a",
"providerId": "d1c45ad7-046b-43de-b65e-012185c17f6a",
"attendanceDate": "2012-07-07T00:00:00"
},
{
"trainingName": "GAP - Soil Management & Fertilizer Application",
"providerName": "Hidden Provider",
"trainingId": "17effec3-1284-4609-af41-2252b4dde8b5",
"providerId": "17effec3-1284-4609-af41-2252b4dde8b5",
"attendanceDate": "2012-08-16T00:00:00"
},
{
"trainingName": "GAP - Irrigation & Water Management",
"providerName": "Hidden Provider",
"trainingId": "560be929-134b-4b67-befd-f38fdbb2f7ad",
"providerId": "560be929-134b-4b67-befd-f38fdbb2f7ad",
"attendanceDate": "2012-07-07T00:00:00"
},
{
"trainingName": "GAP - Composting",
"providerName": "Hidden Provider",
"trainingId": "96b16133-51ef-413c-afa5-24f74263126b",
"providerId": "96b16133-51ef-413c-afa5-24f74263126b",
"attendanceDate": "2013-08-22T00:00:00"
},
{
"trainingName": "Certification - UTZ - Code of Conduct",
"providerName": "Hidden Provider",
"trainingId": "5aaa2f31-6aa6-4d5a-86ea-8a25cb11247e",
"providerId": "5aaa2f31-6aa6-4d5a-86ea-8a25cb11247e",
"attendanceDate": "2013-08-22T00:00:00"
},
{
"trainingName": "Economic - Business skills",
"providerName": "Hidden Provider",
"trainingId": "e9467886-42e0-467c-a338-a7fcbc66125f",
"providerId": "e9467886-42e0-467c-a338-a7fcbc66125f",
"attendanceDate": "2013-08-22T00:00:00"
},
{
"trainingName": "GAP - Pruning",
"providerName": "Hidden Provider",
"trainingId": "f05554c2-778b-44b6-972e-c2fea01d924f",
"providerId": "f05554c2-778b-44b6-972e-c2fea01d924f",
"attendanceDate": "2013-05-09T00:00:00"
}
]
}
},
{
...
"_source": {
"id": "dca2d4b86bf1bea73492bde614e0bb3d7cadf2f4f44e9fe3c5b4cf930d2cca00",
"name": "Le Thi Than",
...
"trainings": [
{
"trainingName": "Certification - UTZ - Code of Conduct",
"providerName": "Hidden Provider",
"trainingId": "5aaa2f31-6aa6-4d5a-86ea-8a25cb11247e",
"providerId": "5aaa2f31-6aa6-4d5a-86ea-8a25cb11247e",
"attendanceDate": "2012-06-25T00:00:00"
},
{
"trainingName": "GAP - Crop protection",
"providerName": "Hidden Provider",
"trainingId": "ab40dcda-e253-4ff3-95e9-314e4ce5ba39",
"providerId": "ab40dcda-e253-4ff3-95e9-314e4ce5ba39",
"attendanceDate": "2014-04-23T00:00:00"
},
{
"trainingName": "Economic - Business skills",
"providerName": "Hidden Provider",
"trainingId": "e9467886-42e0-467c-a338-a7fcbc66125f",
"providerId": "e9467886-42e0-467c-a338-a7fcbc66125f",
"attendanceDate": "2012-06-25T00:00:00"
},
{
"trainingName": "GAP - Soil Management & Fertilizer Application",
"providerName": "Hidden Provider",
"trainingId": "17effec3-1284-4609-af41-2252b4dde8b5",
"providerId": "17effec3-1284-4609-af41-2252b4dde8b5",
"attendanceDate": "2012-07-25T00:00:00"
},
{
"trainingName": "GAP - Composting",
"providerName": "Hidden Provider",
"trainingId": "96b16133-51ef-413c-afa5-24f74263126b",
"providerId": "96b16133-51ef-413c-afa5-24f74263126b",
"attendanceDate": "2012-06-25T00:00:00"
},
{
"trainingName": "GAP - Pruning",
"providerName": "Hidden Provider",
"trainingId": "f05554c2-778b-44b6-972e-c2fea01d924f",
"providerId": "f05554c2-778b-44b6-972e-c2fea01d924f",
"attendanceDate": "2013-05-21T00:00:00"
},
{
"trainingName": "GAP - Irrigation & Water Management",
"providerName": "Hidden Provider",
"trainingId": "560be929-134b-4b67-befd-f38fdbb2f7ad",
"providerId": "560be929-134b-4b67-befd-f38fdbb2f7ad",
"attendanceDate": "2012-06-25T00:00:00"
},
{
"trainingName": "GAP - Safety & Hygiene",
"providerName": "Hidden Provider",
"trainingId": "96c36f93-90ce-4378-a9f2-8a8aaa780fd2",
"providerId": "96c36f93-90ce-4378-a9f2-8a8aaa780fd2",
"attendanceDate": "2013-05-21T00:00:00"
},
{
"trainingName": "GAP - Post Harvest",
"providerName": "Hidden Provider",
"trainingId": "d1c45ad7-046b-43de-b65e-012185c17f6a",
"providerId": "d1c45ad7-046b-43de-b65e-012185c17f6a",
"attendanceDate": "2012-06-25T00:00:00"
}
]
}
}
]
}
Upvotes: 0
Views: 122
Answers (1)
Val
Reputation: 217304
Ok, then what you need is simply to declare your trainings property with type: nested in your mapping, like this:
{
"mappings": {
"person": {
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"trainings": {
"type": "nested", <----- add "nested" here
"properties": {
"attendanceDate": {
"type": "date",
"format": "dateOptionalTime"
},
"providerId": {
"type": "string",
"index": "not_analyzed"
},
"trainingId": {
"type": "string",
"index": "not_analyzed"
}
}
}
}
}
}
}
And then you need to change your aggregation a bit to work with your new nested type, like this:
{
"size": 0,
"aggs": {
"trainning_agg": {
"nested": { <----- use nested aggregation
"path": "trainings"
},
"aggs": {
"trainings": {
"terms": {
"field": "trainings.trainingId"
},
"aggs": {
"provider_agg": {
"terms": {
"field": "trainings.providerId"
}
}
}
}
}
}
}
}
And the response will be as you expect:
{
...
"aggregations" : {
"trainning_agg" : {
"doc_count" : 19,
"trainings" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "17effec3-1284-4609-af41-2252b4dde8b5",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "17effec3-1284-4609-af41-2252b4dde8b5",
"doc_count" : 2
} ]
}
}, {
"key" : "560be929-134b-4b67-befd-f38fdbb2f7ad",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "560be929-134b-4b67-befd-f38fdbb2f7ad",
"doc_count" : 2
} ]
}
}, {
"key" : "5aaa2f31-6aa6-4d5a-86ea-8a25cb11247e",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "5aaa2f31-6aa6-4d5a-86ea-8a25cb11247e",
"doc_count" : 2
} ]
}
}, {
"key" : "96b16133-51ef-413c-afa5-24f74263126b",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "96b16133-51ef-413c-afa5-24f74263126b",
"doc_count" : 2
} ]
}
}, {
"key" : "96c36f93-90ce-4378-a9f2-8a8aaa780fd2",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "96c36f93-90ce-4378-a9f2-8a8aaa780fd2",
"doc_count" : 2
} ]
}
}, {
"key" : "ab40dcda-e253-4ff3-95e9-314e4ce5ba39",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "ab40dcda-e253-4ff3-95e9-314e4ce5ba39",
"doc_count" : 2
} ]
}
}, {
"key" : "d1c45ad7-046b-43de-b65e-012185c17f6a",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "d1c45ad7-046b-43de-b65e-012185c17f6a",
"doc_count" : 2
} ]
}
}, {
"key" : "e9467886-42e0-467c-a338-a7fcbc66125f",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "e9467886-42e0-467c-a338-a7fcbc66125f",
"doc_count" : 2
} ]
}
}, {
"key" : "f05554c2-778b-44b6-972e-c2fea01d924f",
"doc_count" : 2,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "f05554c2-778b-44b6-972e-c2fea01d924f",
"doc_count" : 2
} ]
}
}, {
"key" : "c06e0cee-1050-41b6-ac83-ea3a00f0abb7",
"doc_count" : 1,
"provider_agg" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ {
"key" : "c06e0cee-1050-41b6-ac83-ea3a00f0abb7",
"doc_count" : 1
} ]
}
} ]
}
}
}
}
Upvotes: 1
Related Questions
- ElasticSearch - issue with sub term aggregation with array fields
- Elasticsearch nested query with aggregation using nested term doesn't return any bucket
- Nested Aggregation in Nest Elastic Search
- Elasticsearch 7.8 Nested Aggregation not returning correct data
- Elasticsearch: terms aggregations on doubly nested object
- Elasticsearch: reverse_nested aggregation under deep nested aggregation doesn't work
- Can't nest terms aggregations more than two deep without further aggs being ignored?
- elasticsearch 5 nested term aggregation does not work
- Elasticsearch aggregation doesn't work with nested-type fields
- Aggregation on nested field not working