w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
MongoDB - Aggregation, group by an array value
Unfortunately, that isn't possible using aggregation with your schema. The problem is that aggregation is meant to operate over values in an array that are being selected by the $group clause and those elements have all the data needed. Your setup separates what you want to group by and what you want to sum. You could use a mapReduce job to do what you want with your schema. http://docs.mongodb.org/manual/core/map-reduce/ should be able to get you started. Let me know if you have any other questions. Best, Charlie

Categories : Mongodb

MongoDB Aggregation $group and categorise
Damo, one thing that you must keep in mind is that when you want to group by a value, you probably have to use $cond operator. db.esbtrans.aggregate({ $group : { _id : "$messageflow", errors : { $sum : { $cond : [ { $eq : ["$status", "ERR"] } ,1,0] } }, successes : { $sum : { $cond : [ { $eq : ["$status", "OK"] } ,1,0] } }, } }) Explaining: I group by messageflow because this field is your basic axis. Then to count the number of erros and successes, I use the $sum operator in combination with $cond and $eq. It just compares if status is ERR or OK and sum properly.

Categories : Mongodb

MongoDB double $group aggregation
Try this pipeline: [ {$unwind:"$results"}, {$match: {"results.discipline":{$in:["football", "basketball"]}}}, {$group{_id:{player_id:"$player_id",league_id:"$league_id"}, 'average':{$avg:"$results.score"}}} ] it works for me with your doc: { "result" : [ { "_id" : { "player_id" : 0, "league_id" : 2 }, "average" : 23.195 } ], "ok" : 1 } UPD. If you want to group again, by league_id: [{$unwind:"$results"}, {$match: {"results.discipline":{$in:["football", "basketball"]}}}, {$group:{_id:{player_id:"$player_id",league_id:"$league_id"}, 'average':{$avg:"$results.score"} }}, {$group:{_id:"$_id.league_id", 'average':{$avg:"$average"} }} ] { "result" : [ { "_id" : 2, "average" : 23.195 } ], "ok" : 1 }

Categories : Mongodb

Mongodb Aggregation $group, $sum and $sort
The solution was: dir.aggregate( [ { $group: {_id: {fecha:"$date", hora: "$start"}, llamadas :{$sum:"$total_calls"}, answer :{ $sum:"$answer_calls"}, abandoned: {$sum:"$abandoned_calls"}, mail: {$sum:"$voicemail_calls"} } }, { $sort: {'_id.fecha':1 , '_id.hora':1} } ] ) Thank you again to Sammaye and JohnnyHK

Categories : Mongodb

Mongodb Aggregation framework group and sort
You can do this: db.collection.aggregate( {$sort:{"time":1}}, { $group: { _id: "$sessionId", messages: { "$push": {message: "$msg", time: "$time"} } } } ) This will sort the collection based on time then group by session id. Each session ID group will have an array of sub-documents which contain the message and time of the message. By sorting then pushing the messages will be ordered by time in your messages array.

Categories : Mongodb

Use mongodb aggregation framework to group by length of array
Ok, got it! Here we go. The aggregation pipeline is basically that: { $unwind: "$saved_things" }, { $group: { _id: "$_id", size: { $sum: 1 } } }, { $group: { _id: "$size", frequency: { $sum: 1 } } }, { $project: { size: "$_id", frequency: 1, _id: 0 } } Unwind saved_things array, then group by document _id and count it, thus we can achieve the array size. Now is easy, group by size and count the frequency. Use project to rename _id field to size.

Categories : Mongodb

Spring Data MongoDB - Aggregation Framework - invalid reference in group Operation
The problem is not PDch or pd.PDch. For some reason the code is not using the Aggregation.DEFAULT_CONTEXT to get the field reference. This is happens when ever there is more than one field. Code works correctly if you only have one group field.

Categories : Mongodb

mongoDB - aggregation on aggregation?
Here's how to do it. The key is that (a) you need to do $group twice and (b) you need to first $group by the thing you want to sub-total and then $group to get totals. db.records.aggregate( {$group: {_id : {d:"$device",c:"$carrier"}, subtotal:{$sum:1}} }, {$group: {_id:"$_id.c", devices:{$push:{device:"$_id.d", subtotal:"$subtotal"}}, total:{$sum:"$subtotal"}} } )

Categories : Mongodb

