How to find match in documents in Mongo and Mongo aggregation?


What you are really asking here is how to make MongoDB return something that is actually quite different from the form in which you store it in your collection. The standard query operations do allow a "limitted" form of "projection", but even as the title on the page shared in that link suggests, this is really only about "limiting" the fields to display in results based on what is present in your document already.

So any form of "alteration" requires some form of aggregation, which with both the aggregate and mapReduce operations allow to "re-shape" the document results into a form that is different from the input. Perhaps also the main thing people miss with the aggregation framework in particular, is that it is not just all about "aggregating", and in fact the "re-shaping" concept is core to it's implementation.

So in order to get results how you want, you can take an approach like this, which should be suitable for most cases:

db.collection.aggregate([
    { "$unwind": "$students" },
    { "$unwind": "$studentDept" },
    { "$group": {
        "_id": "$students.name",
        "tfee": { "$first": "$students.fee" },
        "tdept": {
            "$min": {
                "$cond": [
                    { "$eq": [ 
                        "$students.name", 
                        "$studentDept.name"
                    ]},
                    "$studentDept.dept",
                    false
                ]
            }
        }
    }},
    { "$match": { "tdept": { "$ne": false  } } },
    { "$sort": { "_id": 1 } },
    { "$project": {
        "_id": 0,
        "name": "$_id",
        "fee": "$tfee",
        "dept": "$tdept"
    }}
])

Or alternately just "filter out" the cases where the two "name" fields do not match and then just project the content with the fields you want, if crossing content between documents is not important to you:

db.collection.aggregate([
    { "$unwind": "$students" },
    { "$unwind": "$studentDept" },
    { "$project": {
        "_id": 0,
        "name": "$students.name",
        "fee": "$students.fee",
        "dept": "$studentDept.dept",
        "same": { "$eq": [ "$students.name", "$studentDept.name" ] }
    }},
    { "$match": { "same": true } },
    { "$project": {
        "name": 1,
        "fee": 1,
        "dept": 1
    }}
])

From MongoDB 2.6 and upwards you can even do the same thing "inline" to the document between the two arrays. You still want to reshape that array content in your final output though, but possible done a little faster:

db.collection.aggregate([

  // Compares entries in each array within the document
  { "$project": {
    "students": {
      "$map": {
        "input": "$students",
        "as": "stu",
        "in": {
          "$setDifference": [
            { "$map": {
              "input": "$studentDept",
              "as": "dept",
              "in": {
                "$cond": [
                  { "$eq": [ "$$stu.name", "$$dept.name" ] },
                  {
                    "name": "$$stu.name",
                    "fee": "$$stu.fee",
                    "dept": "$$dept.dept"
                  },
                  false
                ]
              }
            }},
            [false]
          ]
        }
      }
    }
  }},

  // Students is now an array of arrays. So unwind it twice
  { "$unwind": "$students" },
  { "$unwind": "$students" },

  // Rename the fields and exclude
  { "$project": {
    "_id": 0,
    "name": "$students.name",
    "fee":  "$students.fee",
    "dept": "$students.dept"
  }},
])

So where you want to essentially "alter" the structure of the output then you need to use one of the aggregation tools to do. And you can, even if you are not really aggregating anything.



Mongo DB aggregation framework calculate avg documents

For this you just need to know the amount of questions, and the amount of different profiles (uniquely identified with "pid" I presume). With the aggregation framework, you need to do that in two stages:

  • First, you calculate the number of questions per PID
  • Then you calculate the average of questions per PID

You'd do that like this:

Step one:

db.profiler.aggregate( [
    { $group: { _id: '$pid', count: { '$sum': 1 } } },
] );

Which outputs (in my case, with some sample data):

{
    "result" : [
        { "_id" : 2, "count" : 7 },
        { "_id" : 1, "count" : 1 },
        { "_id" : 3, "count" : 3 },
        { "_id" : 4, "count" : 5 }
    ],
    "ok" : 1
}

I have four profiles, respectively with 7, 1, 3 or 5 questions.

Now with this result, we run another group, but in this case we don't really want to group by anything, and thus do we need to set the _id value to null as you see in the second group below:

db.profiler.aggregate( [
    { $group: { _id: '$pid', count: { '$sum': 1 } } },
    { $group: { _id: null, avg: { $avg: '$count' } } }
] );

And then this outputs:

{
    "result" : [
        { "_id" : null, "avg" : 4 }
    ], 
    "ok" : 1
}

Which tells me that I have on average, 4 questions per profile.


