Lookup returning more than the number of elements in the default array

Hi

I have a collection with an attribute called tag_ids. It is a array and the data is like:

0:2
2:19

And I have another collection called tags with a structure like:

{
   id: 2,
   name: 'Factory'
}
{
   id:19,
   name:'Account'
}

After joining this two tables with my aggregation I got another field but instead of two elements in my new array I got 7

lookup:
{
  from: 'tags',
  localField: 'tag_ids',
  foreignField: 'id',
  as: 'str_tags_id'
}

The stranger is that when I try to filter the array the filter is not applied.

“str_tags_id.project_id”:393382

Could someone tell me why this occurs?

Hello, @Ezequias_Rocha!

Probably, you have documents with duplicate ‘id’ field.
‘id’ field, unlike ‘_id’ does not have to be necessarily unique.

Yes @slava I have duplicate Ids but I have another field I would like to use in the lookup. Is that possible? Is there any alternative if my lookup collection has duplicate Ids?

If I could use the project_id field in the same step or in another I would like to use.

Regards
Ezequias

Can someone tell me why this kind of thing occurs?

Yes. You can use any fields to join collections. See examples.

It is possible to filter out the duplicates in the nested pipeline, like this:

db.players.aggregate([
  {
    $match: {},
  },
  {
    $lookup: {
      from: 'teams',
      let: {
        team: '$fromTeam'
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: ['$teamName', '$$team']
            }
          },
        },
        {
          $group: {
            _id: '$teamName',
            country: {
              $first: '$country',
            },
            trainer: {
              $first: '$trainer',
            }
          }
        }
      ],
      as: 'joinedTeam',
    }
  },
]).pretty();

For this dataset:

db.players.insertMany([
  { player: 'Oleg', fromTeam: 'A' },
  { player: 'Piotr', fromTeam: 'B' }
]);

db.teams.insertMany([
  { teamName: 'A', country: 'Ukraine', trainer: 'Stas' },
  { teamName: 'B', country: 'Poland', trainer: 'Pawel' },
  { teamName: 'B', country: 'Poland', trainer: 'Pawel' }
]);

You will get this result:

[
  {
    "player" : "Oleg",
    "fromTeam" : "A",
    "joinedTeam" : [
      {
        "_id" : "A",
        "country" : "Ukraine",
        "trainer" : "Stas"
      }
    ]
  },
  {
    "player" : "Piotr",
    "fromTeam" : "B",
    "joinedTeam" : [
      {
        "_id" : "B",
        "country" : "Poland",
        "trainer" : "Pawel"
      }
    ]
  }
]

But, that will probably be not as performant, as you may want :slight_smile:
Better use a prop, that contain a unique value for document identification, like _id.

1 Like

Thank you @slava I peform the filter and it works perfectly.

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