Filtering, mapping and sorting documents in aggregation pipeline

Lets say I have an aggregation pipeline, and I am being able to get the required details as needed but I need to sort values of key named visits in descending order and then cherry pick items in it.

                {
                    $match: {
                        mobile_number: "1234567890",
                    }
                },
                {
                    $lookup: {
                        from: "visitor_logs",
                        localField: "_id",
                        foreignField: "visitor_id",
                        as: "visits",
                    }
                },
                {
                    $project: {
                        _id: 0,
                        visitor_id: "$_id",
                        first_name: "$first_name",
                        last_name: "$last_name",
                        mobile_number: "$mobile_number",
                        visits: {
                            $filter: {
                                input: "$visits",
                                as: "visit",
                                cond: {
                                    $and: [
                                        {$gte: ["$$visit.in_time", "1610609615"]},
                                        {$lte: ["$$visit.in_time", "1610615328"]},
                                    ]
                                }
                            },
                        },
                    }
                },
            ]);

Sample response

{
    "type": "SUCCESS",
    "log": [
        {
            "visitor_id": "5ffff1354351be2c600c4f94",
            "first_name": "Ayan",
            "last_name": "Dey",
            "mobile_number": "1234567890",
            "visits": [
                {
                    "_id": "5ffff3df82dc1a0e90d89a5c",
                    "in_time": "1610609615",
                    "out_time": "1610609671",
                    "checked_in_status": false,
                    "visitor_id": "5ffff1354351be2c600c4f94",
                    "visit_purpose": "Test",
                    "person_to_meet": "Someone new",
                    "__v": 0
                },
                {
                    "_id": "5ffff41a82dc1a0e90d89a5d",
                    "in_time": "1610609615",
                    "out_time": "1610609730",
                    "checked_in_status": false,
                    "visitor_id": "5ffff1354351be2c600c4f94",
                    "visit_purpose": "Test",
                    "person_to_meet": "Someone new",
                    "__v": 0
                },
                {
                    "_id": "5ffff45a82dc1a0e90d89a5e",
                    "in_time": "1610609615",
                    "out_time": "1610609919",
                    "checked_in_status": false,
                    "visitor_id": "5ffff1354351be2c600c4f94",
                    "visit_purpose": "Test",
                    "person_to_meet": "Someone new",
                    "__v": 0
                }
            ]
        }
    ]
}

Now, what I am looking for is to arrange the visits field in descending order based on _id. And by cherry-picking I mean, selecting only certain items inside visits.

Answer

Hope you have resolved, for the reference this may help you, try lookup with pipeline,

• let to pass localfield, pipeline to put your conditions in $match stage and your filter conditions you don’t need to filter in $project stage. and put sort by _id in descending order

  { $match: { mobile_number: "1234567890", } },
  {
    $lookup: {
      from: "visitor_logs",
      let: { visitor_id: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$$visitor_id", "$visitor_id"] },
            in_time: {
              $gte: "1610609615",
              $lte: "1610615328"
            }
          }
        },
        { $sort: { _id: -1 } },
        {
          $project: {
            _id: 0,
            v_id: "$_id",
            in_time: 1,
            out_time: 1,
            checked_in_status: 1,
            visit_purpose: 1,
            person_to_meet: 1
          }  
        }
      ],
      as: "visits"
    }
  },
  {
    $project: {
      _id: 0,
      visitor_id: "$_id",
      first_name: 1,
      last_name: 1,
      mobile_number: 1,
      visits: 1
    }
  }