CODEWITHSUNDEEP
mongodbmongodb-querypymongoaggregation-framework
Adriano Martins
Adriano Martins

Reputation: 1808

Returning the matched element from a $in query

I have several documents that follow this structure:

{
  "queue-type": <integer>,
  "participants": [{
     "id": <integer>,
     "level": <level>,
     "flags": <integer>
  }]
}

And there is an multikey index on participants.id.

During the code, there is a find query, as follows: db.queues.find({"participants.id": {"$in": [2, 3, 4]}}), which gives the following as result:

{"queue-type": 1, "participants": [{"id": 1, "level": 10, "flags":4},{"id": 2, "level": 10, "flags":8}]}
{"queue-type": 25, "participants": [{"id": 5, "level": 10, "flags":4},{"id": 15, "level": 10, "flags":8},{"id": 4, "level": 10, "flags":8}]}

Is there any way to also retrieve the the element that was used to match on the query? Something like:

{"queue-type": 1, "_matched": 2, "participants": [{"id": 1, "level": 10, "flags":4},{"id": 2, "level": 10, "flags":8}]}
{"queue-type": 25, "_matched": 4, "participants": [{"id": 5, "level": 10, "flags":4},{"id": 15, "level": 10, "flags":8},{"id": 4, "level": 10, "flags":8}]}

PS: I'm trying to avoid looping through the [2, 3, 4] and the participants array, since they are way bigger.

Examples: queues

{"queue-type": 1, "participants": [{"id": 1, "level": 10, "flags":4},{"id": 2, "level": 10, "flags":8}]}
{"queue-type": 2, "participants": [{"id": 3, "level": 10, "flags":0}]}
{"queue-type": 3, "participants": [{"id": 4, "level": 10, "flags":4},{"id": 5, "level": 10, "flags":8}]}
{"queue-type": 4, "participants": [{"id": 7, "level": 10, "flags":4},{"id": 8, "level": 10, "flags":8},{"id": 9, "level": 10, "flags":8}]}

The result I wish to retrieve:

db.queues.find({"participants.id": {"$in": [2]}});
{"queue-type": 1, "_matched": 2, "participants": [{"id": 1, "level": 10, "flags":4},{"id": 2, "level": 10, "flags":8}]}

Note the "_matched" element is the same as the 'participant.id' given on the search query

Another example:

db.queues.find({"participants.id": {"$in": [2, 3, 6]}});
{"queue-type": 1, "_matched": 2, "participants": [{"id": 1, "level": 10, "flags":4},{"id": 2, "level": 10, "flags":8}]}
{"queue-type": 2, "_matched": 3, "participants": [{"id": 3, "level": 10, "flags":0}]}

Multiple matches example:

db.queues.find({"participants.id": {"$in": [1, 2, 3]}});
{"queue-type": 1, "_matched": 1, "participants": [{"id": 1, "level": 10, "flags":4},{"id": 2, "level": 10, "flags":8}]}
{"queue-type": 1, "_matched": 2, "participants": [{"id": 1, "level": 10, "flags":4},{"id": 2, "level": 10, "flags":8}]}
{"queue-type": 2, "_matched": 3, "participants": [{"id": 3, "level": 10, "flags":0}]}

A not good solution is to simply copy the 'participants' data ('participants-cpy') and then run: 

db.queues.find({"participants-cpy.id": {"$in": [2]}}, {"participants-cpy.$":1, "participants":1, "_id": 1, "queue-type":1})

which could be used to retrieve the element used to 'match' the query, but that would generate duplicated data - which is pretty bad :p

Upvotes: 3

Views: 77

Answers (1)

Sede
Sede

Reputation: 61243

A regular find query will not work here. You need to use the aggregation framework. In your pipeline you need to select only those documents that match your query criteria using the $match pipeline operator.

The next and last stage in the pipeline is the $project stage where you use add the new field "_matched" to your documents. If you think about it, you will realize that the new field nothing other than an array that contains the elements that appear in your "partcipantsId" array/list and in all the "id" from the "participants" field in your document.

To get that value you simply perform a set intersection operation on the participantsId array and the array of "id" from "participants" using the $map and the $setIntersection operator. Note that the resulted array contains only unique entries because $setIntersection filters out duplicates.

participantsId = [1, 2, 3]
db.queues.aggregate([
    { "$match": { "participants.id": { "$in": participantsId } } }, 
    { "$project": {
        "_matched": {
            "$setIntersection": [
                { "$map": { 
                    "input": "$participants", 
                    "as": "p", 
                    "in": "$$p.id"
                }}, 
                participantsId
            ]
        }, 
        "queue-type": 1, 
        "participants": 1
    }}
])

Upvotes: 1

Related Questions