Mongodb nested array lookup

Viewed 29

I have 2 collection: Company and Stores. I need to $lookup Store collection using the storeIds on Company collection. Then again i need to check if year (Company collection) exists in Statuses.year in Store collection. If exists, then retrieve all the fields from Company collection plus Statuses from Store. Can both of this be achieved using $lookup. Any help is much appreciated.

Company: [
{
  "_id": {
    "$oid": "1388445c0000000000000001"
  },
  "name": "Company A",
  "year": 2022,
  "storeIds": [
    {
      "$oid": "1388445c0000000000000011"
    },
    {
      "$oid": "1388445c0000000000000012"
    }
  ]
}
]

Store: [
{
  "_id": {
    "$oid": "1388445c0000000000000011"
  },
  "name": "Store A",
  "statuses": [
    {
      "year": 2021,
      "status": "pending"
    },
    {
      "year": 2022,
      "status": "review"
    }
  ]
}
]

Output: [
{
  "_id": {
    "$oid": "1388445c0000000000000001"
  },
  "name": "Company A",
  "year": 2022,
  "storeIds": [
    {
      "$oid": "1388445c0000000000000011"
    }
  ],
  "statuses": [
    {
      "year": 2022,
      "status": "review"
    }
  ]
}
]
0 Answers
Related