2

I have a model which looks like

{
"_id" : ObjectId("5b975b7ffb8361fa0cdacc41"),
"refid" : 1,
"item" : "xyz1",
"qnid" : 1
}
{
"_id" : ObjectId("5b975b7ffb8361fa0cdacc42"),
"refid" : 1,
"item" : "xyz2",
"qnid" : 2
}
{
"_id" : ObjectId("5b975b7ffb8361fa0cdacc43"),
"refid" : 1,
"item" : "xyz3",
"qnid" : 3
}
{
"_id" : ObjectId("5b975b7ffb8361fa0cdacc44"),
"refid" : 4,
"item" : "xyz4",
"qnid" : 4
}
{
"_id" : ObjectId("5b975b7ffb8361fa0cdacc45"),
"refid" : 4,
"item" : "xyz5",
"qnid" : 5
}

here refid is the reference id for the article and qnid is the actually id which holdes the version. i want to fetch all the articles ie refid with recent version data.

i want to get the recent records aggregated by refid

article refid 1 has qnid 3 as the recent one. article refid 4 has qnid 5 as the recent one. ie 

{
"_id" : ObjectId("5b975b7ffb8361fa0cdacc43"),
"refid" : 1,
"item" : "xyz3",
"qnid" : 3
}
{
"_id" : ObjectId("5b975b7ffb8361fa0cdacc45"),
"refid" : 4,
"item" : "xyz5",
"qnid" : 5
}

Please help me how can i do this. I used aggregate function with sort but it is not giving the data

s7vr
  • 73,656
  • 11
  • 106
  • 127
Priyanka DL
  • 47
  • 5

1 Answers1

2

You can use below aggregation in 3.4.

$sort on refid asc and qnid desc.

$group by refid and $first with $$ROOT to access the whole document.

$replaceRoot to promote the data to the top.

db.collection.aggregate([
  {"$sort":{"refid":1,"qnid":-1}},
  {"$group":{
    "_id":"$refid",
    "data":{"$first":"$$ROOT"}
  }},
  {"$replaceRoot":{"newRoot":"$data"}}
])
s7vr
  • 73,656
  • 11
  • 106
  • 127
  • Thanks for your answer.. but it is not working for me. im using mongo version v3.6.5 . but im getting [thread1] Error: command failed: { "ok" : 0, "errmsg" : "Unrecognized pipeline stage name: 'replaceRoot'", "code" : 40324, "codeName" : "Location40324" } : aggregate failed : – Priyanka DL Sep 12 '18 at 10:23
  • 1
    @user2705938 & veeram `replaceRoot` should be `$replaceRoot` – Ashh Sep 12 '18 at 11:30
  • 1
    @AnthonyWinzlet ah. Thank you!. Updated. – s7vr Sep 12 '18 at 11:31
  • it works like the way i wanted.. thank you @Veeram and Anthony – Priyanka DL Sep 12 '18 at 11:43
  • facing another problem in this. if i want to sort by createdAt along with refid and qnid, sort by createdAt doesnot work, as it is already sorted by refid and qnid. how do i sort the record with all 3? i tried {"$sort":{"refid":1,"qnid":-1,"createdAt":-1}}, and at the end .sort({"createdAt":-1}) . How can i sort by date also? – Priyanka DL Jan 21 '19 at 08:24