Use $size with $sort in array and sub array

Question

Here's the structure part of my collection:

_id: ObjectId("W"),
names: [
    {
        number: 1,
        subnames: [ { id: "X", day: 1 }, { id: "Y", day: 10 }, { id: "Z", day: 2 } ],
        list: ["A","B","C"],
        day: 1
    },
    {
        number: 2,
        day: 5
    },
    {
        number: 3,
        subnames: [ { id: "X", day: 8 }, { id: "Z", day: 5 } ],
        list: ["A","C"],
        day: 2
    },
    ...
],
...

I use this request:

db.publication.aggregate( [ { $match: { _id: ObjectId("W") } }, { $group: { _id: "$_id", SizeName: { $first: { $size: { $ifNull: [ "$names", [] ] } } }, names: { $first: "$names" } } }, { $unwind: "$names" }, { $sort: { "names.day": 1 } }, { $group: { _id: "$_id", SzNames: { $sum: 1 }, names: { $push: { number: "$names.number", subnames: "$names.subnames", list: "$names.list", SizeList: { $size: { $ifNull: [ "$names.list", [] ] } } } } } } ] );

but I would now use $sort for my names array AND my subnames array to obtain this result (subnames may not exist) :

_id: ObjectId("W"),
names: [
    {
        number: 2,
        SizeList: 0,
        day: 5
    },
    {
        number: 3,
        subnames: [ { id: "Z", day: 5 }, { id: "X", day: 8 } ],
        list: ["A","C"],
        SizeList: 2,
        day: 2
    },
    {
        number: 1,
        subnames: [ { id: "X", day: 1 }, { id: "Z", day: 2 }, { id: "Y", day: 10 } ],
        list: ["A","B","C"],
        SizeList: 3,
        day: 1
    }
    ...
],
...

Can you help me ?

Okey sorry, i just see it

John S
– John S

2015-01-14 12:29:05 +00:00
Commented Jan 14, 2015 at 12:29 — John S
– John S, Commented Jan 14, 2015 at 12:29

Neil Lunn · Accepted Answer · 2015-01-14 03:53:57Z

You can do this, but with great difficulty. I for one would gladly vote for an inline version of $sort along the lines of the $map operator. That would makes things so much easier.

For now though you need to de-construct and re-build the arrays after sorting. And you have to be very careful about this. Hence make false arrays with a single entry before processing $unwind:

db.publication.aggregate([
    { "$project": {
        "SizeNames": { 
            "$size": { 
                "$ifNull": [ "$names", [] ]
             }
        },
        "names": { "$ifNull": [{ "$map": {
            "input": "$names",
            "as": "el",
            "in": {
                "SizeList": { 
                    "$size": { 
                        "$ifNull": [ "$$el.list", [] ]
                     }
                 },
                "SizeSubnames": {
                    "$size": { 
                        "$ifNull": [ "$$el.subnames", [] ]
                     }
                },
                "number": "$$el.number",
                "day": "$$el.day",
                "subnames": { "$ifNull": [ "$$el.subnames", [0] ] },
                "list": "$$el.list"
            }
        }}, [0] ] }
    }},
    { "$unwind": "$names" },
    { "$unwind": "$names.subnames" },
    { "$sort": { "_id": 1, "names.subnames.day": 1 } },
    { "$group": {
        "_id": {
            "_id": "$_id",
            "SizeNames": "$SizeNames",
            "names": {
                "SizeList": "$names.SizeList",
                "SizeSubnames": "$names.SizeSubnames",
                "number": "$names.number",
                "list": "$names.list",
                "day": "$names.day"
            }
        },
        "subnames": { "$push": "$names.subnames" }
    }},
    { "$sort": { "_id._id": 1, "_id.names.day": 1 } },
    { "$group": {
        "_id": "$_id._id",
        "SizeNames": { "$first": "$_id.SizeNames" },
        "names": {
            "$push": { "$cond": [
                { "$ne": [ "$_id.names.SizeSubnames", 0 ] },
                {
                    "number": "$_id.names.number",
                    "subnames": "$subnames",
                    "list": "$_id.names.list",
                    "SizeList": "$_id.names.SizeList",
                    "day": "$_id.names.day"
                },
                {
                    "number": "$_id.names.number",
                    "list": "$_id.names.list",
                    "SizeList": "$_id.names.SizeList",
                    "day": "$_id.names.day"
                }
            ]}
        }
    }},
    { "$project": {
        "SizeNames": 1,
        "names": { 
            "$cond": [
                { "$ne": [ "$SizeNames", 0 ] },
                "$names",
                []
            ]
        }
    }}
])

You can kind of "hide away" the original empty array from the inner document as shown, but it's really difficult to remove all presence of the outer "names" array without pulling a similar conditional array "push" technique, and that really isn't a practical approach.

If all of this is just about sorting array elements in individual documents though, the aggregation framework should not be the tool to do this. It can be done as shown, but per document this is much easier to do in client side code.

Output:

{
    "_id" : ObjectId("54b5cff8102f292553ce9bb5"),
    "SizeNames" : 3,
    "names" : [
            {
                    "number" : 1,
                    "subnames" : [
                            {
                                    "id" : "X",
                                    "day" : 1
                            },
                            {
                                    "id" : "Z",
                                    "day" : 2
                            },
                            {
                                    "id" : "Y",
                                    "day" : 10
                            }
                    ],
                    "list" : [
                            "A",
                            "B",
                            "C"
                    ],
                    "SizeList" : 3,
                    "day" : 1
            },
            {
                    "number" : 3,
                    "subnames" : [
                            {
                                    "id" : "Z",
                                    "day" : 5
                            },
                            {
                                    "id" : "X",
                                    "day" : 8
                            }
                    ],
                    "list" : [
                            "A",
                            "C"
                    ],
                    "SizeList" : 2,
                    "day" : 2
            },
            {
                    "number" : 2,
                    "SizeList" : 0,
                    "day" : 5
            }
    ]
}

And if I want add SizeX (with $size) in all subnames documents ? Can you help me
@JohnS Sure. I can and so likely can others contribute as well. But how this works is that you "ask a new question" with all the details of what you really want. And then you "accept" the answers given to you, as I've already prompted. One question, one Answer. That is how this works. Welcome to StackOverflow.

Collectives™ on Stack Overflow

Use $size with $sort in array and sub array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related