CODEWITHSUNDEEP
mongodbexpressmongoose
user6680
user6680

Reputation: 139

Returning unique mongodb documents using distinct not working

Each message document has a messageTrackingId. I want to return all the messages, but exclude documents that have the same messageTrackingId. So for example, if I had 4 documents in my table and 3 of them have the same messageTrackingId value, then the Messages.find() should only return 2 documents.

I'm trying to use distinct to only return the unique documents so I don't get duplicates with the same messageTrackingId. Currently postman is returning no documents.

if I changed Messages.find({query}).distinct('messageTrackingId') to Messages.find(query) then it returns all the recipientId's documents. but when I add distinct, I get no results.

app.get('/api/messages',(req, res, next)=>{
  query = {};
  inbox = false;
  messageId = false;
  if(req.query.recipientId){
    query = { recipientId: req.query.recipientId }
    inbox = true;

Messages.aggregate(// Pipeline
  [
      // Stage 1
      {
          $group: {
              _id: "$messageTrackingId",
              message : { $addToSet: '$message' },
              recipientId : { $addToSet: '$recipientId' },
              creator : { $addToSet: '$creator' },
              messageTrackingId : { $addToSet: '$messageTrackingId' },

          }
      },

      // Stage 2
      {
          $project: {
              _id: 1,
              message: { $arrayElemAt: ["$message", 0 ] },
              recipientId: { $arrayElemAt: ["$recipientId", 0 ] },
              creator: { $arrayElemAt: ["$creator", 0 ] },
              messageTrackingId: { $arrayElemAt: ["$messageTrackingId", 0 ] }
          }
      }

  ])

messages model

const mongoose = require("mongoose");
const uniqueValidator = require("mongoose-unique-validator");

const messagingSchema = mongoose.Schema({
  creator: { type: mongoose.Schema.Types.ObjectId, ref: "User" },
  recipient: { type: String, required: true },
  recipientId: { type: String, required: true },
  message: { type: String, required: true },
  subject: { type: String, required: true },
  creationDate: { type: Date, required: true },
  messageTrackingId: { type: String }
  // readDate: { type: Date, required: true }
});


module.exports = mongoose.model("Messages", messagingSchema);

Upvotes: 0

Views: 1345

Answers (1)

Samuel Goldenbaum
Samuel Goldenbaum

Reputation: 18909

distinct will return distinct fields which is not what you want.

You will need to use aggregation and group by the messageTrackingId, then project grabbing the first message content etc you want:

Given sample data like:

{ "messageTrackingId" : 1, "message" : "hello" }
{ "messageTrackingId" : 1, "message" : "hello" }
{ "messageTrackingId" : 1, "message" : "bye" }
{ "messageTrackingId" : 2, "message" : "bye" }
{ "messageTrackingId" : 2, "message" : "bye" }
{ "messageTrackingId" : 1, "message" : "hello" }

In MongoDB:

db.getCollection("messages").aggregate(

// Pipeline
[
    // Stage 1
    {
        $group: {
            _id: "$messageTrackingId",
            message : { $addToSet: '$message' }
        }
    },

    // Stage 2
    {
        $project: {
            _id: 1,
            message: { $arrayElemAt: ["$message", 0 ] }
        }
    },

]);

To use in mongoose, simply using the aggregate function on your model:

Using Mongoose

const result = await Message.aggregate(// Pipeline
        [
            // Stage 1
            {
                $group: {
                    _id: "$messageTrackingId",
                    message : { $addToSet: '$message' }
                }
            },

            // Stage 2
            {
                $project: {
                    _id: 1,
                    message: { $arrayElemAt: ["$message", 0 ] }
                }
            },

        ]);

UPDATE AFTER COMMENTS

If you need to query for a given messageTrackingId, then add $match as the first stage of the pipeline:

const result = await Message.aggregate(
        [
            {
                $match: {
                    messageTrackingId: {$eq: 2}
                }
            },
            {
                $group: {
                    _id: "$messageTrackingId",
                    message : { $addToSet: '$message' }
                }
            },
            {
                $project: {
                    _id: 1,
                    message: { $arrayElemAt: ["$message", 0 ] }
                }
            },

        ]);

Upvotes: 1

Related Questions