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?

The type field in the cars collection was named as `type: ", is it a typo? — Yong Shun
– Yong Shun, Commented Jul 30, 2022 at 7:48
@YongShun no it isnt a typo. its an attribute, but just an example value — StormTrooper
– StormTrooper, Commented Jul 30, 2022 at 7:51

Yong Shun · Accepted Answer · 2022-07-30 10:22:03Z

1

You should look for an aggregation query.

$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).
$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)

edited Jul 30, 2022 at 10:22

answered Jul 30, 2022 at 7:55

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

StormTrooper Over a year ago

Isn't there any shorter or optimized way to query this? for me it seems like we could shorten the check for specifications to something like specifications.length > 0

Yong Shun Over a year ago

No, MongoDB doesn't support such specifications.length > 0, this is JS syntax. Or probably work with $size to get array length and use $gt operator. Demo

StormTrooper Over a year ago

I am getting this error when I run your example according to my schema: MongoError: $lookup with 'pipeline' may not specify 'localField' or 'foreignField'

Yong Shun Over a year ago

Which MongoDB version are you used? I think you are not able to use 'localField' or 'foreignField' with pipeline if your MongoDB version is lower than v5.

StormTrooper Over a year ago

I am using MongoDB server version: 4.2.5

Collectives™ on Stack Overflow

mongodb fetch records if array exists

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related