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

Working with Data
1

Hi all,

i have 2 collections :

  1. users
    { _id: 1, username: ‘mark’ },
    { _id: 2, username: ‘leon’ },
    { _id: 3, username: ‘leontin’ },

  2. 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.

2 
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.

3

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

1 Like