Imagine two mongo collections.
FirstCollection is formatted like this:
{
"JoinField": "this is my data"
}
SecondCollection is formatted like this (the real data is more complex and needs flattening):
{
"FirstField" : "food",
"SecondField": "bar",
"Properties" : {
"JoinField": "this is my data"
}
}
SecondCollection contains duplicates and I'm trying to join the two tables and flatten the data into one projection.
This query will broadly do what I want:
db.getCollection('FirstCollection').aggregate([
{
"$lookup": {
from: 'SecondCollection',
localField: 'JoinField',
foreignField: 'Properties.JoinField',
as: 'secondCollection'
}
},
{
"$unwind": "$secondCollection"
},
{
"$project": {
"JoinField" : 1,
firstField : "$secondCollection.FirstField",
secondField : "$secondCollection.SecondField",
}
}
])
The only problem is that this contains duplicates in itself because it repeats the data for each duplicate in SecondCollection that matches a line in FirstCollection.
How can I only get the first record (or any single copy of the record) from SecondCollection?
EDIT: Apologies for lack of clarity. What I will currently get from that query, presuming a duplicate in SecondCollection, is this:
{
"JoinField": "this is my data".
"FirstField" : "food",
"SecondField": "bar"
}
{
"JoinField": "this is my data".
"FirstField" : "food",
"SecondField": "bar"
}
What I want is this:
{
"JoinField": "this is my data".
"FirstField" : "food",
"SecondField": "bar"
}
Try lookup with aggregation pipeline from MongoDB v3.6,
let to pass local field to second collection pipeline,pipeline to match field and, set limit to return single document {
"$lookup": {
from: "SecondCollection",
let: { "jfield": "$JoinField" },
pipeline: [
{ $match: { $expr: { $eq: ["$Properties.JoinField", "$$jfield"] } } },
{ $limit: 1 }
],
as: "secondCollection"
}
},
Second option from MongoDB v3.2, put a $set stage after $lookup and before $unwind,
$arrayElemAt return element from specific position {
$set: {
secondCollection: { $arrayElemAt: ["$secondCollection", 0] }
}
}
If I understand the question correctly, you could try this
db.b.aggregate([ //Used second collection as the source
{
"$group": { //Removing duplicates in the join field
"_id": "$properties.joinField",
FirstField: {
"$addToSet": "$FirstField"
},
SecondField: {
"$addToSet": "$SecondField"
},
"JoinField": {
$first: "$Properties.JoinField"
}
}
},
{
"$lookup": {
from: "a",
localField: "JoinField",
foreignField: "JoinField",
as: "FirstCollection"
}
},
{
"$unwind": "$FirstCollection"
},
{
"$project": {
"JoinField": 1,
firstField: "$FirstField",
secondField: "$SecondField",
}
}
])