Mongo DB Aggregation using match and group aggregate function

I am new to MongoDB, but I would go for the $project and use conditional sentences(please note code not checked, just a "hint"):

{$match: {"paid_currency_type": "real_currency", 
"paid_amount": {$gt: 0}}}
{$group: {_id:"$user_id", curr: 'real_currency', total: {$sum :
'paid_amount'}}
{$project: {_id:'$_id',USD:{{$cond: [{$eq:{'$curr', 'USD']}, '$total',
$multiply[$total,$change.usd.real_currency]}},
                       Yen:{ you know what I mean ;-)},
                       Cur:{}}}

Please note I used an external object $change, which would be containing the changing rate to the currencies. I am new to MongoDB, so I do not know exactly how to add an external variable in your aggregation (maybe is just using them like I did, but not sure, sorry).

If you are running your aggregation for each currency, it should be easier, forget the conditonal operator I wrote and just get the $project idea and show the three currencies (one from data , the other with $multiply for the changing rate).

Hope this helps!


Mongo aggregation with relevance match multiple fields

Using multiple $or clauses with case-insensitive $regex isn't going to be very performant -- particularly if you have a sizeable amount of data to search. MongoDB (as at 2.4) is not able to efficiently use an index for case-insensitive regex (standard indexes are case-sensitive), and matching anywhere within each field will be a costly comparison for large strings. The $or clauses are executed independently, so you will ideally want to have an index on each field (to avoid a full collection scan) and the case-insensitive $regex comparison is still going to be a full index scan.

A much better approach for this use case would be using the text indexes available in MongoDB 2.4+. Text indexes are case-insensitive, include language-based word stemming, can include multiple text fields, and always return results in order ranked by relevance. You can also adjust the relative weights for the fields that are indexed.

It's worth noting that stemming isn't the same as using a regex. If you want to match words, stemming can be helpful in reducing to the common language root (i.e. "running" matches "run").

If you are matching names, you will probably want a fuzzy matching approach based on similarity or character transposition. For a good writeup on several approaches to matching names, see Efficient Techniques for Fuzzy and Partial matching in mongoDB.


In Mongo, how to match documents based with ranges?

According to the final result you want as below:

{
    name: "Acme",
    price: 20,
    finalPrice: 30
}

You can use:

var min = 12, max = 14; 
db.products.aggregate([ {
    $match : {
        name : "Acme",
        minEmployees : {
            $lte : min
        },
        maxEmployees : {
            $gte : max
        }
    }
}, {
    $project : {
        _id : 0,
        name : "$name",
        price : "$price",
        finalPrice : {
            $multiply : [ "$price", "$priceMultiplierEmployees", {
                $subtract : [ min, "$minEmployees" ]
            } ]
        }
    }
} ]);

How to calculate difference between values of different documents using mongo aggregation?

Tough question in principle, but I'm going to stay with the simplified case you present of two documents and base a solution around that. The concepts should abstract, but are more difficult for expanded cases. Possible with the aggregation framework in general:

db.collection.aggregate([
    // Match the documents in a pair
    { "$match": {
        "timeMilliSec": { "$in": [ 1414590255, 1414590245 ] }
    }}

    // Trivial, just keeping an order
    { "$sort": { "timeMilliSec": -1 } },

    // Unwind the arrays
    { "$unwind": "$data" },

    // Group first and last
    { "$group": {
        "_id": "$data.name",
        "firstX": { "$first": "$data.x" },
        "lastX": { "$last": "$data.x" },
        "firstY": { "$first": "$data.y" },
        "lastY": { "$last": "$data.y" }
    }},

    // Difference on the keys
    { "$project": {
        "diff": {
            "$divide": [
                { "$subtract": [ "$firstX", "$lastX" ] },
                { "$subtract": [ "$firstY", "$lastY" ] }
            ]
        }
    }},

    // Not sure you want to take it this far
    { "$group": {
        "_id": null,
        "diffX": { 
            "$min": {
                "$cond": [
                     { "$eq": [ "$_id", "X" ] },
                     "$diff",
                     false
                 ]
            }
        },
        "diffY": { 
            "$min": {
                "$cond": [
                     { "$eq": [ "$_id", "Y" ] },
                     "$diff",
                     false
                 ]
            }
        }
    }}
])

Possibly overblown, not sure of the intent, but the output of this based on the sample would be:

{ 
    "_id" : null, 
    "diffX" : 0.14285714285714285, 
    "diffY" : 0.6 
}

Which matches the calculations.

You can adapt to your case, but the general principle is as shown.

The last "pipeline" stage there is a little "extreme" as all that is done is combine the results into a single document. Otherwise, the "X" and "Y" results are already obtained in two documents in the pipeline. Mostly by the $group operation with $first and $last operations to find the respective elements on the grouping boundary.

The subsequent operations in $project as a pipeline stage performs the required math to determine the distinct results. See the aggregation operators for more details, particularly $divide and $subtract.

Whatever you do you follow this course. Get a "start" and "end" pair on your two keys. Then perform the calculations.


How to find match in documents in Mongo and Mongo aggregation?

What you are really asking here is how to make MongoDB return something that is actually quite different from the form in which you store it in your collection. The standard query operations do allow a "limitted" form of "projection", but even as the title on the page shared in that link suggests, this is really only about "limiting" the fields to display in results based on what is present in your document already.

So any form of "alteration" requires some form of aggregation, which with both the aggregate and mapReduce operations allow to "re-shape" the document results into a form that is different from the input. Perhaps also the main thing people miss with the aggregation framework in particular, is that it is not just all about "aggregating", and in fact the "re-shaping" concept is core to it's implementation.

So in order to get results how you want, you can take an approach like this, which should be suitable for most cases:

db.collection.aggregate([
    { "$unwind": "$students" },
    { "$unwind": "$studentDept" },
    { "$group": {
        "_id": "$students.name",
        "tfee": { "$first": "$students.fee" },
        "tdept": {
            "$min": {
                "$cond": [
                    { "$eq": [ 
                        "$students.name", 
                        "$studentDept.name"
                    ]},
                    "$studentDept.dept",
                    false
                ]
            }
        }
    }},
    { "$match": { "tdept": { "$ne": false  } } },
    { "$sort": { "_id": 1 } },
    { "$project": {
        "_id": 0,
        "name": "$_id",
        "fee": "$tfee",
        "dept": "$tdept"
    }}
])

Or alternately just "filter out" the cases where the two "name" fields do not match and then just project the content with the fields you want, if crossing content between documents is not important to you:

db.collection.aggregate([
    { "$unwind": "$students" },
    { "$unwind": "$studentDept" },
    { "$project": {
        "_id": 0,
        "name": "$students.name",
        "fee": "$students.fee",
        "dept": "$studentDept.dept",
        "same": { "$eq": [ "$students.name", "$studentDept.name" ] }
    }},
    { "$match": { "same": true } },
    { "$project": {
        "name": 1,
        "fee": 1,
        "dept": 1
    }}
])

From MongoDB 2.6 and upwards you can even do the same thing "inline" to the document between the two arrays. You still want to reshape that array content in your final output though, but possible done a little faster:

db.collection.aggregate([

  // Compares entries in each array within the document
  { "$project": {
    "students": {
      "$map": {
        "input": "$students",
        "as": "stu",
        "in": {
          "$setDifference": [
            { "$map": {
              "input": "$studentDept",
              "as": "dept",
              "in": {
                "$cond": [
                  { "$eq": [ "$$stu.name", "$$dept.name" ] },
                  {
                    "name": "$$stu.name",
                    "fee": "$$stu.fee",
                    "dept": "$$dept.dept"
                  },
                  false
                ]
              }
            }},
            [false]
          ]
        }
      }
    }
  }},

  // Students is now an array of arrays. So unwind it twice
  { "$unwind": "$students" },
  { "$unwind": "$students" },

  // Rename the fields and exclude
  { "$project": {
    "_id": 0,
    "name": "$students.name",
    "fee":  "$students.fee",
    "dept": "$students.dept"
  }},
])

So where you want to essentially "alter" the structure of the output then you need to use one of the aggregation tools to do. And you can, even if you are not really aggregating anything.



- Technology - Languages
+ Webmasters
+ Development
+ Development Tools
+ Internet
+ Mobile Programming
+ Linux
+ Unix
+ Apple
+ Ubuntu
+ Mobile & Tablets
+ Databases
+ Android
+ Network & Servers
+ Operating Systems
+ Coding
+ Design Software
+ Web Development
+ Game Development
+ Access
+ Excel
+ Web Design
+ Web Hosting
+ Web Site Reviews
+ Domain Name
+ Information Security
+ Software
+ Computers
+ Electronics
+ Hardware
+ Windows
+ PHP
+ ASP/ASP.Net
+ C/C++/C#
+ VB/VB.Net
+ JAVA
+ Javascript
+ Programming
Privacy Policy - Copyrights Notice - Feedback - Report Violation 2018 © BigHow