How to sort by "connected" fields?

user_2822406 0
Viewed 116

I'm not sure how to phrase it, but I'd like to sort the following data in a way that the child messages are immediately below their parents. 

{ "_id" : "oI", "msg" : "Hello", "ts" : ISODate("2019-09-20T01:01:43.749Z") },
{ "_id" : "z", "msg" : "can you hear me?", "ts" : ISODate("2019-09-20T01:01:46.569Z"), "tcount" : 3 },
{ "_id" : "wG", "msg" : "Undsen bichver 2", "ts" : ISODate("2019-09-20T02:00:58.254Z") },
{ "_id" : "BF", "msg" : "Undsen bichver 3", "ts" : ISODate("2019-09-20T02:01:06.784Z") },
{ "_id" : "Jq", "msg" : "Undsen bichver 4", "ts" : ISODate("2019-09-20T02:01:11.785Z") },
{ "_id" : "oP", "parentId" : "z", "msg" : "Test 1", "ts" : ISODate("2019-09-20T01:02:06.860Z") },
{ "_id" : "Mq", "parentId" : "z", "msg" : "Test 2", "ts" : ISODate("2019-09-20T01:05:37.173Z") },
{ "_id" : "Lq", "parentId" : "z", "msg" : "Test 3", "ts" : ISODate("2019-09-20T02:01:24.370Z") }

The sort I used to display the above result is this: 

.sort({ "parentId": 1, "ts": 1 })

The result I want is this: 

{ "_id" : "oI", "msg" : "Hello", "ts" : ISODate("2019-09-20T01:01:43.749Z") },
{ "_id" : "z", "msg" : "can you hear me?", "ts" : ISODate("2019-09-20T01:01:46.569Z"), "tcount" : 3 },
{ "_id" : "oP", "parentId" : "z", "msg" : "Test 1", "ts" : ISODate("2019-09-20T01:02:06.860Z") },
{ "_id" : "Mq", "parentId" : "z", "msg" : "Test 2", "ts" : ISODate("2019-09-20T01:05:37.173Z") },
{ "_id" : "Lq", "parentId" : "z", "msg" : "Test 3", "ts" : ISODate("2019-09-20T02:01:24.370Z") },
{ "_id" : "wG", "msg" : "Undsen bichver 2", "ts" : ISODate("2019-09-20T02:00:58.254Z") },
{ "_id" : "BF", "msg" : "Undsen bichver 3", "ts" : ISODate("2019-09-20T02:01:06.784Z") },
{ "_id" : "Jq", "msg" : "Undsen bichver 4", "ts" : ISODate("2019-09-20T02:01:11.785Z") }

Note the records with "parentId" : "z" are immediately below its parent ("_id" : "z")

Is it possible to do something like this?

aggregation-framework mongodb-query mongodb
2 Answers

Use $addFields and $ifNull to create new field you can then sort by:

db.collection.aggregate([
    {
        $addFields: {
            sortBy: { $ifNull: [ "$parentId", "$_id" ] }
        }
    },
    {
        $sort: {
            sortBy: -1, parentId: 1
        }
    }
])

Mongo Playground

user_62389770

I have managed to achieve your desired sorting with the following;

db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      localField: "parentId",
      foreignField: "_id",
      as: "parent"
    }
  },
  {
    $addFields: {
      fake_ts: {
        $cond: {
          if: { $eq: [ { $size: "$parent" }, 1 ] },
          then: "$parent.ts",
          else: "$ts"
        }
      }
    }
  },
  {
    $sort: { 
      fake_ts: 1,
      ts: 1
    }
  },
  {
    $project: {
      fake_ts: 0,
      parent: 0
    }
  }
])

Where I first join with itself to get parent within each child document, then I create a dummy field (idea from @mickl), and use parent's ts for child documents. Then I just $sort & $project out temp fields.

Sorting is done with first fake_ts, then with ts, so that we will get the child documents always under the parent, but they will be sorted with their own ts between themselves.

The result will be;

[
  {
    "_id": "oI",
    "msg": "Hello",
    "ts": ISODate("2019-09-20T01:01:43.749Z")
  },
  {
    "_id": "z",
    "msg": "can you hear me?",
    "tcount": 3,
    "ts": ISODate("2019-09-20T01:01:46.569Z")
  },
  {
    "_id": "oP",
    "msg": "Test 1",
    "parentId": "z",
    "ts": ISODate("2019-09-20T01:02:06.86Z")
  },
  {
    "_id": "Mq",
    "msg": "Test 2",
    "parentId": "z",
    "ts": ISODate("2019-09-20T01:05:37.173Z")
  },
  {
    "_id": "Lq",
    "msg": "Test 3",
    "parentId": "z",
    "ts": ISODate("2019-09-20T02:01:24.37Z")
  },
  {
    "_id": "wG",
    "msg": "Undsen bichver 2",
    "ts": ISODate("2019-09-20T02:00:58.254Z")
  },
  {
    "_id": "BF",
    "msg": "Undsen bichver 3",
    "ts": ISODate("2019-09-20T02:01:06.784Z")
  },
  {
    "_id": "Jq",
    "msg": "Undsen bichver 4",
    "ts": ISODate("2019-09-20T02:01:11.785Z")
  }
]

Check the code interactively on Mongoplayground

user_36410670
Related