Select documents where lookup is empty

I have the database as shown below:

db={
  "item": [
    {
      "_id": "ABC",
      "name": "first item"
    },
    {
      "_id": "DEF",
      "name": "second item"
    },
    {
      "_id": "XYZ",
      "name": "third item"
    },
    {
      "_id": "EFG",
      "name": "fourth item"
    }
  ],
  "itemBox": [
    {
      "_id": "1",
      "items": [
        "ABC",
        "DEF"
      ]
    },
    {
      "_id": "2",
      "items": [
        "EFG"
      ]
    }
  ]
}

I want to select only items that do not have if its ID appear in the items list for any itemBox. In this example, I would expect the result to be

[
  {
    "_id": "XYZ",
    "name": "third item"
  }
]

this is currently the query I have to perform the lookup:

db.item.aggregate([
  {
    "$lookup": {
      "from": "itemBox",
      "localField": "_id",
      "foreignField": "items",
      "as": "itemBox_doc"
    }
  }
])

This results in the output:

[
  {
    "_id": "ABC",
    "itemBox_doc": [
      {
        "_id": "1",
        "items": [
          "ABC",
          "DEF"
        ]
      }
    ],
    "name": "first item"
  },
  {
    "_id": "DEF",
    "itemBox_doc": [
      {
        "_id": "1",
        "items": [
          "ABC",
          "DEF"
        ]
      }
    ],
    "name": "second item"
  },
  {
    "_id": "XYZ",
    "itemBox_doc": [],
    "name": "third item"
  },
  {
    "_id": "EFG",
    "itemBox_doc": [
      {
        "_id": "2",
        "items": [
          "EFG"
        ]
      }
    ],
    "name": "fourth item"
  }
]

How should I modify the query to provide the expected result?

Mongoplayground link: Mongo playground

I have managed to find a query that returns my expected result

db.item.aggregate([
  {
    "$lookup": {
      "from": "itemBox",
      "localField": "_id",
      "foreignField": "items",
      "as": "itemBox_doc"
    }
  },
  {
    $match: {
      "itemBox_doc": {
        $size: 0
      }
    }
  },
  {
    "$project": {
      "itemBox_doc": 0
    }
  }
])

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.