0

I have the below document and search query when i execute the query elastic is fetching the unmatched documents scenario is for the matched exceptionId it should do the aggregation but it is fetching all the documents and also aggregation is not working.

1) item

2) location

3) transportMode

4) sourceLocation

5) shipDate

6) arrivalDate

Mapping:

{
    "mappings": {
        "recommendations": {
            "properties": {
                "recommendations": {
                    "type": "nested"
                }
            }
        }
    }
}

The document:

  {
    "recommendations": [
        {
            "id": "578bd845-3ca9-495f-bc13-dbcc48e8f415",
            "tenantId": "0d474f0a-f060-4828-b2ec-a81c4b27fa2e",
            "clusterId": "1",
            "eventId": "1",
            "exceptionId": "1",
            "eventType": "Delayed",
            "item": "Item1",
            "location": "DC1",
            "dueDate": "2019-01-10T05:30:00.000+0530",
            "exceptionQuantity": 100,
            "recommendationType": "stockTransfer",
            "customerName": "Walmart",
            "primaryRecommendation": true,
            "priority": 1,
            "sourceLocation": "DC2",
            "transferQuantity": 100,
            "shipDate": "2019-01-09T05:30:00.000+0530",
            "arrivalDate": "2019-01-10T05:30:00.000+0530",
            "transportMode": "Road",
            "transferCost": 500,
            "maxQtyAvailableForTransfer": 100,
            "totalQtyAtSource": 100,
            "operation": "Road-Item1-from-DC2-to-DC1",
            "peggedStockDemandId": "1",
            "revenueRecovered": "20000.0"
        },
        {
            "id": "578bd845-3ca9-495f-bc13-dbcc48e8f415",
            "tenantId": "0d474f0a-f060-4828-b2ec-a81c4b27fa2e",
            "clusterId": "1",
            "eventId": "1",
            "exceptionId": "1",
            "eventType": "Delayed",
            "item": "Item1",
            "location": "DC1",
            "dueDate": "2019-01-10T05:30:00.000+0530",
            "exceptionQuantity": 100,
            "recommendationType": "stockTransfer",
            "customerName": "Walmart",
            "primaryRecommendation": true,
            "priority": 1,
            "sourceLocation": "DC2",
            "transferQuantity": 100,
            "shipDate": "2019-01-09T05:30:00.000+0530",
            "arrivalDate": "2019-01-10T05:30:00.000+0530",
            "transportMode": "Road",
            "transferCost": 500,
            "maxQtyAvailableForTransfer": 100,
            "totalQtyAtSource": 100,
            "operation": "Road-Item1-from-DC2-to-DC1",
            "peggedStockDemandId": "1",
            "revenueRecovered": "20000.0"
        },
        {
            "id": "578bd845-3ca9-495f-bc13-dbcc48e8f415",
            "tenantId": "0d474f0a-f060-4828-b2ec-a81c4b27fa2e",
            "clusterId": "1",
            "eventId": "1",
            "exceptionId": "2",
            "eventType": "Delayed",
            "item": "Item1",
            "location": "DC1",
            "dueDate": "2019-01-10T05:30:00.000+0530",
            "exceptionQuantity": 100,
            "recommendationType": "stockTransfer",
            "customerName": "Walmart",
            "primaryRecommendation": true,
            "priority": 1,
            "sourceLocation": "DC2",
            "transferQuantity": 100,
            "shipDate": "2019-01-09T05:30:00.000+0530",
            "arrivalDate": "2019-01-10T05:30:00.000+0530",
            "transportMode": "Road",
            "transferCost": 500,
            "maxQtyAvailableForTransfer": 100,
            "totalQtyAtSource": 100,
            "operation": "Road-Item1-from-DC2-to-DC1",
            "peggedStockDemandId": "1",
            "revenueRecovered": "20000.0"
        }
    ]
}

