Watch keynotes and sessions from MongoDB.live, our virtual developer conference.

Lookup vs two queries

I have two collections like
Business = {
_id : ObjectID,
name: string
}
and
Features ={
bid: ObjectID // it is _id of businesses
features: Array of string
}

And I need to build query that find all businesses with special feature.
First solution is to do 2 queries: first query is features.Find(features:“my_feature”) and second to get all businesses with _id from list, that I get on first step.
Second solution is to build single query with $lookup and $match.
But I don`t know what is best solution for such situation.

I came from RDMS, where such problem usually is solved by single query like
SELECT * FROM businesses INNER JOIN features ON businesses._id=features.bid
WHERE ‘my_feature’ IN features.features

Hello, @Roman_Buzuk! Welcome to the community!

I think in your case it would be better to merge those collections into 1, like this:

db.businesses.insertMany([
  {
    _id: 1,
    name: 'b1',
    features: ['f1', 'f2', 'f3'],
  },
  {
    _id: 2,
    name: 'b2',
    features: ['f2', 'f3', 'f10'],
  }
]);

Later, you can get your busineses objects like this:

db.businesses.find({ feature: 'f1'}, { features: false });

This is because denormalized data schema is preferred in MongoDB.

Learn more:

Thanks for your answer.
I understand, that such scheme can be better, but I can`t change scheme. So I should work with 2 collections

I would go with aggregation solution (see example below).
It may require a bit more RAM for $unwind and $replaceRoot stages, but the result can be achieved faster using only 1 request to the database.

db.features.aggregate([
  {
    $match: {
      features: 'f3',
    }
  },
  {
    $group: {
      _id: null,
      businessesIds: {
        $addToSet: '$bid',
      }
    }
  },
  {
    $lookup: {
      from: 'businesses',
      localField: 'businessesIds',
      foreignField: '_id',
      as: 'businesses',
    }
  },
  {
    $unwind: '$businesses',
  },
  {
    $replaceRoot: {
      newRoot: '$businesses'
    },
  }
]);

Yes, I came with similar solution.
Thanks.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.