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

Question

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

db.<collectionName>.insertMany( [{ "_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" }] } ]) — Hanna
– Hanna, Commented Jun 7, 2022 at 13:44

nimrod serok · Accepted Answer · 2022-06-07 15:53:34Z

1

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

edited Jun 7, 2022 at 15:53

answered Jun 7, 2022 at 15:19

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Buzz Moschetti Over a year ago

Clever use of $$REMOVE here to "not push" something.

Buzz Moschetti · Accepted Answer · 2022-06-07 14:00:50Z

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'}}}
]);

answered Jun 7, 2022 at 14:00

Buzz Moschetti

7,6433 gold badges27 silver badges41 bronze badges

3 Comments

Hanna Over a year ago

Thanks Buzz Moschetti. but I don't want to sort my address ascending or descending. I only want to top specific value for address, for example, I want to see all the records of 'Tel Aviv's at the top of their object, as like as the example. Do you know hoe can I do it?

Buzz Moschetti Over a year ago

I am confused; the answer matches your indicated target output. Can you edit the output in the question to make this "top specific value for address" more clear? What is a "top specific value"?

Hanna Over a year ago

Is it more clear? I want to see all the objects with the specific value field as the firsts in their array.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related