CODEWITHSUNDEEP
mongodb
user1142464
user1142464

Reputation: 520

I need sort nested collections by field and output result sliced and grouped by document field

I have collection.

{
  "cityIsoCode": "LED",
  "flights": [{
    "departureDate": "2019-01-03",
    "fromIsoCode": "MOW",
    "returnDate": "2019-01-09",
    "totalPrice": 829
  },{
    "departureDate": "2019-01-03",
    "fromIsoCode": "MOW",
    "returnDate": "2019-01-04",
    "totalPrice": 467
  }, {
    "departureDate": "2019-01-03",
    "fromIsoCode": "MOW",
    "returnDate": "2019-01-05",
    "totalPrice": 838
  }]
}
  {
    "cityIsoCode": "KZN",
    "flights": [{
      "departureDate": "2019-01-03",
      "fromIsoCode": "MOW",
      "returnDate": "2019-01-04",
      "totalPrice": 518
    }, {
      "departureDate": "2019-01-03",
      "fromIsoCode": "MOW",
      "returnDate": "2019-01-03",
      "totalPrice": 551
    }, {
      "departureDate": "2019-01-03",
      "fromIsoCode": "MOW",
      "returnDate": "2019-01-10",
      "totalPrice": 765
    }]
  }

I need sort nested collection "flights" by field "totalPrice" and output $slice: [1,1] of "flights" grouped by document field "cityIsoCode".

I need to get this result:

{
	"cityIsoCode" : "LED",
	"flights" : [
		{
		  "departureDate": "2019-01-03",
      "fromIsoCode": "MOW",
      "returnDate": "2019-01-09",
      "totalPrice": 829
		}
	]
}
{
	"cityIsoCode" : "KZN",
	"flights" : [
		{
      "departureDate": "2019-01-03",
      "fromIsoCode": "MOW",
      "returnDate": "2019-01-03",
      "totalPrice": 551
    }
	]
}

Can I do it in mongo and how to make right query for it?

Upvotes: 0

Views: 37

Answers (1)

Saravana
Saravana

Reputation: 12817

you can use aggregation pipeline to do this

db.t45.aggregate([
    {$unwind : "$flights"}, 
    {$sort : {"flights.totalPrice":1}}, 
    {$group : {_id : {_id : "$_id", cityIsoCode : "$cityIsoCode"}, flights : {$push : "$flights"}}}, 
    {$project : {_id : "$_id._id", cityIsoCode : "$_id.cityIsoCode", flights : {$slice : ["$flights", 1,1]}}}
]).pretty()

this is with mongo 3.4.10 version

I believe in higher versions we can sort array elements directly, we can avoid the $unwind, $sort and $group with just $sort by array element field and $project the fields required

Upvotes: 1

Related Questions