Mongodb Question posted in
The official documentation can be found here.

MongoDB find data between multiple related collections

I have 3 collections :

users

[
    { _id: 1, username: 'mark' },
    { _id: 2, username: 'leon' },
    { _id: 3, username: 'leontin'}
]

providers

[
    { _id: 1, name: 'prov1' },
    { _id: 2, name: 'prov2' },
    { _id: 3, name: 'prov3' },
    { _id: 39, name: 'prov39' }
]

data

[
    {
      _id: ObjectId('6675309f82bdfbe3b00febfa'),
      user: 1,
      provider: 1,
      message: '94451 Laos Sweden Iran',
    },
    {
      _id: ObjectId('6675309f82bdfbe3b00febfb'),
      user: 2,
      provider: 3,
      message: 'message  22222',
    },
    {
      _id: ObjectId('6675309f82bdfbe3b00febfc'),
      user: 3,
      provider: 39,
      message: 'message  3333',
    }
]

If i search like this, it’s working correctly:

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

But i need to add more to this query: where provider.name like '^prov3':

I have added the related collection but I can’t add the "like"

db.data.aggregate([
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user_info"
    }
  },
  {
    "$unwind": "$user_info"
  },
  {
    "$lookup": {
      "from": "providers",
      "localField": "provider",
      "foreignField": "_id",
      "as": "provider_info"
    }
  },
  {
    "$unwind": "$provider_info"
  },
  {
    $match: {
      $expr: {
        $regexMatch: {
          input: "$user_info.username",
          regex: "^leo",
          options: "i"
        }
      }
    }
  }
])
Tags:

2

Answers


  1. 0

    In your $match stage, you should have the $and operator to match the document based on the user_info.username and provider_info.name criteria.

    db.data.aggregate([
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user_info"
    }
  },
  {
    "$lookup": {
      "from": "providers",
      "localField": "provider",
      "foreignField": "_id",
      "as": "provider_info"
    }
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            $regexMatch: {
              input: {
                $first: "$user_info.username"
              },
              regex: "^leo",
              options: "i"
            }
          },
          {
            $regexMatch: {
              input: {
                $first: "$provider_info.name"
              },
              regex: "^prov3",
              options: "i"
            }
          }
        ]
      }
    }
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
  2. 0

    This could be done in a similar way to the answer to your previous question.

    Since you want both checks ^leo and ^prov3, do "lookup + unwind + match" for userInfo and then do a "lookup + unwind + match" for providers. This ensures that results match both criteria. Also, by doing the triple-stage together, there are fewer documents which need to go through the rest of the pipeline.

    And each sequence is highly optimised by MongoDB: $lookup, $unwind, and $match Coalescence.

    db.data.aggregate([
  // lookup + unwind + match `user_info`
  {
    $lookup: {
      from: "users",
      localField: "user",
      foreignField: "_id",
      as: "user_info"
    }
  },
  { $unwind: "$user_info" },
  {
    $match: {
      $expr: {
        $regexMatch: {
          input: "$user_info.username",
          regex: "^leo",
          options: "i"
        }
      }
    }
  },
  // lookup + unwind + match `provider_info`
  {
    $lookup: {
      from: "providers",
      localField: "provider",
      foreignField: "_id",
      as: "provider_info"
    }
  },
  { $unwind: "$provider_info" },
  {
    $match: {
      $expr: {
        $regexMatch: {
          input: "$provider_info.name",
          regex: "^prov3",
          options: "i"
        }
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search