CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
richnwilson
richnwilson

Reputation: 81

Filter on collection and merge results in another collection in MongoDB

I am using MongoDB 4.2.9 and have the following requirements:

Collection A

{ _id: "1",
  status: "Report",
  type: "Academy",
  rating: "Excellent",
  ReportNo: "A1"
},
{ _id: "2",
  status: "Open",
  type: "Academy",
  rating: "",
  ReportNo: ""
},
{ _id: "3",
  status: "Draft",
  type: "Academy",
  rating: "",
  ReportNo: ""
},
{ _id: "4",
  status: "Report",
  type: "Academy",
  rating: "Great",
  ReportNo: "A4"
}

Collection B

{ _id: "98",
  status: "Archived",
  type: "Academy",
  rating: "So So",
  ReportNo: "X2"
},
{ _id: "99",
  status: "Archived",
  type: "Academy",
  rating: "Great",
  ReportNo: "X1"
}

Resulting View

{ _id: "1",
  status: "Report",
  type: "Academy",
  rating: "Excellent",
  ReportNo: "A1"
},
{ _id: "4",
  status: "Report",
  type: "Academy",
  rating: "Great",
  ReportNo: "A4"
},
{ _id: "98",
  status: "Archived",
  type: "Academy",
  rating: "So So",
  ReportNo: "X2"
},
{ _id: "99",
  status: "Archived",
  type: "Academy",
  rating: "Great",
  ReportNo: "X1"
}

My goal is to create an aggregation view so that I can filter on a status value in Collection 'A' and then merge those results with Collection 'B' and show in the view ?

I can filter on Collection 'A' using the match call, just can't see how to merge resulting documents into Collection 'B'

Upvotes: 0

Views: 1083

Answers (1)

ray
ray

Reputation: 15286

From my understandings, your "merge" behaviour is actually a union view of filtered view of collection A and collection B.

With MongoDB v4.2, you can use $facet to handle collection A and collection B separately.

  • simply perform filtering on A
  • perform uncorrelated $lookup on B

wrangle the result and merge them together to get the union view that you are looking for.

db.createCollection(
  "unionView",
  {
    "viewOn" : "A",
    "pipeline" : [
  {
    "$facet": {
      "A": [
        {
          "$match": {
            status: "Report"
          }
        }
      ],
      "B": [
        {
          $limit: 1
        },
        {
          "$lookup": {
            "from": "B",
            "pipeline": [],
            "as": "B"
          }
        },
        {
          $unwind: "$B"
        },
        {
          "$replaceRoot": {
            "newRoot": "$B"
          }
        }
      ]
    }
  },
  {
    $project: {
      all: {
        "$setUnion": [
          "$A",
          "$B"
        ]
      }
    }
  },
  {
    $unwind: "$all"
  },
  {
    "$replaceRoot": {
      "newRoot": "$all"
    }
  }
]
  }
)

Here is the Mongo Playground for your reference.

With MongoDB v4.4+, you can create a view with $unionWith

db.createCollection(
  "unionView",
  {
    "viewOn" : "A",
    "pipeline" : [
  {
    "$match": {
      status: "Report"
    }
  },
  {
    "$unionWith": {
      "coll": "B"
    }
  }
]
  }
)

Here is the Mongo playground for your reference.

Upvotes: 1

Related Questions