How to select data with $lookup and filter from related collection (like field)

IATN · 2024-06-21T10:10:00.577Z

Hi all,

i have 2 collections :

users
{ _id: 1, username: ‘mark’ },
{ _id: 2, username: ‘leon’ },
{ _id: 3, username: ‘leontin’ },
data
{
_id: ObjectId(‘6675309f82bdfbe3b00febfa’),
user: 1,
message: ‘94451 Laos Sweden Iran’,
}
{
_id: ObjectId(‘6675309f82bdfbe3b00febfa’),
user: 2,
message: ‘message 22222’,
},
{
_id: ObjectId(‘6675309f82bdfbe3b00febfa’),
user: 3,
message: ‘message 3333’,
}

if i search :

$results = $collectiondata->aggregate([[‘$lookup’ => [“from” =>“users”, “localField” => “user”, “foreignField” => “_id”, “as” => “user_info”]], [‘$match’ => [‘user_info.username’ => ‘leontin’]]])->toArray();

is working good.

But i need to get all result where username like ‘^leo’ .

Any help, thx in advance.

Regards I.

IATN · 2024-06-21T11:03:32.485Z

users

db.users.insert({ _id: 1, username: 'mark' });
db.users.insert({ _id: 2, username: 'leon' });
db.users.insert({ _id: 3, username: 'leontin'});

data

db.data.insert(
{
  _id: ObjectId('6675309f82bdfbe3b00febfa'),
  user: 1,
  message: '94451 Laos Sweden Iran',
});
db.data.insert(
{
  _id: ObjectId('6675309f82bdfbe3b00febfb'),
  user: 2,
  message: 'message  22222',
});
db.data.insert( 
{
  _id: ObjectId('6675309f82bdfbe3b00febfc'),
  user: 3,
  message: 'message  3333',
});

if i search :

db.data.aggregate([{‘$lookup’ : {“from” :“users”, “localField” : “user”, “foreignField” : “_id”, “as” : “user_info”}}, {‘$match’ : {‘user_info.username’ : ‘leon’}}]);

is working good.

But i need to get all result where username like ‘^leo’

Any help, thanks in advance.

Regards I.

turivishal · 2024-06-21T11:35:26.514Z

Hello @IATN,

You can use $regex operator with same input “^leo”.

db.data.aggregate([
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user_info",
      "pipeline": [
        {
          "$match": {
            "username": {
              "$regex": "^leo",
              "$options": "i"
            }
          }
        }
      ]
    }
  }
])

Playground