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

    XMLWordPrintable

    Details

    • Operating System:
      ALL
    • Steps To Reproduce:
      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 } }   );

      Description

      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
      		}
      	}
      );
      

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                2 Vote for this issue
                Watchers:
                12 Start watching this issue

                Dates

                • Created:
                  Updated: