Geek Answers Handbook

Top Hit Aggregation Result Group

{
    "took": 53,
    "timed_out": false,
    "_shards": {
        "total": 2,
        "successful": 2,
        "failed": 0
    },
    "hits": {
        "total": 6,
        "max_score": 1.0,
        "hits": [{
            "_index": "db",
            "_type": "users",
            "_id": "AVOiyjHmzUObmc5euUGS",
            "_score": 1.0,
            "_source": {
                "user": "james",
                "lastvisited": "2016/01/20 02:03:11",
                "browser": "chrome",
                "offercode": "JB20"
            }
        }, {
            "_index": "db",
            "_type": "users",
            "_id": "AVOiyjIQzUObmc5euUGT",
            "_score": 1.0,
            "_source": {
                "user": "james",
                "lastvisited": "2016/01/20 03:04:15",
                "browser": "firefox",
                "offercode": "JB20,JB50"
            }
        }, {
            "_index": "db",
            "_type": "users",
            "_id": "AVOiyjIlzUObmc5euUGU",
            "_score": 1.0,
            "_source": {
                "user": "james",
                "lastvisited": "2016/01/21 00:15:21",
                "browser": "chrome",
                "offercode": "JB20,JB50,JB100"
            }
        }, {
            "_index": "db",
            "_type": "users",
            "_id": "AVOiyjJKzUObmc5euUGW",
            "_score": 1.0,
            "_source": {
                "user": "peter",
                "lastvisited": "2016/01/20 02:32:22",
                "browser": "chrome",
                "offercode": "JB20,JB50,JB100"
            }
        }, {
            "_index": "db",
            "_type": "users",
            "_id": "AVOiy4jhzUObmc5euUGX",
            "_score": 1.0,
            "_source": {
                "user": "james",
                "lastvisited": "2016/01/19 02:03:11",
                "browser": "chrome",
                "offercode": ""
            }
        }, {
            "_index": "db",
            "_type": "users",
            "_id": "AVOiyjI2zUObmc5euUGV",
            "_score": 1.0,
            "_source": {
                "user": "adams",
                "lastvisited": "2016/01/20 00:12:11",
                "browser": "chrome",
                "offercode": "JB10"
            }
        }]
    }
}

I want to filter a document based on the last time a user visited and get the latest available document for an individual user, and then group all the filtered documents based on the offer code.

I get the most recent user document available by performing aggregation of afits. But I can’t group the results of aggregating the afits using the offer code.

ES request to get the most recent user document

    curl -XGET localhost:9200/account/users/_search?pretty -d'{
 "size": "0",
 "query": {
    "bool": {
      "must": {
        "range": {
          "lastvisited": {
            "gte": "2016/01/19",
             "lte": "2016/01/21" 
          }
        }
      }
    }
 },
"aggs": {
  "lastvisited_users": {
      "terms": {
          "field": "user"
     }
    ,
    "aggs": {
       "top_user_hits": {
           "top_hits": {
              "sort": [
               {
                 "lastvisited": {
                   "order": "desc"
                 }
               }
             ],
             "_source": {
                 "include": [
                     "user","offercode","lastvisited"
                  ]
              },
              "size": 1
            }
        }   
    }
  }
}}'

ES output

   {
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 6,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "lastvisited_users" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [ {
        "key" : "james",
        "doc_count" : 3,
        "top_user_hits" : {
          "hits" : {
            "total" : 3,
            "max_score" : null,
            "hits" : [ {
              "_index" : "accounts",
              "_type" : "users",
              "_id" : "AVOtexIEz1WBU8vnnZ2d",
              "_score" : null,
              "_source" : {
                "lastvisited" : "2016/01/20 03:04:15",
                "offercode" : "JB20,JB50",
                "user" : "james"
              },
              "sort" : [ 1453259055000 ]
            } ]
          }
        }
      }, {
        "key" : "adams",
        "doc_count" : 1,
        "top_user_hits" : {
          "hits" : {
            "total" : 1,
            "max_score" : null,
            "hits" : [ {
              "_index" : "accounts",
              "_type" : "users",
              "_id" : "AVOtexJMz1WBU8vnnZ2h",
              "_score" : null,
              "_source" : {
                "lastvisited" : "2016/01/20 00:12:11",
                "offercode" : "JB10",
                "user" : "adams"
              },
              "sort" : [ 1453248731000 ]
            } ]
          }
        }
      }, {
        "key" : "adamsnew",
        "doc_count" : 1,
        "top_user_hits" : {
          "hits" : {
            "total" : 1,
            "max_score" : null,
            "hits" : [ {
              "_index" : "accounts",
              "_type" : "users",
              "_id" : "AVOtexJhz1WBU8vnnZ2i",
              "_score" : null,
              "_source" : {
                "lastvisited" : "2016/01/20 00:12:11",
                "offercode" : "JB1010,aka10",
                "user" : "adamsnew"
              },
              "sort" : [ 1453248731000 ]
            } ]
          }
        }
      }, {
        "key" : "peter",
        "doc_count" : 1,
        "top_user_hits" : {
          "hits" : {
            "total" : 1,
            "max_score" : null,
            "hits" : [ {
              "_index" : "accounts",
              "_type" : "users",
              "_id" : "AVOtexIoz1WBU8vnnZ2f",
              "_score" : null,
              "_source" : {
                "lastvisited" : "2016/01/20 02:32:22",
                "offercode" : "JB20,JB50,JB100",
                "user" : "peter"
              },
              "sort" : [ 1453257142000 ]
            } ]
          }
        }
      } ]
    }
  }
}

