Find keys with value in another collection

Question

We have a request collection ,with example documents as below:

{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ4",
    "scrip" : "5647"
}
{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ4",
    "scrip" : "5648"
}
{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ1",
    "scrip" : "0001"
}
{
    "_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ1",
    "scrip" : "0456"
}

Scrips collection :

{
    "_id" : ObjectId("xxxx"),
    "scrip" : "0001"
}
{
    "_id" : ObjectId("xxxx"),
    "scrip" : "0456"
}
{
    "_id" : ObjectId("xxxx"),
    "scrip" : "5647"
}

We need to return the requests which have all the corresponding scrip in the Scrips collection.

Expected Output :

{"_id" : ObjectId("xxxxxx"),
    "requestId" : "REQ1",
    "scrip" : ["0001","0456"]
}

How do we achieve this?

I didn't quiet understand your question, what do you mean by subset ? How did you get `["0001","0456"]` ? — whoami - fakeFaceTrueSoul, Jan 25 '20 at 02:49
REQ1 has 2 entries in th erquest collection - having scrip as 0001 and 0456 respectively — IUnknown, Jan 25 '20 at 04:19
I'm seeing only one `REQ1` in request collection is that a typo? — whoami - fakeFaceTrueSoul, Jan 25 '20 at 04:34
So you wanted to check request collections `scrip` exists in scrips collection or not & then if exists push those into an array, if not no ? — whoami - fakeFaceTrueSoul, Jan 25 '20 at 04:40
Only for those requests - whose every scrip is a part of the scrip collection else not — IUnknown, Jan 25 '20 at 04:42

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-01-25T06:05:17.180

0

Try this :

db.requests.aggregate([
    /** Filter to reduce dataset */
    { $match: { requestId: { $in: ['REQ4', 'REQ1', 'REQ5'] } } },
    /** check for matching docs based on scrip */
    {
        $lookup:
        {
            from: "scrips",
            localField: "scrip",
            foreignField: "scrip",
            as: "scrips"
        }
    }, { $addFields: { scrips: { $arrayElemAt: ['$scrips', 0] } } },
    /** Group all docs in request collection based on requestId */
    { $group: { _id: '$requestId', scrip: { $push: '$scrip' }, scrips: { $push: '$scrips' } } },
    /** Remove request docs if all request docs doesn't have match in scrips */
    { $match: { $expr: { $eq: [{ $size: '$scrip' }, { $size: '$scrips' }] } } },
    /** Transform final result */
    { $project: { _id: 0, requestId: '$_id', scrip: 1 } }
])

Test : MongoDB-Playground

edited Jan 25 '20 at 06:05

answered Jan 25 '20 at 04:49

whoami - fakeFaceTrueSoul

17,086
6
32
46

Thanks.However,this would scan the entire requests and scripts collection(~1M records).If I were to trigger this only for a chunked list of requests passed in as input,how would that happen. – IUnknown Jan 25 '20 at 05:10
@IUnknown : Maybe it might matter or may not but Yes that's correct I would always suggest to keep your dataset as low as possible, What will be your input look like ? Basically use a `$match` stage – whoami - fakeFaceTrueSoul Jan 25 '20 at 05:19
It would porbably be an array ['REQ4','REQ1'.....] to restrict the scan each iteration – IUnknown Jan 25 '20 at 05:26
@IUnknown : Then you can add this as first stage :: `{$match : {requestId : {$in : ['REQ4','REQ1'.....]}}}` – whoami - fakeFaceTrueSoul Jan 25 '20 at 05:28
Yes - but this would return requests which have even a partial match e.g REQ4...instead of ONLY complete match candidates like REQ1 – IUnknown Jan 25 '20 at 05:34
@IUnknown : I've not got that, Can you give me a sample of what you're talking about – whoami - fakeFaceTrueSoul Jan 25 '20 at 05:35
REQ4 has 2 entries in requests collection - of which only 1(5647) exists in scrip collection.REQ1 has 2 entries in requests - and both scrips exist in scrips collection..complete match – IUnknown Jan 25 '20 at 05:39
@IUnknown : So do you want two both of `REQ4`'s entries in result ? but that's what not needed as O/P ?? – whoami - fakeFaceTrueSoul Jan 25 '20 at 05:42
Only need REQ1 and its scrips in O/P as that request is a complete match with scrips collection – IUnknown Jan 25 '20 at 05:44
What is the addField doing - is it selecting only first match – IUnknown Jan 27 '20 at 05:47
@IUnknown : replacing array with an object – whoami - fakeFaceTrueSoul Jan 27 '20 at 08:53
The addfield does not add 'scrips' field for the records which fail in lookup - how does that happen.The solution works though – IUnknown Jan 27 '20 at 09:08
@IUnknown : if scrips array is there then it would get first object and replace scrips array with scrips object if not it won’t do anything – whoami - fakeFaceTrueSoul Jan 27 '20 at 14:26

Find keys with value in another collection

1 Answers1