Mongoose find by field in inner object (ObjectID)-CodePudding

For example I have two models

const User = new Schema({
  name: { type: String },
});

const Message = new Schema({
  user: { type: ObjectId, ref: 'User' },
  message: { type: String },
});

How to find messages by the key "name" in the user?

It doesn't work

exports.get = async (req, res) => {
  return Message.find({ "user.name": req.query.name })
    .populate('user', 'name')
    .then((data) => res.json(data));
}

I understand why "user.name" doesn't work, but I don't know how to solve it

CodePudding user response：

There is no concept of a join in Mongodb. Hence I would recommend using straight schema definition like this:

const User = new Schema({
  name: { type: String },
});

const Message = new Schema({
  user: [User],
  message: { type: String },
});

then you wouldn't need to use populate anymore. Instead, you would have something like this:

Message.find({ "user.name": req.query.name })

However, If you still prefer to use the current approach, You can try this:

Message.find()
  .populate({
    path: "user",
    match: {
      name: req.query.name
    }
  })
...

CodePudding user response：

You would want to use $lookup:

exports.get = async (req, res) => {
  return Message.aggregate([
    {
      $lookup: { // Aggregate operation
        from: 'users',
        pipeline: [
          {
            $match: {
              name: req.query.name, // If not match, it still returns null, that's why we need the skip operation below
            }
          }
        ],
        as: 'user'
      }
    }, {
      $match: { // Skip operation: 
        "user": { $ne: [] }
      }
    }
  ])
  .then((data) => res.json(data));
}

CodePudding user response：

Basically you need the correct ordering of table User must be your primary table and Messages must be your secondary table from which you should lookup. The query is as following;

exports.get = async (req, res) => {
    return User.aggregate([
        {
            $match: {
                name: req.query.name
            },
            $lookup: {
                from: 'Messages',
                localField: "_id",
                foreignField: "user",
                as: "Messages"
            }
        }
    ])
        .then((data) => res.json(data));
}

Or if you want a clean or nicer result then you can do the following:

exports.get = async (req, res) => {
    return User.aggregate([
        {
            $match: {
                name: req.query.name
            },
            $lookup: {
                let: { id: _id },
                from: 'Messages',
                pipeline: [
                    {
                        $match: {
                            $expr: { $eq: ["$user", "$$id"] }
                        }
                    },
                    {
                        $project: {
                            user: 0
                        }
                    }
                ],
                as: "Messages"
            }
        }
    ])
        .then((data) => res.json(data));
}

This will return all the messages as an array with field name messages of "name" person e.g;

{
   _id: 123,
   name: Ali,
   Messages: [{_id: 1234, message: "Hello"},{_id: 12345, message: "Hi"} ]
}