CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Catalin Marin
Catalin Marin

Reputation: 1242

MongoDB aggregate pipeline and field matching

I really need to understand if I made a broken design or I just don't find a right solution. A document in my collection looks like this:

{
  "_id" : {
    "owner" : "eight@home",
    "day" : 5.0,
    "month" : 0.0
  },
  "value" : {
    "userId" : 7.0,
    "session" : 5.0,
    "no_closed" : 1.0,
    "data" : {
      "sentMessage" : [{
          "adId" : 19.0,
          "detail" : {
            "timestamp" : 1420806952000.0
          }
        }, {
          "adId" : 19.0,
          "detail" : {
            "timestamp" : 1420806969000.0
          }
        }],
      "receivedMessage" : [{
          "adId" : 1.0,
          "detail" : {
            "timestamp" : 1420806955000.0
          }
        }]
    }
  }
}

What I need is to get all the documents where the sentMessage aapId field matches the receivedMessage appId. Imagine that users make use of different apps to message each other through the same server, and I need to find messages that were sent and received by a user through same app througout a certain period of time.

Thanks

Upvotes: 0

Views: 227

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

Yes well your field appears to be called "adId" and not "aapId" as you claim. Not really helping that you didn't provide a sample of data for a positive match either. But oh well...

Mostly using $map and $anyElementTrue operators to traverse array elements for logical comparison.

Not ideal because you are relying on projection from the aggregation framework to decide if elements in the array have matching conditions. You could also code this in JavaScript with $where, but that is probably even worse performance due to the evaluation of JavaScript code and object conversion:

db.collection.aggregate([
    { "$project": {
         "value": 1,
         "matched": {
             "$anyElementTrue": [
                 { "$map": {
                     "input": "$value.data.sentMessage",
                     "as": "sent",
                     "in": {
                         "$anyElementTrue": [
                             { "$map": {
                                 "input": "$value.data.receivedMessage",
                                 "as": "received",
                                 "in": {
                                     "$eq": [ "$$sent.adId", "$$received.adId" ]
                                 }
                             }}
                         ]
                     }
                 }}
             ]
         }
    }},
    { "$match": { "matched": true } }
])

Simple principle. Compare each array element against each other array element and look for the possibility of at least one match. Then the result is true and just return those that meet the conditions.

Upvotes: 1

Related Questions