How to get custom object by user id from aggregate result
I am trying to query mongoDB to fetch data. Here is my doc:
Relations:
{
"_id" : 1,
"from" : "a",
"to" : "b",
"message" : "a to b",
"createdAt" : ISODate("2015-06-06T16:42:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:42:32.789Z")
}
{
"_id" : 2,
"from" : "a",
"to" : "c",
"message" : "a to c",
"createdAt" : ISODate("2015-06-06T16:43:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:43:32.789Z")
}
{
"_id" : 3,
"from" : "b",
"to" : "c",
"message" : "b to c",
"createdAt" : ISODate("2015-06-06T16:44:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:44:32.789Z")
}
{
"_id" : 4,
"from" : "a",
"to" : "c",
"message" : "a to c2",
"createdAt" : ISODate("2015-06-06T16:45:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:45:32.789Z")
}
{
"_id" : 5,
"from" : "b",
"to" : "c",
"message" : "b to c2",
"createdAt" : ISODate("2015-06-06T16:46:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:46:32.789Z")
}
User:
{
"_id" : 'a',
"name" : "q",
"createdAt" : ISODate("2015-06-14T17:20:27.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:30.383Z")
}
{
"_id" : 'b',
"name" : "e",
"createdAt" : ISODate("2015-06-14T17:20:29.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:3.383Z")
}
{
"_id" : 'c',
"name" : "t",
"createdAt" : ISODate("2015-06-14T17:20:28.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:38.383Z")
}
I've tried this:
db.collection.aggregate([
{
"$sort": {
"updatedAt": -1
}
},
{
"$group": {
"_id": {
"to": "$to",
"from": "$from"
},
"id": { "$first": "$_id" },
"message": { "$first": "$message" },
"createdAt": { "$first": "$createdAt" },
"updatedAt": { "$first": "$updatedAt" }
}
},
{
"$project": {
"_id" : 0,
"id": 1,
"from" : "$_id.from",
"to": "$_id.to",
"message": 1,
"createdAt": 1,
"updatedAt": 1
}
}
]);
I have it:
{
"_id" : 1,
"from" : "a",
"to" : "b",
"message" : "a to b",
"createdAt" : ISODate("2015-06-06T16:42:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:42:32.789Z")
}
{
"_id" : 4,
"from" : "a",
"to" : "c",
"message" : "a to c2",
"createdAt" : ISODate("2015-06-06T16:45:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:45:32.789Z")
}
{
"_id" : 5,
"from" : "b",
"to" : "c",
"message" : "b to c2",
"createdAt" : ISODate("2015-06-06T16:46:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:46:32.789Z")
}
Now I want to get one document with a recent combination of . Example:
{
"_id" : 1,
"from" :{
"_id" : 'a',
"name" : "q",
"createdAt" : ISODate("2015-06-14T17:20:27.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:30.383Z")
},
"to" : {
"_id" : 'b',
"name" : "e",
"createdAt" : ISODate("2015-06-14T17:20:29.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:3.383Z")
},
"message" : "a to b",
"createdAt" : ISODate("2015-06-06T16:42:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:42:32.789Z")
}
{
"_id" : 4,
"from" :{
"_id" : 'a',
"name" : "q",
"createdAt" : ISODate("2015-06-14T17:20:27.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:30.383Z")
},
"to" : {
"_id" : 'c',
"name" : "t",
"createdAt" : ISODate("2015-06-14T17:20:28.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:38.383Z")
},
"message" : "a to c2",
"createdAt" : ISODate("2015-06-06T16:45:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:45:32.789Z")
}
{
"_id" : 5,
"from" : {
"_id" : 'b',
"name" : "e",
"createdAt" : ISODate("2015-06-14T17:20:29.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:3.383Z")
},
"to" : {
"_id" : 'c',
"name" : "t",
"createdAt" : ISODate("2015-06-14T17:20:28.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:38.383Z")
},
"message" : "b to c2",
"createdAt" : ISODate("2015-06-06T16:46:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:46:32.789Z")
}
Any help with the code is greatly appreciated.
+3
source to share
1 answer
Since MongoDB does not support joins, you can concatenate two collections into an array of objects using methods to iterate over the cursor and access the documents in both collections, as in the following example: forEach()
aggregate()
var pipeline = [
{
"$sort": {
"updatedAt": -1
}
},
{
"$group": {
"_id": {
"to": "$to",
"from": "$from"
},
"id": { "$first": "$_id" },
"message": { "$first": "$message" },
"createdAt": { "$first": "$createdAt" },
"updatedAt": { "$first": "$updatedAt" }
}
},
{
"$project": {
"_id" : 0,
"id": 1,
"from" : "$_id.from",
"to": "$_id.to",
"message": 1,
"createdAt": 1,
"updatedAt": 1
}
}],
cur = db.collection.aggregate(pipeline),
result = [];
cur.forEach(function (doc){
var toUser = db.user.findOne({"_id": doc.to});
var fromUser = db.user.findOne({"_id": doc.from});
doc.to = toUser;
doc.from = fromUser;
result.push(doc);
})
printjson(result);
Output:
[
{
"id" : 1,
"message" : "a to b",
"createdAt" : ISODate("2015-06-06T16:42:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:42:32.789Z"),
"from" : {
"_id" : "a",
"name" : "q",
"createdAt" : ISODate("2015-06-14T17:20:27.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:30.383Z")
},
"to" : {
"_id" : "b",
"name" : "e",
"createdAt" : ISODate("2015-06-14T17:20:29.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:00Z")
}
},
{
"id" : 4,
"message" : "a to c2",
"createdAt" : ISODate("2015-06-06T16:45:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:45:32.789Z"),
"from" : {
"_id" : "a",
"name" : "q",
"createdAt" : ISODate("2015-06-14T17:20:27.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:30.383Z")
},
"to" : {
"_id" : "c",
"name" : "t",
"createdAt" : ISODate("2015-06-14T17:20:28.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:38.383Z")
}
},
{
"id" : 5,
"message" : "b to c2",
"createdAt" : ISODate("2015-06-06T16:46:32.789Z"),
"updatedAt" : ISODate("2015-06-06T16:46:32.789Z"),
"from" : {
"_id" : "b",
"name" : "e",
"createdAt" : ISODate("2015-06-14T17:20:29.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:00Z")
},
"to" : {
"_id" : "c",
"name" : "t",
"createdAt" : ISODate("2015-06-14T17:20:28.288Z"),
"updatedAt" : ISODate("2015-06-15T14:24:38.383Z")
}
}
]
+2
source to share