Retrieving sorted values in an array not equal to an ObjectID

September 4, 2023

This is my database schema for films:

I have this code which retrieves the title and reviews array of objects sorted in descending order of creation date (working):

db.films.findOne({ _id: ObjectId('64d69473da87c0c91ae7c596') }, 
     {title:1, reviews:{$sortArray:{input:"$reviews", sortBy:{creationdate:-1}}} } )

I’m trying to extend this to find all the reviews with the element is not equal to a specific userid:

db.films.findOne(
      { _id: ObjectId("64d69473da87c0c91ae7c596") },
      {
        title: 1,
        updatedreviews: {
          $sortArray: {
            input: [
              {
                $cond: [
                  {
                    $not: {
                      $in: [
                        ObjectId("64d95f9b4ce0a13e8fc37567"),
                        "$reviews.userid",
                      ],
                    },
                  },
                  "$reviews",
                  null,
                ],
              },
            ],
            sortBy: { "$reviews.creationdate": -1 },
          },
        },
      }
    );

It isn’t working: It’s retrieving null. I tried the opposite which is equal to $in the specific userid but it’s returning all the reviews:

db.films.findOne(
      { _id: ObjectId("64d69473da87c0c91ae7c596") },
      {
        title: 1,
        updatedreviews: {
          $sortArray: {
            input: [
              {
                $cond: [
                  {
                      $in: [
                        ObjectId("64d95f9b4ce0a13e8fc37567"),
                        "$reviews.userid",
                      ],
                  },
                  "$reviews",
                  null,
                ],
              },
            ],
            sortBy: { "$reviews.creationdate": -1 },
          },
        },
      }
);

Any idea on how to fix this problem and retrieve all the reviews not made by that particular user’s ObjectId?

>Solution :

You should use the $filter operator to filter the matching element(s) in the reviews array.

db.films.findOne({
  _id: ObjectId("64d69473da87c0c91ae7c596")
},
{
  title: 1,
  updatedreviews: {
    $sortArray: {
      input: {
        $filter: {
          input: "$reviews",
          cond: {
            $ne: [
              ObjectId("64d95f9b4ce0a13e8fc37567"),
              "$$this.userid"
            ]
          }
        }
      },
      sortBy: {
        "creationdate": -1
      }
    }
  }
})

Demo @ Mongo Playground