Question 116620 in Mongodb Question posted in
The official documentation can be found here.

mongodb $lookup – suppress non-matching documents

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    try this query

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

    Playground.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search