Max size of $lookup arrays?

Working with Data
1

Hello everyone,

I have 3 collections “Product”, “Shop” and “Stock”.

Here my steps :
1 - $match “Product” > 6 909 documents
2 - $lookup “Product” and “Shop” > 6 909 documents
3 - $unwind this first result > 443 163 documents
4 - $lookup this first result and Stock
5 - $unwind this second resultat > 4 253 137 documents

After this 5 steps, I tried to add a “$count” stage, but i got “exceeded time limit” !
I also tried to add “$group” to merge the documents and reduce the number of documents, buf failed again !

Do you have any ideas ?

Followed are the collections :

— Product = 6909 documents
{
‘productId’: ‘141013’,
‘language’: ‘fr’
},
{
productId: ‘141014’,
language: ‘fr’
}
…

— Shop —

{
shopId: ‘101’,
name: ‘Paris’,
languages: [‘fr’]
},
{
shopId: ‘102’,
name: ‘Marseille’,
languages: [‘fr’]
},
{
shopId: ‘103’,
name: ‘Bordeaux’,
languages: [‘fr’]

}
…

Lookup between Product and Shop give :

{
productId: ‘141013’,
language: ‘fr’,
lookupShop: [
{ shopId: ‘101’, name: ‘Paris’, languages: [ ‘fr’ ] },
{ shopId: ‘102’, name: ‘Marseille’, languages: [ ‘fr’ ] },
{ shopId: ‘103’, name: ‘Bordeaux’, languages: [ ‘fr’ ] }
…
]
},
{
productId: ‘141014’,
language: ‘fr’,
lookupShop: [
{ shopId: ‘101’, name: ‘Paris’, languages: [ ‘fr’ ] },
{ shopId: ‘102’, name: ‘Marseille’, languages: [ ‘fr’ ] },
{ shopId: ‘103’, name: ‘Bordeaux’, languages: [ ‘fr’ ] }
…
]
},
…

After $unwind = 443 163 documents

{
productId: ‘141013’,
language: ‘fr’,
lookupShop:
{
shopId:101,
name: ‘Paris’,
languages:[‘fr’]
}
},
{
productId: ‘141013’,
language: ‘fr’,
lookupShop:
{
shopId:102,
name: ‘Marseille’,
languages:[‘fr’]
}
},
{
productId: ‘141013’,
language: ‘fr’,
lookupShop:
{
shopId:103,
name: ‘Bordeaux’,
languages:[‘fr’]
}
},
{
productId: ‘141014’,
language: ‘fr’,
lookupShop:
{
shopId:101,
name: ‘Paris’,
languages:[‘fr’]
}
}
…

— Stock

{
productId: ‘141013’,
sizeId: ‘01’,
shop:
{
shopId: ‘101’,
name: ‘Paris’
}
},
{
productId: ‘141013’,
sizeId: ‘02’,
shop:
{
shopId: ‘101’,
name: ‘Paris’
}
},
{
productId: ‘141013’,
sizeId: ‘01’,
shop:
{
shopId: ‘102’,
name: ‘Marseille’
}
},
…

Lookup with Stock with keys productId and shopId.
After $unwind = 4 253 137 documents

2

Why are you unwinding after the first lookup? You can pass an array of keys as localField and it will “do the right thing”.

But this looks very much like a normalized schema would look - why do you need to so many joins? Can you explain your use case?

Asya
P.S. your documents would be easier to read if you format them as code.
P.P.S. Include the actual pipeline you’re running too…

1 Like
3

You are rightn no need to unwind after the first lookup (thanks).

I need to check the truthfulness of the documents.

I have formated my documents as code.

— Product (with $match and $project)

[
  { _id: 'fr_141013', language: 'fr', productId: '141013' },
  { _id: 'fr_141014', language: 'fr', productId: '141014' }
]

— adding $lookup Shop

[
  {
    _id: 'fr_141013',
    language: 'fr',
    productId: '141013',
    lookupShop: [ { shopId: '407' }, { shopId: '408' } ]
  },
  {
    _id: 'fr_141014',
    language: 'fr',
    productId: '141014',
    lookupShop: [ { shopId: '407' }, { shopId: '409' } ]
  }
]

– adding $lookup Stock

I need to reduce the lookupStock array and keep, for each size, only the values of warehouse.shopIds similar to those lookupShop array’s values.

[
  {
    _id: 'fr_141013',
    language: 'fr',
    productId: '141013',
    lookupShop: [ { shopId: '407' }, { shopId: '408' } ],
    lookupStock: [
      {
        sizeId: '05',
        warehouse: {
          shopIds: [
            '594', '562', '520',
            '510', '533', '511',
            '501', '534', '557',
            '503', '548', '504',
            '406', '407', '506',
            '616', '408', '409'
          ]
        }
      },
      {
        sizeId: '04',
        warehouse: {
          shopIds: [
            '594', '562', '520',
            '510', '533', '511',
            '501', '534', '557',
            '503', '548', '504',
            '406', '407', '506',
            '616', '408', '409'
          ]
        }
      },

Here is the actual pipeline

[{$match: {
 productId: {
  $in: [
   '100007',
   '100007'
  ]
 }
}}, {$project: {
 productId: 1,
 language: 1
}}, {$lookup: {
 from: 'Shop',
 localField: 'language',
 foreignField: 'localeIds',
 pipeline: [
  {
   $match: {
    shopId: {
     $in: [
      '407',
      '408'
     ]
    }
   }
  }
 ],
 as: 'lookupShop'
}}, {$project: {
 productId: 1,
 language: 1,
 'lookupShop.shopId': 1
}}, {$lookup: {
 from: 'Stock',
 localField: 'productId',
 foreignField: 'productId',
 pipeline: [
  {
   $limit: 10
  }
 ],
 as: 'lookupStock'
}}, {$project: {
 language: 1,
 productId: 1,
 lookupShop: 1,
 'lookupStock.sizeId': 1,
 'lookupStock.warehouse.shopIds': 1
}}]

Best regards

Emmanuel