CODEWITHSUNDEEP
mongodbnosqlaggregate
Raz Buchnik
Raz Buchnik

Reputation: 8411

MongoDB how to find documents with undefined $lookups (aggregation)

I have a document in an origin collection that holds OR not holds a reference to a foreign collection document - The key is not mandatory, so sometimes it's missing.

In such a situation the $lookup is "failed" and the desired document is not getting fetched from the DB.

This is the pipeline:

{
    $lookup: {
      from: "tables",
      let: { "enginefuel_type": "$engine.fuel_type" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$enginefuel_type"] }}},
        { $project: { title: 1 }}
      ],
      as: "engine.fuel_type"
    }
  },
  {
    $unwind: "$engine.fuel_type"
  },

  {
    $lookup: {
      from: "tables",
      let: { "enginegear": "$engine.gear" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$enginegear"] }}},
        { $project: { title: 1 }}
      ],
      as: "engine.gear"
    }
  },
  {
    $unwind: "$engine.gear"
  }

I need the document to be found anyway - whether it has the engine.fuel_type and/or engine.gear fields or not. If there is, so it should take the document from the foreign, otherwise just remain empty but not ignore the whole document.

I thought about making some pre-if statement checking if the field exists before making the aggregate query (could also be more efficient, reducing requests to the DB).

Is there any good way to do that?

Upvotes: 0

Views: 925

Answers (1)

Shubham saini
Shubham saini

Reputation: 96

Lookup stage works as you need it to, even though the field is not present in the origin collection, the document will not be ignored and will be a part of the result with "engine.fuel_type" array containing 0 elements.

It's the unwind stage that removes the documents which have 0 array elements. Fortunately, $unwind stage provides preserveNullAndEmptyArrays: option, that includes all the result. So you could try to do something like this:-

$lookup: {
      from: "tables",
      let: { "enginefuel_type": "$engine.fuel_type" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$enginefuel_type"] }}},
        { $project: { title: 1 }}
      ],
      as: "engine.fuel_type"
    }
  },
  {
    $unwind: {
      path: "engine.fuel_type",
      preserveNullAndEmptyArrays: true
    }
  }

Upvotes: 3

Related Questions