$lookup when foreignField is array

Question

I have 2 collections, the first storing the animes watched by each user, their status etc:

const listSchema = mongoose.Schema({
  user: {
    type: Schema.Types.ObjectId,
    ref: 'user'
  },
  animes: [{
    _id: false,
    anime: {
      type: Schema.Types.ObjectId,
      ref: 'anime',
      unique: true
    },
    status: String,
    episodes: Number,
    rating: Number
  }]
});

and the second storing all animes and the relevant information. I want to show that second collection but adding status and episodes fields that are filled from the list of the logged in user.

I tried the following:

Anime.aggregate([
    {
      $lookup: {
        'from': 'lists',
        localField: '_id',
        foreignField: 'animes.anime',
        'as': 'name'
      },
    },
    {
      $unwind: '$animes'
    },
    {
      $project:{
        status:'$lists.animes.status'
      }
    }
  ]
)

but it returns an empty array. I also don't know how to filter by userId seeing how the _id is in the foreign collection.

Because your `foreignField` is an array or objects you cannot get the matching documents — Ashh, Feb 05 '19 at 17:10
I thought `$unwind` would solve that. Any workaround please? — Anass Elidrissi, Feb 05 '19 at 17:18
You can follow this https://stackoverflow.com/questions/53166680/lookup-when-foreign-field-is-an-array — Ashh, Feb 05 '19 at 17:18

Ashh · Accepted Answer · 2019-02-05T18:04:00.703

2

You can use below aggregation

{ "$lookup": {
  "from": "lists",
  "let": { "id": "$_id" },
  "pipeline": [
    { "$match": { "$expr": { "$in": ["$$id", "$animes.anime"] }}},
    { "$unwind": "$animes" },
    { "$match": { "$expr": { "$eq": ["$animes.anime", "$$id"] }}}
  ],
  "as": "name"
}}

edited Feb 05 '19 at 18:04

answered Feb 05 '19 at 17:59

Ashh

44,693
14
105
132

thanks! couldn't figure out what to use for `let` at first. It works, I just replaced the 2nd `$match` with `"$match": {"$expr": {"$eq": ["$user", mongoose.Types.ObjectId(req.userId)]}}` to select the user, nd now it works for all users. Thank you – Anass Elidrissi Feb 05 '19 at 19:21
1

variables taken in the `let` variable can be accessed inside the `$lookup` other wise you cannot. Just like in the above query we can access the `_id` inside the pipeline using `$$`. Have a look – Ashh Feb 06 '19 at 06:28
so `let` lets you define foreign fields and then access them using `$` and `$$` for local fields? I think I understand now, thank you. – Anass Elidrissi Feb 06 '19 at 09:56

$lookup when foreignField is array

1 Answers1

Linked