I'm quite new to Elasticsearch and want to accomplish the following using term aggregation:
- Get a list of products matching a category and selected specifications (features)
- A list of specifications and possible values, with a count of products you would get if you would select it
- The list of specifications should always contain all the specifications of the whole dataset even if it has a count of 0, but only within the selected category
- Products must contain at least one of the selected values of a selected specification
This is my mapping:
{
"mapping": {
"product": {
"properties": {
"category": {
"type": "keyword"
},
"features": {
"properties": {
"breedte": {
"properties": {
"id": {
"type": "long"
},
"measure": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"value": {
"type": "float"
}
}
},
"diepte": {
"properties": {
"id": {
"type": "long"
},
"measure": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"value": {
"type": "float"
}
}
},
"hoogte": {
"properties": {
"id": {
"type": "long"
},
"measure": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"value": {
"type": "float"
}
}
}
}
},
"name": {
"type": "keyword"
},
"url": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
}
This is what I currently have ('breedte' is selected with 2 values, 'hoogte' with 1)
GET _search
{
"from": 0,
"size": 15,
"sort":{
"name":"desc"
},
"stored_fields":[
"_id",
"name"
],
"query":{
"constant_score":{
"filter":{
"bool":{
"must":[
{
"term":{
"category":"Gaskookplaat"
}
},
{
"term":{
"features.hoogte.value":"55"
}
}
],
"should":[
{
"term":{
"features.breedte.value":"750"
}
},
{
"term":{
"features.breedte.value":"590"
}
}
]
}
}
}
},
"aggs":{
"categories":{
"filter":{
"bool":{
"should":[
]
}
},
"aggs":{
"breedte":{
"terms":{
"field":"features.breedte.value",
"min_doc_count":0
},
"meta":{
"id":"id",
"measure":"mm",
"title":"Breedte"
}
},
"hoogte":{
"terms":{
"field":"features.hoogte.value",
"min_doc_count":0
},
"meta":{
"value":"features.hoogte.value"
}
}
}
}
}
}
The results
{
"took": 10,
"timed_out": false,
"_shards": {
"total": 6,
"successful": 5,
"skipped": 0,
"failed": 1,
"failures": [
{
"shard": 0,
"index": ".kibana",
"node": "2fjW10M0RSa_imRTIMutww",
"reason": {
"type": "query_shard_exception",
"reason": "No mapping found for [name] in order to sort on",
"index_uuid": "x4687TCDRTalALulM9xLSg",
"index": ".kibana"
}
}
]
},
"hits": {
"total": 9,
"max_score": null,
"hits": [
{
"_index": "products",
"_type": "product",
"_id": "2014195",
"_score": null,
"sort": [
"5ZTAKGF87"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2014049",
"_score": null,
"sort": [
"5RVSAKGF87"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2023697",
"_score": null,
"sort": [
"9ZTAKG95"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2009530",
"_score": null,
"sort": [
"RVSAKG959"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2016478",
"_score": null,
"sort": [
"RVSAKG675"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2015225",
"_score": null,
"sort": [
"5IXBBGW7"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2010352",
"_score": null,
"sort": [
"75IXBGW"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2018012",
"_score": null,
"sort": [
"0IXBBGW6"
]
},
{
"_index": "products",
"_type": "product",
"_id": "2009793",
"_score": null,
"sort": [
"60IXBGW"
]
}
]
},
"aggregations": {
"categories": {
"meta": {},
"doc_count": 9,
"hoogte": {
"meta": {
"value": "features.hoogte.value"
},
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 55,
"doc_count": 9
},
{
"key": 5,
"doc_count": 0
},
{
"key": 6,
"doc_count": 0
},
{
"key": 8,
"doc_count": 0
},
{
"key": 10,
"doc_count": 0
},
{
"key": 13,
"doc_count": 0
},
{
"key": 16,
"doc_count": 0
},
{
"key": 18,
"doc_count": 0
},
{
"key": 30,
"doc_count": 0
},
{
"key": 32,
"doc_count": 0
}
]
},
"breedte": {
"meta": {
"measure": "mm",
"id": "id",
"title": "Breedte"
},
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 750,
"doc_count": 5
},
{
"key": 590,
"doc_count": 4
},
{
"key": 70,
"doc_count": 0
},
{
"key": 83,
"doc_count": 0
},
{
"key": 85,
"doc_count": 0
},
{
"key": 94,
"doc_count": 0
},
{
"key": 98,
"doc_count": 0
},
{
"key": 110,
"doc_count": 0
},
{
"key": 120,
"doc_count": 0
},
{
"key": 165,
"doc_count": 0
}
]
}
}
}
}
I think I am on the right track and the results seem quite good, however the values of each specification are OR and not AND, causing the other values of a specification to show 0 as count if one value is selected. What I want is the count I would get when I would add that value to the selection. I hope someone can point me in the right direction.
