Aggregation with $lookup and $unwind between two collections doesn't deliver expected results

Working with Data
, ,
1

Please excuse if I made a newbie error in my code here.

I’m trying to merge the results of two queries (against different collections).

One is the Gedmatches collection and this is the expected doc that should join with the 2nd collection:

the query:
db.gedmatches.find({ kit1: 'BkHXGDn4z', kit2: 'S1B52MvnVz' },{kit1:1,kit2:1})

the result:
{ "_id" : "w8WSXAjyvC8Ncxers", "kit1" : "BkHXGDn4z", "kit2" : "S1B52MvnVz" }

The other one is from the Kits collection and this doc should come up as a result of the aggregation query (kitUser is BkHXGDn4z):

the match query in my code:
nameDoc.runningNo is 1184

{ "_id" : "qfTzZ4TSaGuBhCuuK", "names" : [ 1183, 1184, 1185, 1186 ], "name" : "Sheridan Lugo", "kit23" : "S1B52MvnVz" }

Here’s my query, language is MeteorJS (nodeJS):

const pipeline = [
            {
                // get gedmatches with both kitUser on kit1/kit2
                $match: { $or: [{ kit1: kitUser }, { kit2: kitUser }] },
            },
            { $project: { kit1: 1, kit2: 1 } },
            {
                // let's create arrays for both kit1/kit2 and push kits
                $group: {
                    _id: null,
                    kits1: { $push: '$kit1' },
                    kits2: { $push: '$kit2' },
                },
            }, {
                // merge both arrays into 1
                $project: { 'cousins': { '$setUnion': ['$kits1', '$kits2'] } },
            }, {
                // filter out kitUser from the array
                $project: {
                    'cousins': {
                        $filter: {
                            'input': '$cousins',
                            'as': 'cousin',
                            'cond': { $ne: ['$$cousin', kitUser] },
                        },
                    },
                },
            }, {
                // let's get docs from kits from our array
                $lookup: {
                    'from': 'kits',
                    'localField': 'cousins',
                    'foreignField': 'kit23',
                    'as': 'cousins',
                },
            }, {
                // unwind array so we have all as objects
                $unwind: {
                    'path': '$cousins',
                    'preserveNullAndEmptyArrays': false,
                },
            }, {
                // run our searchTerm using regexFindAll to get results that match it
                $project: { 'kit23': '$cousins.kit23', 'match': { names: nameDoc.runningNo } },
            }, {
                // unwind our results as now we have a nested array, we want only 1
                $unwind: { 'path': '$match', 'preserveNullAndEmptyArrays': false },
            }, {
                // get the kits docs that matched `Kits` query
                $lookup: {
                    'from': 'kits',
                    'localField': 'kit23',
                    'foreignField': 'kit23',
                    'as': 'doc',
                },
            }, {
                // unwind doc field, so we get the object and not an array with the object inside
                $unwind: {
                    'path': '$doc',
                    'preserveNullAndEmptyArrays': false,
                },
            }, {
                // only send back kit23 and doc on our object
                $project: {
                    '_id': 0,
                    'kit23': '$kit23',
                    'doc': '$doc',
                },
            },
        ];
        const gedMatchesArray = await GedmatchesRaw.aggregate(pipeline, { session: mongoSession }).toArray();

Result is an empty array :frowning:

Any help is highly appreciated!

3

Hi :wave: @Andreas_West,

Welcome to the MongoDB Community forums :sparkles:

As per my understanding of your above question, you have two collections that contain the following documents:

  • Kits Collection:
{
  "_id": "qfTzZ4TSaGuBhCuuK",
  "names": [
    1183,
    1184,
    1185,
    1186
  ],
  "name": "Sheridan Lugo",
  "kit23": "S1B52MvnVz"
}
  • Gedmatches Collection
{
  "_id": "w8WSXAjyvC8Ncxers",
  "kit1": "BkHXGDn4z",
  "kit2": "S1B52MvnVz"
}

Please correct me if I’m wrong and share the sample document from both collections.

Please elaborate on what you mean by merging two queries. Are you trying to lookup up other collections, then what is the sample output you are expecting?

Also, share the workflow of the code and the MongoDB version you are using.

Best,
Kushagra

1 Like
4

That’s correct.

Sorry, merging is the wrong term, it’s a classic join via $lookup.

The sample output that I’m expecting is:

[{"kit23":"S1B52MvnVz",
"doc":
{"_id":"qfTzZ4TSaGuBhCuuK","names":[1183,1184,1185,1186],"name":"Sheridan Lugo","kit23":"S1B52MvnVz"},
}]

Sorry for the bad formatting, it’s an array of objects (as there can be more results than just one but in this case I expect exactly 1 object to be returned.

It has the kit23 field and the doc object.

MongoDb 4.2 and the I’m not sure what you mean by workflow of the code as I posted the relevant code in my original post.

Thanks you,

Andreas

5

Hi :wave: @Andreas_West,

Thanks for sharing the information.

To obtain the desired result, I ran an aggregation query on the Kits collections, sharing it for your reference:

db.Kits.aggregate([
  {
    $lookup: {
      from: "books",
      localField: "kit23",
      foreignField: "kit2",
      as: "matches",
    },
  },
  {
    $unwind: "$matches",
  },
  {
    $project: {
      _id: 1,
      names: 1,
      name: 1,
      kit23: 1,
      matches: {
        $cond: {
          if: {
            $eq: ["$matches.kit2", "$kit23"],
          },
          then: "$matches",
          else: null,
        },
      },
    },
  },
  {
    $project: {
      kit23: 1,
      doc: "$$ROOT",
    },
  },
  {
    $project: {
      _id: 0,
      kit23: 1,
      doc: {
        _id: 1,
        names: 1,
        name: 1,
        kit23: 1,
      },
    },
  },
])

Here I’ve used $lookup stage joins the Kits collection with the Gedmatches collection based on the kit23 field from Kits and the kit2 field from Gedmatches.

Next, I used the $unwind to deconstruct the resulting array of matches from the previous stage.

After that used $project stage to project only the fields needed from both collections and also created a new field called matches that contains only the match where kit2 is equal to kit23.

Following up on that I’ve used $match to filter out any documents where matches is null and finally used $project to get the desired output, which satisfies your expected result:

{
  "kit23": "S1B52MvnVz",
  "doc": {
    "_id": "qfTzZ4TSaGuBhCuuK",
    "names": [
      1183,
      1184,
      1185,
      1186
    ],
    "name": "Sheridan Lugo",
    "kit23": "S1B52MvnVz"
  }
}

I hope it helps!

Best,
Kushagra

1 Like
7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.