CODEWITHSUNDEEP
mongodbmatchlookup
Vanderci Curvelo Junior
Vanderci Curvelo Junior

Reputation: 172

How filter using part of a field in a lookup collection

I've doing a "join" between two mongodb collections and want to filter a lookup collection (before join) using part of a field as a criteria. My first option would be using regex, but I didn't find how to do it in mongodb doc. I found 3 different ways to use regex $regex, $regexMatch e $regexFind. No one worked or I dont know how to manage with it. Any idea ? I tried to use some of these 3 regex in this part of example, without success

 $and: [{ $eq: ['$id', '$$key'] }, { $eq: ['$x', 0], [here regex maybe or something] }]

I want something like in SQL "WHERE substr(field,3,1) = 'A'" for example

db.collection('collectionA').aggregate([
{
    $lookup: {
      from: 'collectionB',
      let: { key: '$key },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [{ $eq: ['$id', '$$key'] }, { $eq: ['$x', 0] }]
            }
          }
        }
      ],
      as: 'i'
    }
  }
])

Upvotes: 0

Views: 29

Answers (1)

SebastianK
SebastianK

Reputation: 782

Why not using the substring stage for this?

db.collection('collectionA').aggregate([
{
    $lookup: {
      from: 'collectionB',
      let: { key: '$key },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [{ $eq: ['$id', '$$key'] }, {$eq: { $substr: {'$x', 3, 1}, 'A'} }]
            }
          }
        }
      ],
      as: 'i'
    }
  }
])

This should be the equivalent for the SQL statement you mentioned above.

Upvotes: 1

Related Questions