How to fix $sort is not working for array inside array in mongoDB?

Question

My document is as follows :

{
    "_id" : ObjectId("622136811b68d9136e48ba4e"),
    "brand_name" : "iPhone",
    "brand_rating" : [
        {
            "cust_name" : "Sham K",
            "rating" : 5
        },
        {
            "cust_name" : "Nil",
            "rating" : 5
        }
    ],
    "models" : [
        {
            "model_name" : "iPhone 7Plus",
            "RAM" : "4GB",
            "ROM" : "64GB",
            "price" : 98000,
            "buyer" : [
                {
                    "cust_name" : "Anu",
                    "rating" : 3
                },
                {
                    "cust_name" : "Kiran",
                    "rating" : 4
                }
            ]
        },
        {
            "model_name" : "iPhone 2",
            "RAM" : "3GB",
            "ROM" : "32GB",
            "price" : 58000,
            "buyer" : [
                {
                    "cust_name" : "Kiran",
                    "rating" : 4
                }
            ]
        }
    ]
}

Question is List all the customers in descending order who bought the iPhone 7plus.

I try this but sorting is not working

db.brand.aggregate({$unwind : "$models"},{$match : {"models.model_name" :"iPhone 7Plus" }}, {$project : {_id : 0, "models.buyer.cust_name" : 1}}, {$sort : {"models.buyer.cust_name" : -1} })

Output :

    { "models" : { "buyer" : [ { "cust_name" : "Anu" }, { "cust_name" : "Kiran" } ] } }

> [5]

What is the easiest way to solve it?

I want to following two queries output in single query for same document : > db.brand.distinct("brand_name") Output : [ "Sony", "Samsung", "iPhone" ] db.brand.aggregate({$addFields : {total_rating : {$sum : "$brand_rating.rating"} }}, {$sort : {total_rating : -1}}, {$limit : 1 }, {$project : {_id : 0, brand_name : 1, total_rating : 1} }) Output : { "brand_name" : "iPhone", "total_rating" : 10 } I want to get both outputs in a single query (Desired Output) > [ "Sony", "Samsung", "iPhone" ] { "brand_name" : "iPhone", > "total_rating" : 10 } — Amol_G
– Amol_G, Commented Mar 4, 2022 at 17:27

YuTing · Accepted Answer · 2022-03-04 00:37:26Z

1

How about $unwind before $sort

db.collection.aggregate({
  $unwind: "$models"
},
{
  $match: {
    "models.model_name": "iPhone 7Plus"
  }
},
{
  $project: {
    _id: 0,
    "models.buyer.cust_name": 1
  }
},
{
  $unwind: "$models.buyer"
},
{
  $sort: {
    "models.buyer.cust_name": -1
  }
})

mongoplayground

answered Mar 4, 2022 at 0:37

YuTing

6,6692 gold badges8 silver badges19 bronze badges

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

1 Comment

Amol_G Over a year ago

I want to following two queries output in single query for same document : > db.brand.distinct("brand_name") Output : [ "Sony", "Samsung", "iPhone" ] db.brand.aggregate({$addFields : {total_rating : {$sum : "$brand_rating.rating"} }}, {$sort : {total_rating : -1}}, {$limit : 1 }, {$project : {_id : 0, brand_name : 1, total_rating : 1} }) Output : { "brand_name" : "iPhone", "total_rating" : 10 } I want to get both outputs in a single query (Desired Output) > [ "Sony", "Samsung", "iPhone" ] { "brand_name" : "iPhone", > "total_rating" : 10 }

Buzz Moschetti · Accepted Answer · 2022-03-04 15:48:08Z

Here is a variation with only 3 stages and the single $unwind is pushed "down" the pipeline as far as possible to minimize both the number of docs and size thereof. The key thing in this variation is using $filter followed by a dot notation expression to get to the data we want without using $unwind too early.

db.foo.aggregate([
    {$project: {
        _id:false,
        cust_name:
        // Explaining this "inside out":                                             
        // 1.  Start by filtering the models array for iphone 7plus.              
        //     This will yield an array of 1 item (or zero).                      
        // 2.  We want to get the cust_name from buyer array.  Use                
        //     $map on the array of 1 to extract the names using the              
        //     dot notation trick buyer.cust_name to create an array              
        //     of just names.                                                     
        // 3.  We have that list -- but it is a list inside the array             
        //     of one, e.g. [ [ name1, name2 ] ] .  So we lift out                
        //     the inner array with $arrayElemAt: [ inner, 0 ].
        //     Note: v>=4.4 you can use $first: { inner } as a convenient
        //     substitute for $arrayElemAt: [ inner, 0 ]                 
            {$arrayElemAt: [
                {$map: {
                    input: {$filter: {
                      input: "$models",
                      cond: {$eq:['$$this.model_name','iPhone 7Plus']}
                    }},
                    in: "$$this.buyer.cust_name"
                }}, 0]
            }
       }}

    // At this stage we have:                                                     
    // { "cust_name" : [ "Anu", "Kiran" ] }                                       
    // Now it's easy:                                                             
    ,{$unwind: '$cust_name'}
    ,{$sort: {'cust_name':-1}}
]);

Collectives™ on Stack Overflow

How to fix $sort is not working for array inside array in mongoDB?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related