Hi,
Code snippet:
db.stagingcollection.aggregate([
{"$lookup": {"from": "product" ....}}
{"$merge": { "into": "stagingcollection" }}
]);
I have a requirement to run aggregate on “staging” collection and lookup on “product” collection to perform an equality match and retrieve matched info from warehouse collection. This matched info will again be merged back to staging collection.
Staging collection (temporary) will be populated by running a series of 4-5 separate aggregate queries with $merge (by creating unique indexes) to the load required data. Finally the collated data in staging collection will be exported as a JSON and saved/embedded into another document for future download.
Once data is embedded, staging collection will be cleared for future run.
The above aggregate query fails as “staging collection” exists in both query criteria and $merge stage. Why does this fail, Can you please help me understand why this is not possible ?
As a workaround I’ve created one more temporary collection (warehouse) to write the intermediate results and then merge to staging collection.
PS: staging, warehouse, products collection exists in the same database.