Aggregate/lookup/pipeline/match any value of array '$in' an array


Let’s say I have this collection

// collection name: Category
{_id: 1, parents: [2]}
{_id: 2, parents: [1,3]}
{_id: 3, parents: [1]}

I want to output all categories where any id in parents (array) is in ids: [1,3] as example below using $lookup aggregation.

The $lookup aggregation below works fine as $_id is not an array.

$lookup: {
  from: 'Category',
  let: {ids: [1,3]},
  pipeline: [
    {$match: {$expr: {$in: ['$_id', '$$ids']}}},
  as: 'categories'

But how do you match any value of $parents $in $$ids?

Expected output where parents contain either 1 or 3 as ids: [1,3].

{_id: 2, parents: [1,3]}
{_id: 3, parents: [1]}

Thank you,

Hello : )

If i understant what you want right,you dont need lookup, you just need to filter the document,
using that ids array as creteria.

This means that the intersection parents with ids should not be empty (they must have at least 1 common member)

For intersection you can use $setIntersection and then you need the result to have $size>0.

In case you really need $lookup,for example ids come from another pipelike before $lookup,
you can do the same inside the $lookup in the $match.

Hello @Takis,

Yes, the $lookup is part of a pipeline, I just make things easier so I could be able to play in Compass to see if anything’s wrong.

I thought there is an easier way to do it but I understand what you meant.

The solution became more complex as you need to check $size is an array before using it then use $gt in the match.

Thank you,

I looked again at your query and i saw that you have $expr but you can avoid it.
Meaning that ids are not refering to the fields comming from the pipeline.
You dont need it to be a variable.

You can use $in the query operator.The aggregate $in operator doesnt work when both arrays thats why you couldnt
use it.

If there is no need to make ids a variable,you can do it like bellow also,with the in query operator.

{"$match" : { parents: { $in: [1,3]} }}