Question 117686 in Mongodb Question posted in
The official documentation can be found here.

How to "sort" nested object array on MongoDB, so all items with a specific value are the firsts?

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

Tags:

2

Answers


  1. 0

    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'}}}
]);
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search