MongoDB: How to match on the elements of an array?

Question

I have two collections as follows:

db.qnames.find()
{ "_id" : ObjectId("5a4da53f97a9ca769a15d49e"), "domain" : "mail.google.com", "tldOne" : "google.com", "clients" : 10, "date" : "2016-12-30" }
{ "_id" : ObjectId("5a4da55497a9ca769a15d49f"), "domain" : "mail.google.com", "tldOne" : "google.com", "clients" : 9, "date" : "2017-01-30” }

and

db.dropped.find()
{ "_id" : ObjectId("5a4da4ac97a9ca769a15d49c"), "domain" : "google.com", "dropDate" : "2017-01-01", "regStatus" : 1 }

I would like to join the two collections and choose the documents for which 'dropDate' field (from dropped collection) is larger than the 'date' filed (from qnames field). So I used the following query:

 db.dropped.aggregate( [{$lookup:{ from:"qnames", localField:"domain",foreignField:"tldOne",as:"droppedTraffic"}},
     {$match: {"droppedTraffic":{$ne:[]}  }}, 
{$unwind: "$droppedTraffic" } , 
{$match: {dropDate:{$gt:"$droppedTraffic.date"}}} ])

but this query does not filter the records where dropDate < date. Anyone can give me a clue of why it happens?

What is your mongo version ?

s7vr
– s7vr

2018-01-04 19:28:58 +00:00
Commented Jan 4, 2018 at 19:28 — s7vr
– s7vr, Commented Jan 4, 2018 at 19:28

Clement Amarnath · Accepted Answer · 2018-01-04 07:07:57Z

The reason why you are not getting the record is

Date is used as a String in your collections, to make use of the comparison operators to get the desired result modify your collection documents using new ISODate("your existing date in the collection")

Please note even after modifying both the collections you need to modify your aggregate query, since in the final $match query two values from the same document is been compared.

Sample query to get the desired documents

db.dropped.aggregate([ 
    {$lookup: { 
       from:"qnames", 
       localField:"domain", 
       foreignField:"tldOne", 
       as:"droppedTraffic"}
    },
    {$project: {
       _id:1, domain:1, 
       regStatus:1,
       droppedTraffic: {
          $filter: { 
             input: "$droppedTraffic", 
             as:"droppedTraffic", 
             cond:{ $gt: ["$$droppedTraffic.date", "$dropDate"]}
          }
       }
     }} 
])

In this approach given above we have used $filter which avoids the $unwind operation

mickl · Accepted Answer · 2018-01-04 07:07:13Z

1

You should use $redact to compare two fields of the same document. Following example should work:

db.dropped.aggregate( [
    {$lookup:{ from:"qnames", localField:"domain",foreignField:"tldOne",as:"droppedTraffic"}},
    {$match: {"droppedTraffic":{$ne:[]}  }}, 
    {$unwind: "$droppedTraffic" }, 
    {
        "$redact": {
            "$cond": [
                { "$lte": [ "$dropDate", "$droppedTraffic.date" ] },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

answered Jan 4, 2018 at 7:07

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Collectives™ on Stack Overflow

MongoDB: How to match on the elements of an array?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related