ARRANGEMENT AND FILTER OF DOCUMENT INCLUDING ARRAY TO ELEMENT IN ARRAY

What do you want to do

I would like to print the expected results below with the MongoDB command for the document to be processed.
(Some documents do not contain some arrays)

Documents to be processed

{
    "_id"—ObjectId ("60ddc26b03edfb7a6b424f10"),
    "member": [
        {
            "joinDate": "2021-07-01 12:00:00.000",
            "username": "John"
        },
        {
            "joinDate": "2021-07-01 13:00:00.000",
            "username": "Maria"
        }
    ],
    "createdAt": "2021-07-01 11:00",
    "roomName": "roomA",
}
{
    "_id"—ObjectId("60ddbcccd495870939756dcb"),
    "member": [
        {
            "joinDate": "2021-07-01 14:00:00.000",
            "userName": "Jack"
        },
        {
            "joinDate": "2021-07-01 11:00:00.000",
            "userName": "George"
        }
    ],
    "createdAt": "2021-07-01 10:00",
    "roomName": "roomB"
}
{
    "_id"—ObjectId("60ddbcccd495870939756dcc"),
    "createdAt": "2021-07-01 12:00",
    "roomName": "roomC"
}

Output results that meet the following conditions

joinDate deploys two elements of the array in the document to only the newest elements
Also rename the element of the array (add a delay to the prefix)
Order documents in descending order of latestJoinDate
Do not print documents that do not have member elements

Expect Results

{
    "_id"—ObjectId("60ddbcccd495870939756dcb"),
    "latestJoinDate": "2021-07-01 14:00:00.000",
    "latestUserName": "Jack",
    "createdAt": "2021-07-01 10:00",
    "roomName": "roomB"
}
{
    "_id"—ObjectId ("60ddc26b03edfb7a6b424f10"),
    "latestJoinDate": "2021-07-01 13:00:00.000",
    "latestUserName": "Maria",
    "createdAt": "2021-07-01 11:00",
    "roomName": "roomA",
}

json mongodb

2022-09-30 10:14

1 Answers

I solved myself!

Below is the query I was looking for.

 db.collection.aggregate([
  {
    $match: {
      member: {
        $exists —true
      }
    }
  },
  {
    $addFields: {
      member: {
        $arrayElemAt: [
          "$member",
          {
            $indexOfArray: [
              "$member.joinDate",
              {
                $max: "$member.joinDate"
              }
            ]
          }
        ]
      }
    }
  },
  {
    $project: {
      createdAt: 1,
      roomName: 1,
      latestJoinDate: "$member.joinDate",
      latestUserName: "$member.userName"
    }
  },
  {
    $sort: {
      latestJoinDate: -1
    }
  }
])

https://mongoplayground.net/p/mYE2Q1tk4ij

2022-09-30 10:14

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656