mongodb fetch records if array exists

Question

I have 2 collections:

Vehicles:

[
    {
        "_id": "a1",
        "type:": "car",
        "make": "Honda",
        "specifications": ["1", "2"]
    },
    {
        "_id": "a2",
        "type:": "car",
        "make": "Toyota",
        "specifications": ["3", "4"]
    },
    {
        "_id": "a3",
        "type:": "car",
        "make": "Honda",
        "specifications": []
    },
    {
        "_id": "a4",
        "type:": "car",
        "make": "Toyota"
    }
]

Specifications:

[
    {
        "_id": "1",
        "color": "Black"
    },
    {
        "_id": "2",
        "sunroof": "yes"
    },
    {
        "_id": "3",
        "engine": "1800 CC"
    },
    {
        "_id": "4",
        "bodyType": "Sedan"
    }
]

I want to fetch those records which has at least one specification. And also the details from specifications collections should appear in Vehicles collections somewhere.

Expected response:

[
    {
        "_id": "a1",
        "make": "Honda",
        "type:": "car",
        "carSpecifications": [
            {
                "color": "Black"
            },
            {
                "sunroof": "yes"
            }
        ],

    },
    {
        "_id": "a2",
        "make": "Toyota",
        "type:": "car",
        "specifications": [
            {
                "engine": "1800 CC"
            },
            {
                "bodyType": "Sedan"
            }
        ]
    }
]

Now what I tried so far is:

db.vehicles.find({type: "car", "specifications": {$exists: true}}, {fields: {"specifications.$": 1}}).fetch()

this query is returning all the records from Vehicles.

After getting all the records I put a loop on the records I get and check manually if specifications.length > 0 than I query from Specifications collection accordingly.

Can I achieve all this with a single query?

Answer 1

You should look for an aggregation query.

1. $match - Filter documents with "type:" "car" and specifications is not an empty array (with $ifNull, default as [] when specifications field is null or not existed).

2. $lookup - Vehicles collection join specifications collection (Refer to Use $lookup with an Array). Work with pipeline to return the array without the _id field (Refer to Correlated Subqueries Using Concise Syntax).

MongoDB v5 query

db.vehicles.aggregate({
  $match: {
    $and: [
      {
        "type:": "car"
      },
      {
        $expr: {
          $ne: [
            {
              $ifNull: [
                "$specifications",
                []
              ]
            },
            []
          ]
        }
      }
    ]
  }
},
{
  $lookup: {
    from: "specifications",
    localField: "specifications",
    foreignField: "_id",
    pipeline: [
      {
        $project: {
          _id: 0
        }
      }
    ],
    as: "specifications"
  }
})

Sample Mongo Playground (v5)

---

MongoDB v4 query

db.vehicles.aggregate({
  $match: {
    $and: [
      {
        "type:": "car"
      },
      {
        $expr: {
          $ne: [
            {
              $ifNull: [
                "$specifications",
                []
              ]
            },
            []
          ]
        }
      }
    ]
  }
},
{
  $lookup: {
    from: "specifications",
    let: {
      specifications: "$specifications"
    },
    pipeline: [
      {
        $match: {
          $expr: {
            $in: [
              "$_id",
              "$$specifications"
            ]
          }
        }
      },
      {
        $project: {
          _id: 0
        }
      }
    ],
    as: "specifications"
  }
})

Sample Mongo Playground (v4)