Combining data from 2 mongoDB collections into 1 document

user_16734869 0
Viewed 59

I want to filter 2 collections and return one document.

I have 2 MongoDB collections modelled as such

Analytics_Region

    _id:5ecf3445365eca3e58ff57c0,
    type:"city"
    name:"Toronto"
    CSD:"3520005"
    CSDTYPE:"C"
    PR:"35"
    PRNAME:"Ontario"
    geometry:Object
    country:"CAN"
    updatedAt:2021-04-23T18:25:50.774+00:00
    province:"ON"

Analytics_Region_Custom

    _id:5ecbe871d8ab4ab6845c5142
    geometry:Object
    name:"henry12"
    user:5cbdd019b9d9170007d15990
    __v:0

I want to output a single collection in alphabetical order by name,

    {
       _id: 5ecbe871d8ab4ab6845c5142,
       name: "henry12",
       type: "custom",
       province: null
    
    },
    {
        _id:5ecf3445365eca3e58ff57c0,
        name:"Toronto"
        type:"city"
        province:"ON",
    }

Things to note: In the output, we have added a type of "custom" for every document in Analytics_Region_custom. We also add a province of "null" for every document.

So far I looked into $lookup (to fetch results from another collection) but it does not seem to work for my needs since it adds an array onto every document

mongoose aggregation-framework pymongo mongodb-query mongodb
2 Answers

You can use $unionWith
Documents will be added to the pipeline(no check for duplicates), and from those documents we will project the fields

  • if type is missing => custom
  • if province missing => null

*if those 2 have any false value, like false/0/null the old value is kept (new value only if field is missing)

Test code here

db.coll1.aggregate([
  {
    "$unionWith": {
      "coll": "coll2"
    }
  },
  {
    "$project": {
      "_id": "$_id",
      "name": "$name",
      "type": {
        "$cond": [
          {
            "$ne": [
              {
                "$type": "$type"
              },
              "missing"
            ]
          },
          "$type",
          "custom"
        ]
      },
      "province": {
        "$cond": [
          {
            "$ne": [
              {
                "$type": "$province"
              },
              "missing"
            ]
          },
          "$province",
          null
        ]
      }
    }
  },
  {
    "$sort": {
      "name": 1      
    }
  }
])
user_48826920
  • $unionWith to perform union of both collections
  • $project to project only fields that you want
  • sort to sort by name field
db.orders.aggregate([
  {
    $unionWith: "inventory"
  },
  {
    $project: {
      _id: 1,
      name: 1,
      province: { $cond: { if: "$province",  then: "$province", else: null } },
      type: { $cond: { if: "$type", then: "$type", else: "custom" } }
    }
  },
  { 
    $sort: { name: 1 }
  }
])

Working example

user_143898300
Related