How to "sort" nested object array on MongoDB, so all items with a specific value are at th-CodePudding

I have this data and I want to sort it by two fields: first by specific address (details.address), for example 'Tel Aviv'. second by regular sort, by details.cost field. here is my data:

[{
    "_id": "123",
    "details": [{
        "_id": "1",
        "address": "Ramat Gan",
        "cost": "50"
    }, {
        "_id": "2",
        "address": "Tel Aviv",
        "cost": "30"
    }]
},
{
    "_id": "456",
    "details": [{
        "_id": "4",
        "address": "Modi'in",
        "cost": "40"
    }, {
        "_id": "5",
        "address": "Tel Aviv",
        "cost": "20"
    }]
}
]

and I want to get this data after the two sorting:

[{
    "_id": "456",
    "details": [{
        "_id": "5",
        "address": "Tel Aviv",
        "cost": "20"
    }, {
        "_id": "4",
        "address": "Modi'in",
        "cost": "40"
    }, {
        "_id": "123",
        "details": [{
            "_id": "2",
            "address": "Tel Aviv",
            "cost": "30"
        }, {
            "_id": "1",
            "address": "Ramat Gan",
            "cost": "50"
        }]
    }]
}]

actually, I want to sort by my specific value address' (in this case - 'Tel Aviv') cost

CodePudding user response：

Pretty straightforward: $unwind then re-$group. When sorting arrays of things across document boundaries you pretty much have no choice but to use $unwind to let $sort work properly.

db.foo.aggregate([
    {$unwind: '$details'}
    ,{$sort: {'details.address':-1,'details.cost':1}}

    // Rebuild the original doc; $push will *preserve* the sorted
    // order of address cost following from the stage above:
    ,{$group: {_id:'$_id', details: {$push: '$details'}}}
]);

CodePudding user response：

If you want both splitting and sorting by cost you can expand @BuzzMoschetti's solution $group part to use $cond:

db.collection.aggregate([
  {$unwind: "$details"},
  {$sort: {"details.cost": 1}},
  {
    $group: {
      _id: "$_id",
      top: {
        $push: {
          $cond: [{$eq: ["$details.address", "Tel Aviv"]}, "$details", "$$REMOVE"]
        }
      },
      bottom: {
        $push: {
          $cond: [{$ne: ["$details.address", "Tel Aviv"]}, "$details", "$$REMOVE"]
        }
      }
    }
  },
  {$project: {details: {$concatArrays: ["$top", "$bottom"]}}}
])

See how it works on the playground example both

In case you to just order by specific address first:

db.collection.aggregate([
  {
    $project: {
      top: {
        $filter: {
          input: "$details",
          as: "item",
          cond: {$eq: ["$$item.address", "Tel Aviv"]}
        }
      },
      bottom: {
        $filter: {
          input: "$details",
          as: "item",
          cond: {$ne: ["$$item.address", "Tel Aviv"]}
        }
      }
    }
  },
  {
    $project: {
      details: {$concatArrays: ["$top", "$bottom"]}
    }
  }
])

See how it works on the playground example top-city