Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-30812

When using an array element as the local field for $lookup, $project doesn't work

    • Query Optimization
    • Fully Compatible
    • ALL
    • Hide
      db.venues.aggregate(
      
      	// Pipeline
      	[
      		// Stage 1
      		{
      			$lookup: {
      			    "from" : "chains",
      			    "localField" : "chainIds.0",
      			    "foreignField" : "_id",
      			    "as" : "venueChain"
      			}
      		},
      
      		// Stage 2
      		{
      			$addFields: {
      			  "chainName": "$venueChain.name"
      			}
      		},
      
      		// Stage 3
      		{
      			$project: {
      			    n: 1,
      			    chainName: 1
      			}
      		},
      	],
      
      	// Options
      	{
      		cursor: {
      			batchSize: 50
      		}
      	}
      
      );
      
      Show
      db.venues.aggregate( // Pipeline [ // Stage 1 { $lookup: { "from" : "chains", "localField" : "chainIds.0", "foreignField" : "_id", "as" : "venueChain" } }, // Stage 2 { $addFields: { "chainName": "$venueChain.name" } }, // Stage 3 { $project: { n: 1, chainName: 1 } }, ], // Options { cursor: { batchSize: 50 } } );
    • QO 2023-08-21, QO 2023-09-04, QO 2023-09-18

      If an array element is used as the localField for the $lookup aggregation pipe, a $project anywhere else in the pipeline will fail to return results and also take a while to run. There is a workaround by adding a field containing the local element and using that field as the localField:

      db.venues.aggregate(
      
      	// Pipeline
      	[
      		// Stage 1
      		{
      			$addFields: {
      			  "firstChainId": {
      			    "$arrayElemAt": [ "$chainIds", 0 ]
      			  }
      			}
      		},
      
      		// Stage 2
      		{
      			$lookup: {
      			    "from" : "chains",
      			    "localField" : "firstChainId",
      			    "foreignField" : "_id",
      			    "as" : "venueChain"
      			}
      		},
      
      		// Stage 3
      		{
      			$addFields: {
      			  "chainName": "$venueChain.name"
      			}
      		},
      
      		// Stage 4
      		{
      			$project: {
      			  n: 1,
      			  chainName: 1
      			}
      		},
      	],
      
      	// Options
      	{
      		cursor: {
      			batchSize: 50
      		}
      	}
      );
      

            Assignee:
            alexander.ignatyev@mongodb.com Alexander Ignatyev
            Reporter:
            damian@liveapp.com Damian Mastylo
            Votes:
            2 Vote for this issue
            Watchers:
            14 Start watching this issue

              Created:
              Updated:
              Resolved: