I only want to see matching documents, i.e. only T3 in the example below. I can find the matching documents between lotterytickets (many documents) and lotterydrawing (only a few documents).

How can I filter out the non-matching documents? Basically, I'd not like to see documents with the condition drawnticket==[], but I haven't found the conditional code to apply.

Any help would be appreciated. Thank you in advance

Configuration:

db={
  "lotteryticket": [
    {
      "_id": ObjectId("6021ce0cb4d2c2b4f24c3a2e"),
      "ticket": "T1",
      "player": "Alice"
    },
    {
      "_id": ObjectId("6021ce0cb4d2c2b4f24c3a2f"),
      "ticket": "T2",
      "player": "Bob"
    },
    {
      "_id": ObjectId("6021ce0cb4d2c2b4f24c3a33"),
      "ticket": "T3",
      "player": "Charles"
    }
  ],
  "lotterydrawing": [
    {
      "_id": ObjectId("63309480b749b733c087b758"),
      "ticket": "T3"
    },
    {
      "_id": ObjectId("63309480b749b733c087b759"),
      "ticket": "T9"
    },
    {
      "_id": ObjectId("63309480b749b733c087b75a"),
      "ticket": "T77"
    }
  ]
}

Query:

db.lotteryticket.aggregate([
  {
    $lookup: {
      from: "lotterydrawing",
      localField: "ticket",
      foreignField: "ticket",
      as: "drawnticket",
      
    }
  }
])

Result:

[
  {
    "_id": ObjectId("6021ce0cb4d2c2b4f24c3a2e"),
    "drawnticket": [],
    "player": "Alice",
    "ticket": "T1"
  },
  {
    "_id": ObjectId("6021ce0cb4d2c2b4f24c3a2f"),
    "drawnticket": [],
    "player": "Bob",
    "ticket": "T2"
  },
  {
    "_id": ObjectId("6021ce0cb4d2c2b4f24c3a33"),
    "drawnticket": [
      {
        "_id": ObjectId("63309480b749b733c087b758"),
        "ticket": "T3"
      }
    ],
    "player": "Charles",
    "ticket": "T3"
  }
]

https://mongoplayground.net/p/bYcLEzrF5QT

CodePudding user response：

Add a match stage, to filter stages with the empty drawn tickets. Like this:

db.lotteryticket.aggregate([
  {
    $lookup: {
      from: "lotterydrawing",
      localField: "ticket",
      foreignField: "ticket",
      as: "drawnticket",
      
    }
  },
  {
    "$match": {
      $expr: {
        "$gt": [
          {
            $size: "$drawnticket"
          },
          0
        ]
      }
    }
  }
])

Playground.

CodePudding user response：

try this query

db.lotteryticket.aggregate([
  {
    $lookup: {
      from: "lotterydrawing",
      localField: "ticket",
      foreignField: "ticket",
      as: "drawnticket"
    }
  },
  {
    "$match": {
      drawnticket: {
        $exists: true,
        $ne: []
      }
    }
  }
])

Playground.