Elastic search flat index structure-CodePudding

My flat data looks like this:

[
  {
    user: "User1",
    fullName: "George Mann",
    moneyUsed: 12.0,
    month: "2022-01-31"
  },
  {
    user: "User1",
    fullName: "George Man",
    moneyUsed: 13.0,
    month: "2022-02-28"
  },
  {
    user: "User1",
    fullName: "George Man",
    moneyUsed: 14.0,
    month: "2022-03-31"
  },
  {
    user: "User2",
    fullName: "Mary Mann",
    moneyUsed: 17.0,
    month: "2022-01-31"
  }
]

This type of structure allows me easily to perform aggregations on the amounts of money spent by a user as each month for each user is stored separately. The problem I have is when I try to display all unique users in a users table (user, fullName). For example if I would have 100 unique users I am not sure what is the best approach to show them considering sorting and paging.

I was thinking of adding a new field "mostRecent" for the most recent month for each user. In this way I can easily identify the most recent entry in ES for each user.

What is the best approach to implement this scenario?

CodePudding user response：

You can use field collapsing.

I assumend that user's type is keyword and month's type is date in the index mapping. A query like this should work for you. You can also paginate using size and from parameters.

{
  "from": 0,
  "size": 1, 
  "sort": [
    {
      "month": { 
        "order": "desc"
      }
    }
  ],
  "collapse": {
    "field": "user"
  }
}