MongoDb sorting documents with multiple nested arrays

Question

There is a document with nested arrays, just want to sort collection by customerId then productList.productId and then productList.itemList.id in ascending order. MongoDb version is 3.0.14.

I've tried so far this and the query doesn't sort the collection as expected:

db.file_old.find({}, {
        customerId: 1,
        "productList.productId": 1,
        "productList.itemList.id": 1
    })
    .sort({
        customerId: 1,
        productList: 1,
        "productList.itemList": 1
    })

and try aggregate framework also like this:

db.file_old.aggregate([
    {"$unwind": "$productList"} ,
    {"$sort": {"customerId": 1, "productList.productId": 1}}
])

It work fine for two field but if try to adding "productList.itemList.id" doesn't work, like this:

db.file_old.aggregate([
    {"$unwind": "$productList"} ,
    {"$sort": {"customerId": 1, "productList.productId": 1,  "productList.itemList.id": 1}}
])

Collection structure:

{
    "_id" : ObjectId("5f33cc2a1e84082968132324"),
    "customerId" : 2196,
    "productList" : [
        {
            "productId" : 7531,
            "itemList" : [
                {
                    "id" : 144
                },
                {
                    "id" : 145
                }
            ]
        },
        {
            "productId" : 7534,
            "itemList" : [
                {
                    "id" : 1244
                },
                {
                    "id" : 1243
                },
                {
                    "id" : 1245
                },
                {
                    "id" : 1242
                }
            ]
        }
    ]
},{
    "_id" : ObjectId("5f33cc2a1e84082968132326"),
    "customerId" : 2201,
    "productList" : [
        {
            "productId" : 101201,
            "itemList" : [
                {
                    "id" : 863
                },
                {
                    "id" : 865
                },
                {
                    "id" : 862
                }
            ]
        },
        {
            "productId" : 7537,
            "itemList" : [
                {
                    "id" : 982
                },
                {
                    "id" : 1002
                },
                {
                    "id" : 896
                }
            ]
        }
    ]
}

turivishal · Accepted Answer · 2020-08-17T12:55:03.440

0

You can not sort directly array, first requires to unwind(deconstruct) and then sort will apply, lets see step by step,

productList

deconstruct array ($unwind)

itemList

deconstruct array ($unwind)

sort by id ($sort)

re-construct array ($group)

sort by productId ($sort)

re-construct productList ($group)

sort by customerId ($sort)

$unwind deconstruct productList array

db.collection.aggregate([
  { $unwind: "$productList" },

$unwind deconstruct productList.itemList array

  { $unwind: "$productList.itemList" },

$sort by productList.itemList.id ascending order

  { $sort: { "productList.itemList.id": 1 } },

$group by all 3 main level of ids and re-construct itemList array

  {
    $group: {
      _id: {
        _id: "$_id",
        customerId: "$customerId",
        productId: "$productList.productId"
      },
      itemList: { $push: "$productList.itemList" }
    }
  },

$sort by productId ascending order

  { $sort: { "_id.productId": 1 } },

$group by main 2 level of ids and re-construct productList array

  {
    $group: {
      _id: {
        _id: "$_id._id",
        customerId: "$_id.customerId"
      },
      productList: {
        $push: {
          productId: "$_id.productId",
          itemList: "$itemList"
        }
      }
    }
  },

$project to show required fields

  {
    $project: {
      _id: "$_id._id",
      customerId: "$_id.customerId",
      productList: 1
    }
  },

$sort by customerId id

  { $sort: { customerId: 1 } }
])

Playground

edited Aug 17 '20 at 12:55

answered Aug 17 '20 at 12:25

turivishal

34,368
7
36
59

I try your first post, doesn't sort correctly `productList.productId`. – fuat Aug 17 '20 at 12:34
@turivishal is there any way to do sorting without unwinding? I have faced a prblm tht i jave three level nested array where i need to sort – varman Aug 17 '20 at 12:48
@varman i don't know but will check, and i think unwind is expensive for query, might be affect performance and speed, will ask to the community and update you. – turivishal Aug 17 '20 at 12:53
1

Sure. Thank you, unwind is expensive when we use lot of data. Let me know if you get help, i will if i get. – varman Aug 17 '20 at 12:54
@varman Yes, many things are expensive if you use MongoDb with millions of data. – fuat Aug 17 '20 at 12:57
@varman you can use [$function](https://docs.mongodb.com/master/reference/operator/aggregation/function/index.html) to build your own operator, check sort operator [example](https://stackoverflow.com/a/60957218/8987128) from v4.4, or might be there is no option other than unwind – turivishal Aug 17 '20 at 14:34
@fuat, have you solved your problem with this answer? let me know is there any issue with query. – turivishal Aug 17 '20 at 14:36
@turivishal Thank you, i will look this – varman Aug 17 '20 at 15:10

MongoDb sorting documents with multiple nested arrays

1 Answers1