The Query:

   {
    "aggregations": {
        "exceptionIds": {
            "filter": {
                "terms": {
                    "exceptionId": [
                        "1"
                    ],
                    "boost": 1
                }
            },
            "aggregations": {
                "by_exceptionId": {
                    "terms": {
                        "field": "recommendations.exceptionId.keyword",
                        "size": 10,
                        "min_doc_count": 1,
                        "shard_min_doc_count": 0,
                        "show_term_doc_count_error": false,
                        "order": [
                            {
                                "_count": "desc"
                            },
                            {
                                "_key": "asc"
                            }
                        ]
                    },
                    "aggregations": {
                        "by_item": {
                            "terms": {
                                "field": "recommendations.item.keyword",
                                "size": 10,
                                "min_doc_count": 1,
                                "shard_min_doc_count": 0,
                                "show_term_doc_count_error": false,
                                "order": [
                                    {
                                        "_count": "desc"
                                    },
                                    {
                                        "_key": "asc"
                                    }
                                ]
                            },
                            "aggregations": {
                                "by_destination": {
                                    "terms": {
                                        "field": "recommendations.location.keyword",
                                        "size": 10,
                                        "min_doc_count": 1,
                                        "shard_min_doc_count": 0,
                                        "show_term_doc_count_error": false,
                                        "order": [
                                            {
                                                "_count": "desc"
                                            },
                                            {
                                                "_key": "asc"
                                            }
                                        ]
                                    },
                                    "aggregations": {
                                        "by_trans": {
                                            "terms": {
                                                "field": "recommendations.transportMode.keyword",
                                                "size": 10,
                                                "min_doc_count": 1,
                                                "shard_min_doc_count": 0,
                                                "show_term_doc_count_error": false,
                                                "order": [
                                                    {
                                                        "_count": "desc"
                                                    },
                                                    {
                                                        "_key": "asc"
                                                    }
                                                ]
                                            },
                                            "aggregations": {
                                                "by_sourcelocation": {
                                                    "terms": {
                                                        "field": "recommendations.sourceLocation.keyword",
                                                        "size": 10,
                                                        "min_doc_count": 1,
                                                        "shard_min_doc_count": 0,
                                                        "show_term_doc_count_error": false,
                                                        "order": [
                                                            {
                                                                "_count": "desc"
                                                            },
                                                            {
                                                                "_key": "asc"
                                                            }
                                                        ]
                                                    },
                                                    "aggregations": {
                                                        "by_shipdate": {
                                                            "terms": {
                                                                "field": "recommendations.shipDate.keyword",
                                                                "size": 10,
                                                                "min_doc_count": 1,
                                                                "shard_min_doc_count": 0,
                                                                "show_term_doc_count_error": false,
                                                                "order": [
                                                                    {
                                                                        "_count": "desc"
                                                                    },
                                                                    {
                                                                        "_key": "asc"
                                                                    }
                                                                ]
                                                            },
                                                            "aggregations": {
                                                                "by_arrival": {
                                                                    "terms": {
                                                                        "field": "recommendations.arrivalDate.keyword",
                                                                        "size": 10,
                                                                        "min_doc_count": 1,
                                                                        "shard_min_doc_count": 0,
                                                                        "show_term_doc_count_error": false,
                                                                        "order": [
                                                                            {
                                                                                "_count": "desc"
                                                                            },
                                                                            {
                                                                                "_key": "asc"
                                                                            }
                                                                        ]
                                                                    },
                                                                    "aggregations": {
                                                                        "quantity": {
                                                                            "sum": {
                                                                                "field": "recommendations.stockTransfer.transferQuantity"
                                                                            }
                                                                        },
                                                                        "transfercost": {
                                                                            "sum": {
                                                                                "field": "recommendations.stockTransfer.transferCost"
                                                                            }
                                                                        },
                                                                        "revenueRecovered": {
                                                                            "sum": {
                                                                                "field": "recommendations.stockTransfer.revenueRecovered"
                                                                            }
                                                                        }
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

The Response:

{
    "took": 3,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 1,
        "max_score": 1.0,
        "hits": [
            {
                "_index": "testing",
                "_type": "recommendations",
                "_id": "1",
                "_score": 1.0,
                "_source": {
                    "recommendations": [
                        {
                            "id": "578bd845-3ca9-495f-bc13-dbcc48e8f415",
                            "tenantId": "0d474f0a-f060-4828-b2ec-a81c4b27fa2e",
                            "clusterId": "1",
                            "eventId": "1",
                            "exceptionId": "1",
                            "eventType": "Delayed",
                            "item": "Item1",
                            "location": "DC1",
                            "dueDate": "2019-01-10T05:30:00.000+0530",
                            "exceptionQuantity": 100,
                            "recommendationType": "stockTransfer",
                            "customerName": "Walmart",
                            "primaryRecommendation": true,
                            "priority": 1,
                            "sourceLocation": "DC2",
                            "transferQuantity": 100,
                            "shipDate": "2019-01-09T05:30:00.000+0530",
                            "arrivalDate": "2019-01-10T05:30:00.000+0530",
                            "transportMode": "Road",
                            "transferCost": 500,
                            "maxQtyAvailableForTransfer": 100,
                            "totalQtyAtSource": 100,
                            "operation": "Road-Item1-from-DC2-to-DC1",
                            "peggedStockDemandId": "1",
                            "revenueRecovered": "20000.0"
                        },
                        {
                            "id": "578bd845-3ca9-495f-bc13-dbcc48e8f415",
                            "tenantId": "0d474f0a-f060-4828-b2ec-a81c4b27fa2e",
                            "clusterId": "1",
                            "eventId": "1",
                            "exceptionId": "2",
                            "eventType": "Delayed",
                            "item": "Item1",
                            "location": "DC1",
                            "dueDate": "2019-01-10T05:30:00.000+0530",
                            "exceptionQuantity": 100,
                            "recommendationType": "stockTransfer",
                            "customerName": "Walmart",
                            "primaryRecommendation": true,
                            "priority": 1,
                            "sourceLocation": "DC2",
                            "transferQuantity": 100,
                            "shipDate": "2019-01-09T05:30:00.000+0530",
                            "arrivalDate": "2019-01-10T05:30:00.000+0530",
                            "transportMode": "Road",
                            "transferCost": 500,
                            "maxQtyAvailableForTransfer": 100,
                            "totalQtyAtSource": 100,
                            "operation": "Road-Item1-from-DC2-to-DC1",
                            "peggedStockDemandId": "1",
                            "revenueRecovered": "20000.0"
                        }
                    ]
                }
            }
        ]
    },
    "aggregations": {
        "exceptionIds": {
            "doc_count": 0,
            "by_exceptionId": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
            }
        }
    }
}
P.J.Meisch
  • 18,013
  • 6
  • 50
  • 66
Rahul
  • 101
  • 1
  • 13

1 Answers1

1

Using your mapping and creating recommendation of type "nested". You need to use nested filter in aggregation. Using nested type you can treat each object of array as a separate type otherwise they will be flattened out.

Example.

Doc 1: "id":"1" "recommendations":[ { "eventId": "1", "exceptionId":"1" }, { "eventId": "2", "exceptionId":"2" } ]

Doc 2 :

"id":"1" "recommendations":[ { "eventId": "2", "exceptionId":"1" } ]

Consider above 2 documents with recommendation mapped as object. If you will filter on document which have eventId:2 and exceptionId:1, it will return both documents as object types are flattened out and relation between fields of object is not maintained, it is considered as a single document.

When recommendation is mapped as"nested type, document 2 will be returned. Even if you have filtered document using nested type in query. In aggregation it again has to be used as all the objects of array are returned by query .

Mapping

{
  "index10" : {
    "mappings" : {
      "properties" : {
        "recommendations" : {
          "type" : "nested",
          "properties" : {
            "arrivalDate" : {
              "type" : "date"
            },
            "clusterId" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "customerName" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "dueDate" : {
              "type" : "date"
            },
            "eventId" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "eventType" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "exceptionId" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "exceptionQuantity" : {
              "type" : "long"
            },
            "id" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "item" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "location" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "maxQtyAvailableForTransfer" : {
              "type" : "long"
            },
            "operation" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "peggedStockDemandId" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "primaryRecommendation" : {
              "type" : "boolean"
            },
            "priority" : {
              "type" : "long"
            },
            "recommendationType" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "revenueRecovered" : {
              "type" : "float"
            },
            "shipDate" : {
              "type" : "date"
            },
            "sourceLocation" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "tenantId" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            },
            "totalQtyAtSource" : {
              "type" : "long"
            },
            "transferCost" : {
              "type" : "long"
            },
            "transferQuantity" : {
              "type" : "long"
            },
            "transportMode" : {
              "type" : "text",
              "fields" : {
                "keyword" : {
                  "type" : "keyword",
                  "ignore_above" : 256
                }
              }
            }
          }
        }
      }
    }
  }
}

Query:

{
  "size": 0, --> hits will not be returned
  "aggregations": {
    "exceptionIds": {
      "nested": {
        "path": "recommendations"  --> nested agrregation on nested
      },
      "aggs": {
        "recomm": {
          "filter": {
            "terms": {
              "recommendations.exceptionId.keyword": [ --> filter on exceptionId
                "1"
              ]
            }
          },
          "aggs": {
            "revenueRecovered": {
              "sum": {
                "field": "recommendations.revenueRecovered"
              }
            },
            "transfercost": {
              "sum": {
                "field": "recommendations.transferCost"
              }
            },
            "transferQuantity": {
              "sum": {
                "field": "recommendations.transferQuantity"
              }
            }
          }
        }
      }
    }
  }
}

Result:

"aggregations" : {
    "exceptionIds" : {
      "doc_count" : 2,
      "recomm" : {
        "doc_count" : 1,
        "transferQuantity" : {
          "value" : 100.0
        },
        "transfercost" : {
          "value" : 500.0
        },
        "revenueRecovered" : {
          "value" : 20000.0
        }
      }
    }
  }
jaspreet chahal
  • 8,817
  • 2
  • 11
  • 29
  • But yet your query returns all the objects not matched one and also aggregation is not working when there is exception id match for example for exceptionId 1 if there are 2 docs then aggregation should return below result revenueRecovered -> 4000 totalCost -> 100 quantity -> 200, But the query not returning the aggregation counts and i have updated the question to include the updated document. – Rahul May 03 '20 at 10:23
  • could you please correct me in the mapping if something is wrong – Rahul May 03 '20 at 12:10
  • @Rahul If you are checking in hits. I have added query part you will get matching docs in inner_hits. In aggregation do you want to sum revenueRecovered,totalCost and quantity by exception id? – jaspreet chahal May 03 '20 at 12:24
  • Yes! that is the main goal here and hits has to be ignored – Rahul May 03 '20 at 12:29
  • simply for the matched exceptionId it should do the aggregation for mentioned fields – Rahul May 03 '20 at 12:35
  • @Rahul I have updated answer. My answer is based on mapping I created based on document you added in question. – jaspreet chahal May 03 '20 at 12:43
  • getting the mapping error and my elastic version is 6.3.2 – Rahul May 03 '20 at 12:53
  • @Rahul You accepted the answer did it work for you? – jaspreet chahal May 03 '20 at 13:08
  • yeah i had to fix the mapping for the elastic version 6.3.2 but in the query i need to aggregate on the fields which have mentioned in the question and query which i have posted contains this logic can this extra logic works on your query – Rahul May 03 '20 at 13:16
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/213016/discussion-between-jaspreet-chahal-and-rahul). – jaspreet chahal May 03 '20 at 13:20