CODEWITHSUNDEEP
mongodbnosql
Kamalakannan J
Kamalakannan J

Reputation: 2998

MongoDB: $nin and $in not working together in $elemMatch

I have below documents in my transactions collections.

{txnId: 1, amount: 200, tags:["monthly", "recharge"]},
{txnId: 2, amount: 4000, tags:["monthly", "salary"]},
{txnId: 3, amount: 1000, tags:["monthly", "waterbill"]},
{txnId: 4, amount: 400, tags:["monthly", "electricity"]},
{txnId: 5, amount: 300, tags:["dinner", "daily"]}

I want to fetch all the documents which are having monthly but not salary

Some queries I tried and their respective outputs:

Query 1:

db.transactions.find({tags: {$elemMatch: {$in: ["monthly"], $nin: ["salary"]}}})

Output 1:

{txnId: 1, amount: 200, tags:["monthly", "recharge"]}
{txnId: 2, amount: 4000, tags:["monthly", "salary"]}
{txnId: 3, amount: 1000, tags:["monthly", "waterbill"]}
{txnId: 4, amount: 400, tags:["monthly", "electricity"]}

Query 2:

db.transactions.find({tags: {$nin: ["salary"]}})

Output 2:

{txnId: 1, amount: 200, tags:["monthly", "recharge"]}
{txnId: 3, amount: 1000, tags:["monthly", "waterbill"]}
{txnId: 4, amount: 400, tags:["monthly", "electricity"]}
{txnId: 5, amount: 300, tags:["dinner", "daily"]}

Query 3:

db.transactions.find({tags: {$nin: ["salary"]}, tags: {$elemMatch: {$in: ["monthly"]}}})

Output 3:

{txnId: 1, amount: 200, tags:["monthly", "recharge"]}
{txnId: 2, amount: 4000, tags:["monthly", "salary"]}
{txnId: 3, amount: 1000, tags:["monthly", "waterbill"]}
{txnId: 4, amount: 400, tags:["monthly", "electricity"]}

Why $in and $nin not working together, and even $nin not working as expected ? or did I misunderstand ?

Upvotes: 2

Views: 793

Answers (1)

jerry
jerry

Reputation: 745

You can use $nin & $in together too. The below query will give the expected records. Hope need not use $elemMatch.

db.transactions.find({$and:[{tags:{$in:['monthly']}}, {tags:{$nin:['salary']}}]}).toArray()

Output:

[
{txnId: 1, amount: 200, tags:["monthly", "recharge"]},
{txnId: 3, amount: 1000, tags:["monthly", "waterbill"]},
{txnId: 4, amount: 400, tags:["monthly", "electricity"]}
]

Upvotes: 3

Related Questions