Group and take top K of each group with aggregation framwork
The first two steps are easy enough: X = 1; Y = 3 db.collection.aggregate( [ { $match: { Group: { $gte: X, $lt: Y } } }, { $group: { _id: '$Group' } } ] ); With the above dataset, that gives you: { "result" : [ { "_id" : 2 }, { "_id" : 1 } ], "ok" : 1 } In order to be able to pick the K docs with the largest value, you need to modify your group to include each document and the max value for that group, and then we unwind so that we can sort on group and value (DESC): db.collection.aggregate( [ { $match: { Group: { $gte: X, $lt: Y } } }, { $group: { _id: '$Group', docs: { $push: { _id: '$_id', Group: '$Group', Value: '$Value' } } } }, { $unwind: '$docs' }, { $sort: { 'docs.Group': 1, 'docs.Value': -1 } } ] ); From now on, we're struck t

Categories : Mongodb

mongodb aggregation addToSet with sum
I got it to work with this pipeline. var unwind = { "$unwind" : "$ids" }; var group = { "$group" : { "_id" : { "account" : "$account", "id" : "$ids.id" }, "idSalesAmount" : { "$sum" : "$ids.salesAmount" }, "idSalesCount" : { "$sum" : "$ids.salesCount" } }}; var group2 = { "$group" : { "_id" : "$_id.account", "ids" : { "$addToSet" : { "id" : "$_id.id", "salesAmount" : "$idSalesAmount", "saleCount" : "$idSalesCount" } } }}; var project = { "$project" : { "account" : "$_id", "ids" : 1, "_id" : 0 } }; db.mytest.aggregate([unwind, group, group2, project])

Categories : Mongodb

MongoDB Aggregation Framework
Your schema as designed would make using anything but a MapReduce difficult as you've used the keys of the object as values. So, I adjusted your schema to better match with MongoDB's capabilities (in JSON format as well for this example): { '_id' : 'star_wars', 'count' : 1234, 'spellings' : [ { spelling: 'Star wars', total: 10}, { spelling: 'Star Wars', total : 15}, { spelling: 'sTaR WaRs', total : 5} ] } Note that it's now an array of objects with a specific key name, spelling, and a value for the total (I didn't know what that number actually represented, so I've called it total in my examples). On to the aggregation: db.so.aggregate([ { $unwind: '$spellings' }, { $project: { 'spelling' : '$spellings.spelling', 'total': '$spellings.tot

Categories : Mongodb

MongoDB Using Map Reduce against Aggregation
Take a look to this. The Aggregation FW results are stored in a single document so are limited to 16 MB: this might be not suitable for some scenarios. With MapReduce there are several output types available including a new entire collection so it doesn't have space limits. Generally, MapReduce is better when you have to work with large data sets (may be the entire collection). Furthermore, it gives much more flexibility (you write your own aggregation logic) instead of being restricted to some pipeline commands.

Categories : Mongodb

is it possible to use "$where" in mongodb aggregation functions
MongoDB doesn't support $where in aggregation pipeline and hope this will never happen, because JavaScript slows things down. Never the less, you still have options: 1) Мaintain additional field(e.g. app_name_len) than will store app_name length and query it, when needed. 2) You can try extremely slow MapReduce framework, where you allowed to write aggregations with JavaScript.

Categories : Mongodb

State-dependent aggregation in MongoDB
I'm going to answer my own question with the solution I ended implementing. I realized that I was really interested in the previous state of each document. In my case documents are inserted in large batches in temporal order. So, I simply created a state_prev field and a delta field (the difference between sequential documents' timestamp values). { timestamp: Number, state: Number, state_prev: Number, delta: Number } I'm now able to $sum the new delta field and $group by the state_prev field to achieve my desired aggregate computation.

Categories : Mongodb

Absolute value with MongoDB aggregation framework
It's not directly available, but you can do it using a $cond operator and a $subtract within a $project like this (as a JavaScript object): { $project: { amount: { $cond: [ { $lt: ['$amount', 0] }, { $subtract: [0, '$amount'] }, '$amount' ] }}} So if amount < 0, then 0 - amount is used, otherwise amount is used directly. UPDATE As of the 3.2 release of MongoDB, you can use the new $abs aggregation expression operator to do this directly: { $project: { amount: { $abs: '$amount' } }

Categories : Mongodb

