Mongodb regexMatch within Lookup

user_9012921 0
Viewed 23

I have a patients collections that looks like this

[
  {
   _id: ObjectId(60f5a851e8b5d11111111),
   firstName:"test_1",
   lastName: "test_last_1"
  },
  {
   _id: ObjectId(60f5a851e8b5d11111112),
   firstName:"test_2",
   lastName: "test_last_2"
  },
]

and a messages collections that looks like this:

[
  {
   _id: ObjectId(60f5a851e8b5d22222222),
   body: "Lorem ipsum dolor sit amet, consectetur adipiscing elit.",
   createdByPatient: null,
   createdByUser: ObjectId(60e888ac01198634444444)
  },
  {
   _id: ObjectId(60f5a851e8b5d22222223),
   body: "It's a great day",
   createdByPatient: ObjectId(60f5a851e8b5d11111111)
   createdByUser: null
  },
  {
   _id: ObjectId(60f5a851e8b5d22222224),
   body: "I'm a message",
   createdByPatient: ObjectId(60f5a851e8b5d11111111)"
   createdByUser: null
  },
]

I'd like to pull the patients that have messages which match a substring. For example, using the dummy data above and given the substring "day", the desired result should look like this

Results:

[
  {
   _id: ObjectId(60f5a851e8b5d11111111),
   firstName:"test_1",
   lastName: "test_last_1",
   lastMessage: {
      _id: ObjectId(60f5a851e8b5d22222223),
       body: "It's a great day",
       createdByPatient: ObjectId(60f5a851e8b5d11111111)"
       createdByUser: null
    }
  },
]

This will likely be solved using an aggregation.

node.js mongodb
1 Answers

Based on your expected result:

  1. $lookup - patients collection (key: _id) join messages collection (key: createdByPatient) and match the regex with $regexMatch operator. And it outcomes the messages field with an array value.

Note:

1.1. If the _id in patients collection is ObjectId type, you need to cast the createdByPatient in messages collection as ObjectId via $toObjectId. (Since you didn't provide the correct ObjectId, I assume that the _id in patients collection is string type.

1.2. option: i in $regexMatch means searching the string with ignoring case-sensitive.

  1. $set - Set lastMessage field with get the last element of messages array via $arrayElemAt.

  2. $unset - Remove messages field from the documents.

db.patients.aggregate([
  {
    $lookup: {
      from: "messages",
      let: {
        patientId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$createdByPatient",
                    "$$patientId"
                  ]
                },
                {
                  $regexMatch: {
                    input: "$body",
                    regex: "day",
                    options: "i"
                  }
                }
              ]
            }
          }
        }
      ],
      as: "messages"
    }
  },
  {
    $set: {
      lastMessage: {
        $arrayElemAt: [
          "$messages",
          -1
        ]
      }
    }
  },
  {
    $unset: "messages"
  }
])

Sample Mongo Playground

user_80176900
Related