Filter on collection and merge results in another collection in MongoDB-CodePudding

I am using MongoDB 4.2.9 and have the following requirements:

Collection 'A' has multiple documents with a string field 'status' that I need to filter on
Collection 'B' has multiple documents

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'

CodePudding user response：

You can use a $merge stage to add documents to the B collection:

db.A.aggregate([
  {$match: { status: "myValue"}},
  {$merge: { into: "B"}}
])

CodePudding user response：

From my understandings, your "merge" behaviour is actually a union view of filtered view of collection A and collection B. In this sense, 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.