Now I want to aggregate the results of aggregating the afits.

Expected Result

{
  "offercode_grouped": {
      "JB20": 1,
      "JB10": 1,
      "JB20,JB50": 1,
      "JB20,JB50,JB100": 2,
      "":1
   }
}

I tried using Pipeline aggregation, but I don’t know how to group the result of aggregating the afits.

+4
3

, . , "".

function_score query, sampler aggregation terms aggregation.

curl -s -XPUT "http://127.0.0.1:9200/stackoverflow" -d'
{
  "mappings": {
    "document": {
      "properties": {
        "name": {
          "type": "string", 
          "index": "not_analyzed"
        },
        "lastvisited": {
          "type": "date",
          "format": "YYYY/MM/dd HH:mm:ss"
        },
        "browser": {
          "type": "string",
          "index": "not_analyzed"
        },
        "offercode": {
          "type": "string",
          "index": "not_analyzed"
        }
      }
    }
  }
}'


curl -s -XPUT "http://127.0.0.1:9200/stackoverflow/document/1?routing=james" -d'
{
  "user": "james",
  "lastvisited": "2016/01/20 02:03:11",
  "browser": "chrome",
  "offercode": "JB20"
}'

curl -s -XPUT "http://127.0.0.1:9200/stackoverflow/document/2?routing=james" -d'
{
  "user": "james",
  "lastvisited": "2016/01/20 03:04:15",
  "browser": "firefox",
  "offercode": "JB20,JB50"
}'

curl -s -XPUT "http://127.0.0.1:9200/stackoverflow/document/3?routing=james" -d'
{
  "user": "james",
  "lastvisited": "2016/01/21 00:15:21",
  "browser": "chrome",
  "offercode": "JB20,JB50,JB100"
}'

curl -s -XPUT "http://127.0.0.1:9200/stackoverflow/document/4?routing=peter" -d'
{
  "user": "peter",
  "lastvisited": "2016/01/20 02:32:22",
  "browser": "chrome",
  "offercode": "JB20,JB50,JB100"
}'

curl -s -XPUT "http://127.0.0.1:9200/stackoverflow/document/5?routing=james" -d'
{
  "user": "james",
  "lastvisited": "2016/01/19 02:03:11",
  "browser": "chrome",
  "offercode": ""
}'

curl -s -XPUT "http://127.0.0.1:9200/stackoverflow/document/6?routing=adams" -d'
{
  "user": "adams",
  "lastvisited": "2016/01/20 00:12:11",
  "browser": "chrome",
  "offercode": "JB10"
}'


curl -XPOST "http://127.0.0.1:9200/stackoverflow/_search" -d'
{
  "query": {
    "function_score": {
      "boost_mode": "replace", // we need to replace document score with the result of the functions
      "query": {
        "bool": {
          "filter": [
            {
              "range": { // get documents within the date range
                "lastvisited": {
                  "gte": "2016/01/19 00:00:00",
                  "lte": "2016/01/21 23:59:59"
                }
              }
            }
          ]
        }
      },
      "functions": [
        {
          "linear": {
            "lastvisited": {
              "origin": "2016/01/21 23:59:59", // same as lastvisited lte filter
              "scale": "2d" // set the scale - please, see elasticsearch docs for more info https://www.elastic.co/guide/en/elasticsearch/reference/2.3/query-dsl-function-score-query.html#function-decay
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "user": {
      "sampler": { // get top scored document per user
        "field": "user",
        "max_docs_per_value": 1
      },
      "aggs": {
        "offers": { // aggregate user documents per `offercode`
          "terms": {
            "field": "offercode"
          }
        }
      }
    }
  },
  "size": 0
}'


{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 6,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "user": {
      "doc_count": 3,
      "offers": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "JB20,JB50,JB100",
            "doc_count": 2
          },
          {
            "key": "JB10",
            "doc_count": 1
          }
        ]
      }
    }
  }
}

, routing . , sampler . , - .

sampler . . , function_score query . field_value_factor, - - , .

: field_value_factor, , _score. . https://github.com/elastic/elasticsearch/issues/11872. decay clintongormley . decay origin. , 1 1 origin _score. (. ).

. , , . , ...

+3

This may solve your problem:

SELECT offercode, count(offercode) 
FROM users AS u1 
WHERE u1.ID = (SELECT u2.ID FROM users AS u2 WHERE u2.user = u1.user ORDER BY u2.lastvisited DESC LIMIT 1) 
AND u1.lastvisited >= "2016/01/20" 
AND ORDER BY lastvisited ASC AND GROUP BY offercode;
-2
source

More articles:


All Articles