MongoDB perform $match with two input array values?

Question

In MongoDB, I am trying to write a query where I have two input array Bills, Names where the first one contains billids and the second one contains names of the person. Also in reality Bills at index i and Names at index i is the actual document which we want to search in MongoDB.

I want to write a query such that Bills[i] = db_billid && Names[i] = db_name which means I want to return the result where at a particular index both billid and name matches.

I thought of using $in but the thing is I can apply $in in Bills but I don't know at which index that billid is found.

{ $and: [{ billid: { $in: Bills } }, {name: Names[**index at which this bill is found]}] }

Can anyone please help me how can I solve this ??

MongoDB Schema

var transactionsschema = new Schema({
    transactionid: {type: String},
    billid: {type: String},
    name: {type: String}
});

Sample documents in MongoDB

{ _id: XXXXXXXXXXX, transactionid: 1, billid : bnb1234, name: "sudhanshu"}, { _id: XXXXXXXXXXX, transactionid: 2, billid : bnb1235, name: "michael"}, { _id: XXXXXXXXXXX, transactionid: 3, billid : bnb1236, name: "Morgot"}

Sample arrays

Bills = ["bill1", "bill2", "bill3"], Names = ["name1", "name2", "name"]

Edit - If $in can work in array of objects then I can have array of object with keys as billid and name

var arr = [{ billid: "bill1", "name": "name1"}, {billid: "bill2", "name": "name2"}, {billid: "bill3", "name": "name3"}]

But the thing is then how can put below query

{ $and: [{ billid: { $in: arr.Bills } }, {name: arr.Names}] }

Could you add sample document and sample Bills and Name ?

mickl
– mickl

2018-06-17 16:53:24 +00:00
Commented Jun 17, 2018 at 16:53 — mickl
– mickl, Commented Jun 17, 2018 at 16:53
@mickl wait let me add it.

Sudhanshu Gaur
– Sudhanshu Gaur

2018-06-17 16:54:31 +00:00
Commented Jun 17, 2018 at 16:54 — Sudhanshu Gaur
– Sudhanshu Gaur, Commented Jun 17, 2018 at 16:54
@mickl I have added sample docs.

Sudhanshu Gaur
– Sudhanshu Gaur

2018-06-17 16:58:50 +00:00
Commented Jun 17, 2018 at 16:58 — Sudhanshu Gaur
– Sudhanshu Gaur, Commented Jun 17, 2018 at 16:58

mickl · Accepted Answer · 2018-06-17 20:12:19Z

2

Knowing that bills are unique but names may have duplicates you can use $indexOfArray to get index of matching bill and then use that index to compare names array at evaluated index (using $arrayElemAt to retrieve value). Also you have to check if value returned by $indexOfArray is not equal to -1 (no match)

var bills = ["bnb1234", "bnb1235"];
var names = ["sudhanshu", "michael"];

db.col.aggregate([
    {
        $match: {
            $expr: {
                $and: [
                    { $ne: [{ $indexOfArray: [ bills, "$billid" ] }, -1] },
                    { $eq: ["$name", { $arrayElemAt: [ names, { $indexOfArray: [ bills, "$billid" ] } ] }] },
                ]
            }
        }
    }
])

alternatively $let can be used to avoid duplication:

db.col.aggregate([
    {
        $match: {
            $expr: {
                $let: {
                    vars: { index: { $indexOfArray: [ bills, "$billid" ] } },
                    in: { $and: [ { $ne: [ "$$index", -1 ] }, { $eq: [ "$name", { $arrayElemAt: [ names, "$$index" ] }] }]}
                }
            }
        }
    }
])

edited Jun 17, 2018 at 20:12

answered Jun 17, 2018 at 17:17

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Sudhanshu Gaur Over a year ago

Suppose I have two nested queries first one is mine and the second one whose solution you have given, like `$match: {$and: { "transactionid": "12345678"}, { Your Query} }, now will it work ? Is this rightly nested or not?

mickl Over a year ago

You can add more conditions to $match, like

{     $and: [         { $ne: ["$billIndex", -1] },         { $ne: ["$nameIndex", -1] },         { $eq: ["$billIndex", "$nameIndex"] },         { $eq: [ "$transactionId": 123 ] }     ] }

is that what you mean ?

Sudhanshu Gaur Over a year ago

Leave it, just wanted to ask as Bills are unique fields but Names are not so, suppose Sample DB is [{ billid : bnb1234, name: "michael"}, {billid : bnb1235, name: "michael"}] Bills = ["bnb1234", "bnb1235"] and Names = ["michael", "michael"] Now the thing here is that when you will use $match to see index of both of them then for first one Names index will be 1 and Bills index will be 1 so it's a match but for the second one as Bills index is 2 but Names index is 1, not 2(because Names are not unique).

Sudhanshu Gaur Over a year ago

So how can I overcome that thing. Btw thanks for helping me out of here just one line close to the solution :)

Sudhanshu Gaur Over a year ago

Just found the solution :) changed your answer a bit, thanks a lot man. I have mentioned the solution in the post, change your answer here so that I can accept it (y).

|

Collectives™ on Stack Overflow

MongoDB perform $match with two input array values?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related