"$lookup with 'pipeline' may not specify 'localField' or 'foreignField'"

[Julien_Sarazin]

Hello,

We were working with the aggregate pipeline ($lookup) and faced an issue ("$lookup with ‘pipeline’ may not specify ‘localField’ or ‘foreignField’") with the community version that does not happen in the Enterprise edition.

Can you guys tell me if it’s expected behavior?

Thanks !

{
  from: 'comments',
  localField: '_id',
  foreignField: 'actionId',
  as: 'comments',
  pipeline: [
    { $sort: { createdDate: 1 } },   
  ],
}

[Julien_Sarazin]

FYI We upgraded to 5.0.14 community edition and had the same issue.

[Jason_Tran]

Hi @Julien_Sarazin - Welcome to the community.

Thanks for providing those details regarding the error message.

FYI We upgraded to 5.0.14 community edition and had the same issue.

It’s quite interesting as I’ve tried to use the same $lookup details you’ve provided (although on different documents in my test environment) on a 5.0.14 community edition test server but could not replicate the error. Please see the details below:

Can you confirm / provide the following information:

1. MongoDB Compass version in use
2. OS in use
3. 3-4 sample documents from each collection
4. If the same error occurs when connecting via mongosh to the Community Edition server and running the same aggregation pipeline.

Regards,
Jason

[Pedro_Verdugo]

I found the same issue, was using 5.0.9 locally and was wondering why it behaved differently than my server which has 5.0.13, went ahead and upgraded to 5.0.14 locally but same problem on Windows 10

1. 1.34.1
2. Windows 10
3. Cannot fully share, but is a very simple scenario, as soon as I remove the pipeline field, I get results as expected
4. Yes, I first found it running outside of Compass

The only difference is that the server version where it works is running Ubuntu 22

[Jason_Tran]

Have you used setFeatureCompatibilityVersion to “4.4” for that server which is receiving the error?

I have the pipeline below used in my tests:

{
  '$lookup': {
    from: 'comments',
    localField: '_id',
    foreignField: 'actionId',
    as: 'comments',
    pipeline: [ { '$sort': { createdDate: 1 } } ]
  }
}

Version 5.0.14 server:

Current Mongosh Log ID:	12345b2085df6bb1e9bc4275
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+1.6.0
Using MongoDB:		5.0.14
Using Mongosh:		1.6.0

/// pipeline run:
test> db.local.aggregate(
{
  '$lookup': {
    from: 'comments',
    localField: '_id',
    foreignField: 'actionId',
    as: 'comments',
    pipeline: [ { '$sort': { createdDate: 1 } } ]
  }
})
[
  {
    _id: 1,
    comments: [
      {
        _id: ObjectId("63895a6c71c54b95e649921a"),
        actionId: 1,
        createdDate: ISODate("2022-12-02T01:52:44.467Z")
      }
    ]
  }
]

/// Set the feature compatibility version to "4.4"
test> db.adminCommand({setFeatureCompatibilityVersion:"4.4"})
{
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1669946175, i: 1029 }),
    signature: {
      hash: Binary(Buffer.from("0000000000000000000000000000000000000000", "hex"), 0),
      keyId: Long("0")
    }
  },
  operationTime: Timestamp({ t: 1669946175, i: 1029 })
}

/// same pipeline run again:
test> db.local.aggregate(
{
  '$lookup': {
    from: 'comments',
    localField: '_id',
    foreignField: 'actionId',
    as: 'comments',
    pipeline: [ { '$sort': { createdDate: 1 } } ]
  }
})

/// same error you have received:
MongoServerError: $lookup with 'pipeline' may not specify 'localField' or 'foreignField'
test>

You can check the FCV using the following on a mongod:

db.adminCommand( {
                    getParameter: 1,
                    featureCompatibilityVersion: 1
                 }
               )

Regards,
Jason

[Jason_Tran]

As an additional note, I tested the $lookup via Compass and managed to get the same error you have provided when I set the FCV to “4.4”.

[Pedro_Verdugo]

Ahh that was it, I wasn’t doing many aggregations on my local so totally forgot about that, thanks.

[Jason_Tran]

Thank for updating the post Pedro. Glad to hear that was it.

[Julien_Sarazin]

Hello @Jason_Tran

Sorry for the late reply.

1. I am using Compass Version 1.33.1 (1.33.1)
2. MacOS Monterey 12.4
3. Unfortunately I cannot share document samples since they contain personal information
4. it does

[Julien_Sarazin]

It was indeed related to the FCV 4.4! Thanks a lot!
Setting db.adminCommand( { setFeatureCompatibilityVersion: "5.0" } ) fixed the problem