My database contains 3 collections: “patients”, “therapists”, “subscriptions”.
I’ve configured CSFLE on the db connection, providing a json schema which defines field level encryption only on the ‘name’ and ‘email’ fields in the ‘patients’ collection.
The field level encryption / decryption on the ‘patients’ collection works as expected.
However, now an unrelated aggregation query fails with “MongoError: Pipeline over an encrypted collection cannot reference additional collections.”
This error occurs when executing an aggregation query on the ‘therapists’ collection, which includes a $lookup from the ‘subscriptions’ collection (not “over an encrypted collection” as the error suggests).
Neither ‘therapists’ nor ‘subscriptions’ are defined in the CSFLE json schema, and are not encrypted.
I don’t understand why should this $lookup on unencrypted collections, lead to an error.
According to the documentation
Automatic client-side field level encryption supports the $lookup and
$graphLookup only if the from collection matches the collection on
which the aggregation runs against (i.e. self-lookup operations).
While the limitation may be acceptable when dealing with the ‘patients’ collection, I don’t think it is acceptable when dealing with other, non CSFLE, collections.
I know that I can create 2 separate MongoClient instances, one with CSFLE enabled, and one without, and use the non CSFLE client for the $lookup, but this would introduce extra complexity, and it doesn’t seem like a clean and reasonable solution to me.
BTW, I’m using mongoose, but the behaviour is the same when using mongodb directly.
Would you say this is a bug with mongodb?
Any suggestions would be greatly appreciated.