$setIntersection works only randomly

[Chris_Bernil]

I want to query a match between records in my db based on certain tags. The match would be calculated based on a formula and the intersection of the tags. Now, even querying the intersection doesn’t work…always. Sometimes it does, sometimes it doesn’t. In my example, if I change the displayName attribute to something else (add or remove one character, the query works. In its current state (for demo purposes) it doesn’t as it does not deliver the one intersection match for the last doc with id 3.

https://mongoplayground.net/p/KAYPoV29RFO

That’s my query:

db.collection.aggregate([
  {
    $match: {
      "_id": "1"
    }
  },
  {
    "$lookup": {
      from: "collection",
      let: {
        "criteria": "$tags"
      },
      pipeline: [
        {
          $project: {
            "match": {
              $setIntersection: [
                "$tags",
                "$$criteria"
              ]
            },
            
          }
        }
      ],
      as: "result"
    }
  },
  {
    $project: {
      "tags": 0
    }
  },
  
])

Here is the example data (simplified):

[
  { "_id": "1", "tags": [{ "_id": "a", "displayName": "a", "level": 1}, {"_id": "b", "displayName": "b", "level": 2}, {"_id": "c", "displayName": "c", "level": 3}]},
  {"_id": "2", "tags": [{"_id": "a", "displayName": "a", "level": 1}, {"_id": "b", "displayName": "b", "level": 2}]},
  {"_id": "3", "tags": [{"_id": "a", "displayName": "a", "level": 1}, {"_id": "d", "displayName": "d", "level": 4}]}
]

and the result as it is: (expected is 3 matches for id 1, 2 matches for id 2 and one for the last id. However, the last result has 0 elements in the intersection result. Again, when i change “displayName” to “displayNam” or “displayNames” (obviously in all docs), it give the correct result…

[{
  "_id": "1", "result": [
    {"_id": "1", "match": [{"_id": "a", "displayName": "a", "level": 1}, {"_id": "b", "displayName": "b", "level": 2},{"_id": "c","displayName": "c","level": 3}]},
    {"_id": "2", "match": [{"_id": "a", "displayName": "a", "level": 1}, {"_id": "b", "displayName": "b", "level": 2}]},
    {"_id": "3","match": [*here should be the match to _id: "a", but it's not (always) there*]}
  ]
}]

Does anyone have an idea what I am missing here?

Thanks in advance,

Chris

[Pavel_Duchovny]

Hi @Chris_Bernil ,

Unfortunately, in my experience https://mognoplayground.net does not always provide consistent MongoDB emulation and behaviour.

I recommend loading the data and using one of the official MongoDB tools or drivers to rule out if something works or not.

Using your queries in MongoDB shell or compass against a real MongoDB instance return consistent results:

{ _id: '1',
  result: 
   [ { _id: '1',
       match: 
        [ { _id: 'a', displayName: 'a', level: 1 },
          { _id: 'b', displayName: 'b', level: 2 },
          { _id: 'c', displayName: 'c', level: 3 } ] },
     { _id: '2',
       match: 
        [ { _id: 'a', displayName: 'a', level: 1 },
          { _id: 'b', displayName: 'b', level: 2 } ] },
     { _id: '3',
       match: [ { _id: 'a', displayName: 'a', level: 1 } ] } ] }

So although playground is a convenient tool, its flaky and therefore use the queries against a certified server.

There is a very nice web shell interface that can be instantly consumed for tests:
https://mws.mongodb.com/

Thanks
Pavel

[Chris_Bernil]

Hi Pavel,

thanks for your response. I actually did try this on my local mongo installation via Studio3T before writing this post. But after banging my head against the wall with this for a few hours (with the real query being way more complex than this simplified example) in the early morning I must have copied the data badly, as it does work now locally.

Thank you for taking the time,

Chris