Trying to understand with either C#/Linq or even raw Mongodb query itself how to join multiple arrays as a cartesian product.
Say for example I had a collection that I filtered down to the following two documents:
[ {"movie":"starwars","showday":"monday"}, {"movie":"batman","showday":"thursday"}, {"movie":"sleepless","showday":"tuesday"} ] [ {"actor":"angelina","location":"new york"}, {"actor":"jamie","location":"california"}, {"actor":"mcavoy","location":"arizona"} ] How can I join each item in each array to produce the following type of result?
[{"movie":"starwars","showday":"monday","actor":"angelina","location":"new york"}, {"movie":"batman","showday":"thursday","actor":"angelina","location":"new york"}, {"movie":"sleepless","showday":"tuesday","actor":"angelina","location":"new york"}, {"movie":"starwars","showday":"monday","actor":"jamie","location":"california"}, {"movie":"batman","showday":"thursday","actor":"jamie","location":"california"}, {"movie":"sleepless","showday":"tuesday","actor":"jamie","location":"california"}, {"movie":"starwars","showday":"monday","actor":"mcavoy","location":"arizona"}, {"movie":"batman","showday":"thursday","actor":"mcavoy","location":"arizona"}, {"movie":"sleepless","showday":"tuesday","actor":"mcavoy","location":"arizona"}] I am looking for a solution that could work with any number of documents. So for example if in this example there was a 3rd document that also had 3 object arrays that would produce a result set of 27 items in the array - or 27 rows as it were.
Hoping for a solution of how to use C# (Linq?) Mongodb Driver to query and return data like this but would be open to even a mongodb specific query as I can hopefully reverse the logic from there. thank you
1 Answers
Answers 1
You can try below aggregation pipeline.
Note mergeObjects aggregation operator is available in the 3.5.6 + development release which will be rolled into upcoming 3.6 release.
db.collection.find(); { "data" : [ [ { "movie" : "starwars", "showday" : "monday" }, { "movie" : "batman", "showday" : "thursday" }, { "movie" : "sleepless", "showday" : "tuesday" } ], [ { "actor" : "angelina", "location" : "new york" }, { "actor" : "jamie", "location" : "california" }, { "actor" : "mcavoy", "location" : "arizona" } ] ] } Aggregation:
aggregate({ $project: { cp: { $reduce: { input: "$data", initialValue: { $arrayElemAt: ["$data", 0] // Set the initial value to the first element of the arrays. }, in: { $let: { vars: { currentr: "$$this", // Current processing element currenta: "$$value" // Current accumulated value }, in:{ $reduce: { input: { $map: { input: "$$currenta", as: "a", in: { $map: { input: "$$currentr", as: "r", in: { $mergeObjects: ["$$a", "$$r"] // Merge accumulated value with the current processing element } } } } }, initialValue: [], in: { $setUnion: ["$$value", "$$this"] // Reduce the merged values which will be used as accumulator for next element } } } } } } } } }); Reference: Cartesian product of multiple arrays in JavaScript
0 comments:
Post a Comment