Array de-aggregation with repetition in mongodb
You should be able to do this with a simple $unwind For your example above you can use: db.current.aggregate({$unwind: "$longcollection"}) This will give you a result like this: { result: : [ { "_id" : ObjectId(...), "name": xxxx, "othervar": yyyyy, "longcollection" : { "first": 1, "second":2 } }, { "_id" : ObjectId(...), "name": yyyy, "othervar": zzzz, "longcollection" : { "first": 3, "second":4 } }], "ok" : 1 } To stop the duplicate _id message seen in the comment you should be able to use: db.current.aggregate({$project : {_id: 0, name: 1, othervar:

Categories : Mongodb

MongoDB - Aggregation Framework, PHP and averages
As Asya said, the aggregation framework isn't usable for the last part of your problem (averaging gaps in "hits" between documents in the pipeline). Map/reduce also doesn't seem well-suited to this task, since you need to process the documents serially (and in a sorted order) for this computation and MR emphasizes parallel processing. Given that the aggregation framework does process documents in a sorted order, I was brainstorming yesterday about how it might support your use case. If $group exposed access to its accumulator values during the projection (in addition to the document being processed), we might be able to use $push to collect previous values in a projected array and then inspect them during a projection to compute these "hit" gaps. Alternatively, if there was some facility

Categories : PHP

MongoDB Aggregation count over a relation
You don't say how orders relate to buildings in your schema but if an order has a building id or name it references, just group by that: db.orders.aggregate( { $group : { _id: "$buildingId", sum: {$sum:1} } }, /* $sort by sum:-1, $limit:10 like you already have */ )

Categories : Mongodb

Mongodb query help: aggregation within json
You might want to try: db.coll.aggregate( [ { $match: { Date: /20130202/ } }, { $group: { _id: null, sport: { $sum: "$category.sport" }, national: { $sum: "$category.national" }, international: { $sum: "$category.international" }, finance: { $sum: "$category.finance" }, others: { $sum: "$category.others" }, tech: { $sum: "$category.tech" }, Music: { $sum: "$category.Music" } } } ] )

Categories : Mongodb

MongoDB Aggregation Multiple Keys
mapReduce can be used to solve the problem. 1) define the following map function var mapFunction = function() { var key = this.department; var nb_match_bar2 = 0; var status_exist = 0; var status_absent = 0; if( this.status=="exist" ){ status_exist = 1; }else{ status_absent= 1; } var value = { department: this.department, statuses:{ exist: status_exist, absent: status_absent } }; emit( key, value ); }; 2) define the reduce function var reduceFunction = function(key, values) { var reducedObject = { department: key, statuses: { exist: 0, absent:0 } }; values.forEach( function(value) { reducedObject.statuses.exist += value.statuses.exist; reducedObject.statuses.absent += value.statuses.absent; }

Categories : Mongodb

Mongodb Aggregation Framework and timestamp
Here is a way you can do it by generating the aggregation pipeline programmatically: numberOfMonths=24; /* number of months you want to go back from today's */ now=new Date(); year=now.getFullYear(); mo=now.getMonth(); months=[]; for (i=0;i<numberOfMonths;i++) { m1=mo-i+1; m2=m1-1; d = new Date(year,m1,1); d2=new Date(year,m2,1); from= d2.getTime()/1000; to= d.getTime()/1000; dt={from:from, to:to, month:d2}; months.push(dt); } prev="$nothing"; cond={}; months.forEach(function(m) { cond={$cond: [{$and :[ {$gte:["$_id",m.from]}, {$lt:["$_id",m.to]} ]}, m.month, prev]}; prev=cond; } ); /* now you can use "cond" variable in your pipeline to generate month */ db.collection.aggregate( { $project: { month: cond , value:1 } },

Categories : Mongodb

MongoDB aggregation framework match OR
$match: { $or: [{ author: 'dave' }, { author: 'john' }] } Like so, since the $match operator just takes what you would normally put into the find() function

Categories : Mongodb

Mongodb aggregation not working with mongoose
Your query seems to be formatted correctly, I think you've just projected "contacts" when you should have projected "list". I tried to format my data like yours, and the following queries worked for me. In the shell: db.accounts.aggregate( { $unwind:"$contacts" }, { $group: { _id: '$_id', list: { $push:'$contacts.contactId' } } }, { $project: { _id: 0, list: 1 }} ) or, using the mongoose framework, Account.aggregate( { $unwind:"$contacts" }, { $group: { _id: '$_id', list: { $push:'$contacts.contactId' }}}, { $project: { _id: 0, list: 1 }}, function (err, res) { if (err) //handle error; console.log(res); } ); Since you've tried to suppress the "_id" field in the final output o

Categories : Mongodb

How to do Mongodb aggregation arithmetic operator in Java?
Note that the value of $multiply operator should be an array not an object. So, in Java the code will be: BasicDBList args = new BasicDBList(); args.add(myField); args.add(0); new BasicDBObject("$multiply", args)

Categories : Java

MongoDB Aggregation: How do I recombine a date using $project?
Assuming that, as you are grouping documents by year, month and day, hours and minutes are useless, you can use one of those operators to get a date sample: $first, $last, $min or $max. Sentiment.aggregate([ { $match: { 'content.term' : term_id } }, { $group: { _id: { year : { $year : '$created_at' }, month : { $month : '$created_at' }, dayOfMonth : { $dayOfMonth : '$created_at' }, }, dt_sample : { $first : '$created_at' }, sum : { $sum : '$score'}, count : { $sum : 1 } }, }, { $project: { _id : 0, date : '$dt_sample', sum : 1, count : 1, avg : { $di

Categories : Mongodb

How to count Booleans in MongoDB with aggregation framework
The $project phase is your friend in the pipeline by allowing you to create new fields which have different types and values than original fields. Consider this projection which uses $cond to use one value when something is true and another one when it's false: { $project : { numWhoOwnHome : { $cond : [ "$OwnsAHome", 1, 0 ] } } } If you now do a $group with {$sum : "$numWhoOwnHome"} your result will be the number of people who had OwnsAHome set to true.

Categories : Mongodb

How to add a new field in aggregation in projection with blank value in mongodb
The syntax fieldname:1 means "pass through this field as is. You want to have a literal 1 value - the simplest way is to create an expression that will return 1. I suggest: dpv:{$add:[1]}

Categories : Node Js

Array intersection in mongodb with Aggregation framework
what about this (in the mongo shell)? Simply translate to mongoose db.ss.aggregate([ {$unwind: '$params'}, {$match: {params: {$in: [1,20,30,4,7]} } }, {$group: {_id: {_id:"$_id", age: "$age"}, nb: {"$sum":1} } }, {$sort: {nb:-1}}, {$limit:5}, {$project: {_id:"$_id._id", age:"$_id.age", nb: "$nb"} }, {$sort:{age:1}} ]) The first stage $unwind break up the array field so that you have for each _id, a number of documents equal to the number of elt in params, each with a single value of the array params. $match select the document corresponding to what we want. $group group them back using the _id and the age as key and count the number of doc in each group; this corresponds exactly to the number of element in the intersection. $limit take the top five. $project and $s

Categories : Node Js

MongoDB aggregation of large amounts of data
This projection is not a big deal, it has minor impact on whole execution complexity. You can make simple tests with and without this step to find concrete numbers for your case, but, as I said, it just one additional step for Aggregation framework. If you grouping by date, this post might be helpful

Categories : Performance

Is there a workaround to allow using a regex in the Mongodb aggregation pipeline
This question seems to come many times with no solution. There are two possible solutions that I know: solution 1- using mapReduce. mapReduce is the general form of aggregation that let user do anything imaginable and programmable. following is the mongo shell solution using mapReduce We consider the following 'st' collection. db.st.find() { "_id" : ObjectId("51d6d23b945770d6de5883f1"), "foo" : "foo1", "bar" : "bar1" } { "_id" : ObjectId("51d6d249945770d6de5883f2"), "foo" : "foo2", "bar" : "bar2" } { "_id" : ObjectId("51d6d25d945770d6de5883f3"), "foo" : "foo2", "bar" : "bar22" } { "_id" : ObjectId("51d6d28b945770d6de5883f4"), "foo" : "foo2", "bar" : "bar3" } { "_id" : ObjectId("51d6daf6945770d6de5883f5"), "foo" : "foo3", "bar" : "bar3" } { "_id" : ObjectId("51d6db03945770d6de5883f6"

Categories : Regex

How to match by 'undefined' value in MongoDB Aggregation Framework?
If you want to filter out documents that have some fields missing, use the $exists operator. This works on my machine : > db.test.drop() true > db.test.insert( {'Hello':'World!', 'myField':42}) > db.test.insert( {'Hello again':'World!'}) > db.test.aggregate({'$match':{ 'myField':{'$exists':false} }}) { "result" : [ { "_id" : ObjectId("51b9cd2a6c6a334430ec0c98"), "Hello again" : "World!" } ], "ok" : 1 } The document that has myField present does not show in the results.

Categories : Mongodb

How to overcome the limitations with mongoDB aggregation framework
1) Saving aggregated values directly to some collection(like with MapReduce) will released in future versions, so first solution is just wait for a while :) 2) If you hit 2-nd or 3-rd limitation may you should redesign your data scheme and/or aggregation pipeline. If you working with large time series, you can reduce number of aggregated docs and do aggregation in several steps (like MapReduce do). I can't say more concretely, because I don't know your data/use cases(give me a comment). 3) You can choose different framework. If you familiar with MapReduce concept, you can try Hadoop(it can use MongoDB as data source). I don't have experience with MongoDB-Hadoop integration, but I mast warn you NOT to use Mongo's MapReduce -- it sucks hard on large datasets. 4) You can do aggregation

Categories : Mongodb

Mongodb's Aggregation Framework with Subset and Scala
Subset does not provide methods to perform queries to MongoDB, its only concern is about Mongo Java Driver’s method parameters including documents and their fields. So once you have built the aggregation query - you can now run it, the val query should be a AggregationOutput result - which you can call results() to get the actual aggregation results. See using the aggregation framework with the java driver for more information.

Categories : Mongodb

MongoDB geoNear Aggregation - Order of Operations
Here's how $geoNear works: It gets a cursor to documents whose coordinates satisfy the maxDistance requirement. It then iterates over the cursor and for each document checks if it matches the query requirement. If it doesn't, it skips it and moves to the next document. It does this until it finds limit-many documents or the end of the cursor. Note that this is the limit argument to the $geoNear command, not the $limit operation specified later in the aggregation pipeline. The default limit is 100, so if you don't specify limit you are getting the first 100 documents that match query and whose coordinates satisfy maxDistance, sorting those 100 documents by created_at, and then taking the first 5. When you specify limit:100000, you are getting the first 100000 documents that match query and

Categories : Mongodb

MongoDB Aggregation: Counting distinct fields
I figured this out by using the $addToSet and $unwind operators. Mongodb Aggregation count array/set size db.collection.aggregate([ { $group: { _id: { account: '$account' }, vendors: { $addToSet: '$vendor'} } }, { $unwind:"$vendors" }, { $group: { _id: "$_id", vendorCount: { $sum:1} } } ]); Hope it helps someone

Categories : Mongodb

Group aggregation and descriptive columns
If the columns are the same within a group, just include them in the group by clause: select t.a, t.b, t.c,count(*), t.x, t.y, t.z, .... from t group by t.a, t.b, t.c, t.x, t.y, t.z If you want a random row with the count, then use window functions: select t.* from (select t.*, count(*) over (partition by a, b, c) as cnt, row_number() over (partition by a, b, c order by (select NULL)) as seqnum from t ) t where seqnum = 1 The order by (select NULL) is used in SQL Server. I'm not sure if it will work in Netezza. Any expression will work for the order by.

Categories : SQL

mongodb aggregation framework groupby multiple fields
the Map-Reduce may suit for you. eg: map = function (){ emit(this.topic+this.date, 1); } reduce = function (id, vals){ return Array.sum(vals); } db.coll.mapReduce(map, reduce, {out:'results'}); http://docs.mongodb.org/manual/tutorial/map-reduce-examples/

Categories : Mongodb

How to order MongoDB Aggregation with match, sort, and limit
Put the $sort before the $group, otherwise MongoDB can't use the index to help with sorting. However, in your query it looks like you want to query for a relatively small number of user_ids compared to the total size of your group_members collection. So I recommend an index on user_id only. In that case MongoDB will have to sort your results in memory by last_post_at, but this is worthwhile in exchange for using an index for the initial lookup by user_id.

Categories : Mongodb

Use MongoDB aggregation to find set intersection of two sets within the same document
So here is a solution not using the aggregation framework. This uses the $where operator and javascript. This feels much more clunky to me, but it seems to work so I wanted to put it out there if anyone else comes across this question. db.houses.find({'$where': function() { var ownSet = {}; var useSet = {}; for (var i=0;i<obj.uses.length;i++){ useSet[obj.uses[i].name] = true; } for (var i=0;i<obj.rooms.length;i++){ var room = obj.rooms[i]; for (var j=0;j<room.owns.length;j++){ ownSet[room.owns[j].name] = true; } } for (var prop in ownSet) { if (ownSet.hasOwnProperty(prop)) { if (!useSet[prop]){ return tru

Categories : Mongodb

Get Size of Array Intersection in MongoDB Aggregation Framework
If I understand your question, you have data something like the following: db.users.insert({_id: 100, likes: [ 'pina coladas', 'long walks on the beach', 'getting caught in the rain' ]}) db.users.insert({_id: 101, likes: [ 'cheese', 'bowling', 'pina coladas' ]}) db.users.insert({_id: 102, likes: [ 'pina coladas', 'long walks on the beach' ]}) db.users.insert({_id: 103, likes: [ 'getting caught in the rain', 'bowling' ]}) db.users.insert({_id: 104, likes: [ 'pina coladas', 'long walks on the beach', 'getting caught in the rain' ]}) and you wish to compute for a given user how many matching features ('likes' in this example) they have with other users? The following aggregation pipeline will accomplish this: user = 100 user_likes = db.u

Categories : Mongodb



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.