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

XMLWordPrintableJSON

    • 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
    • None
    • None
    • None
    • None
    • None
    • None
    • None

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

        1. SERVER-30812.js
          2 kB

            Assignee:
            Alexander Ignatyev
            Reporter:
            Damian Mastylo
            Votes:
            2 Vote for this issue
            Watchers:
            14 Start watching this issue

              Created:
              Updated:
              Resolved: