MongoDB Aggregate: เปรียบเทียบฟิลด์ใน $filter ระหว่าง Name1/Name2 กับ Detail1/Detail2

สร้างคอลเลกชันตัวอย่าง

> db.demo137.insertOne(
  {
    Name1: "Chris",
    Name2: "David",
    Detail1: [
      {_id: "John", Name3: "Chris"},
      {_id: "Chris", Name3: "Chris"}
    ],
    Detail2: [
      {_id: "David", Name3: "Chris"},
      {_id: "Carol", Name3: "Chris"},
      {_id: "John", Name3: "Chris"}
    ]
  }
);
{ "acknowledged": true, "insertedId": ObjectId("5e31b4cefdf09dd6d08539a0") }

แสดงข้อมูล

> db.demo137.find();

{ "_id": ObjectId("5e31b4cefdf09dd6d08539a0"), "Name1": "Chris", "Name2": "David", "Detail1": [ { "_id": "John", "Name3": "Chris" }, { "_id": "Chris", "Name3": "Chris" } ], "Detail2": [ { "_id": "David", "Name3": "Chris" }, { "_id": "Carol", "Name3": "Chris" }, { "_id": "John", "Name3": "Chris" } ] }

คิวรี Aggregate: $project + $filter เปรียบเทียบ $$out._id กับ $Name1/$Name2

> db.demo137.aggregate([
  {
    $project: {
      _id: 0,
      Name1: 1,
      Name2: 1,
      Detail1: {
        $filter: {
          input: "$Detail1",
          as: "out",
          cond: { $eq: ["$$out._id", "$Name1"] }
        }
      },
      Detail2: {
        $filter: {
          input: "$Detail2",
          as: "out",
          cond: { $eq: ["$$out._id", "$Name2"] }
        }
      }
    }
  }
])

ผลลัพธ์: Detail1 เหลือ _id=Chris, Detail2 เหลือ _id=David

{ "Name1": "Chris", "Name2": "David", "Detail1": [ { "_id": "Chris", "Name3": "Chris" } ], "Detail2": [ { "_id": "David", "Name3": "Chris" } ] }