Top value aggregation in elasticsearch-CodePudding

My data looks like this

[
  {
    "user": "User1",
    "moneyUsed": 1.0,
    "balance": 100.0, 
    "month": "2022-01-31"
  },
  {
    "user": "User1",
    "moneyUsed": 2.0,
    "balance": 300.0, 
    "month": "2022-02-28"
  },
  {
    "user": "User1",
    "moneyUsed": 3.0,
    "balance": 90.0, 
    "month": "2022-03-31"
  },
  {
    "user": "User2",
    "moneyUsed": 4.0,
    "balance": 50.0, 
    "month": "2022-01-31"
  },
  {
    "user": "User2",
    "moneyUsed": 5.0,
    "balance": 80.0, 
    "month": "2022-02-28"
  },
  {
    "user": "User2",
    "moneyUsed": 6.0,
    "balance": 190.0, 
    "month": "2022-03-31"
  }
]

I want get the balance for the latest month ordered by the user with the highest balance. At the same time I want to sum the money used.

The result should look like this:

[
  {
        "user": "User2",
        "sumMoneyUsed": 15.0,
        "currentBalance": 190.0
  }
]

I've thought about the top_hits aggregation where I select the top document ordered by month. Unfortunately with this solution I can't use the order by.

Another solution would be to do two queries. One where I identify the user with the highest balance in the last month and the second query where I do the sums and display the balance.

CodePudding user response：

Try this:

{
  "size": 0,
  "aggs": {
    "users": {
      "terms": {
        "field": "user.keyword",
        "size": 10
      },
      "aggs": {
        "sumMoneyUsed": {
          "sum": {
            "field": "moneyUsed"
          }
        },
        "currentBalance": {
          "max": {
            "field": "balance"
          }
        }
      }
    }
  }
}

CodePudding user response：

This should work for you:

{
  "size": 0,
  "aggs": {
    "users": {
      "terms": {
        "field": "user.keyword",
        "size": 10
      },
      "aggs": {
        "sumMoneyUsed": {
          "sum": {
            "field": "moneyUsed"
          }
        },
        "currentBalanceByLastMonth": {
          "terms": {
            "field": "month",
            "size": 1,
            "order": { "_term": "desc" }
          },
          "aggs": {
            "maxBalance": {
              "max": {
                "field": "balance"
              }
            }
          }
        }
      }
    }
  }
}