CODEWITHSUNDEEP
mongodb
johhny B
johhny B

Reputation: 1452

MongoDB sorting on children

I have the following documents:

    {'_id':'id1','parentId':"",'count':1}
    {'_id':'id2','parentId':"",'count':12}
    {'_id':'id3','parentId':"",'count':16}
    {'_id':'id4','parentId':"id2",'count':3}
    {'_id':'id5','parentId':"id2",'count':3}
    {'_id':'id6','parentId':"id1",'count':0
{'_id':'id7','parentId':"id1",'count':122}

parentId with a "" field represents a parent node. If the parentId is present this signifies a child node of the parentId given. I need a query that will sort on the 'count' (descending) however I only want it to return a cursor of parentId. However if a child count is the highest this represent the highest value overall and the parent of that child should be returned.

The result of running the query on the above data should give me the documents in the following order: id1, id3 ,id2

I thought about starting an aggregation pipeline. For the first stage I can do a sort on count. However I don't know how i can get the query to return the parentId for a child...

Upvotes: 0

Views: 1108

Answers (1)

HoefMeistert
HoefMeistert

Reputation: 1200

You can do it with the following aggregation:

db.collection.aggregate(
  [
    {
      $project: {
      group_id : { $cond : { if: { $ne: [ "$parentId", "" ] }, then: "$parentId", else: "$_id" }},
      count :1
      }
    },
    {
      $group: {
      _id : "$group_id",
      total_count : { $sum: "$count" }
      }
    },
    {
      $sort: {
      total_count : -1
      }
    }
  ]
);

First i project an extra field "group_id" which is filled with the _id or the parentId depending of the parentId value. The group_id field is used to group and make a total count. Last step is to group on the total_count.

When you are using mongoDb 3.4 you could check out $graphLookup but for now i leave you with the pre 3.4 aggregation ;-)

Upvotes: 1

Related Questions