How to sort documents by array field?

Working with Data
1

Hi There,

I need to sort documents by an array field containing boolean values.

{
	"_id" : ObjectId("610024d989c5b829e72940c5"),
	"firstname" : "Christophe",
	"lastname" : "Pichon",
	"documents" : [
		{
			"mongoId" : "610024d90053e800143b0fd3",
			"status" : "notified",
			"signable" : true,
			"signed" : false
		},
		{
			"mongoId" : "610024d90053e800143b0fd5",
			"status" : "notified",
			"signable" : true,
			"signed" : false
		},
		{
			"mongoId" : "60fea9140053e800143b0f92",
			"status" : "notified",
			"signable" : false,
			"signed" : false
		},
		{
			"mongoId" : "610024d90053e800143b0fd7",
			"status" : "notified",
			"signable" : true,
			"signed" : false
		}
	]
},
{
	"_id" : ObjectId("610024b189c5b829e72940c4"),
	"firstname" : "Paulette",
	"lastname" : "Besnard",
	"documents" : [
		{
			"mongoId" : "610024b00053e800143b0fc4",
			"status" : "notified",
			"signable" : true,
			"signed" : true
		},
		{
			"mongoId" : "610024b10053e800143b0fc6",
			"status" : "notified",
			"signable" : true,
			"signed" : false
		},
		{
			"mongoId" : "60fea9140053e800143b0f92",
			"status" : "notified",
			"signable" : false,
			"signed" : false
		},
		{
			"mongoId" : "610024b10053e800143b0fc8",
			"status" : "notified",
			"signable" : true,
			"signed" : false
		}
	]
},
{
	"_id" : ObjectId("60febc9e89c5b829e72940c3"),
	"firstname" : "Louis",
	"lastname" : "Herve",
	"documents" : [
		{
			"mongoId" : "60febc9e0053e800143b0fb7",
			"status" : "notified",
			"signable" : true,
			"signed" : true
		},
		{
			"mongoId" : "60febc9e0053e800143b0fb9",
			"status" : "notified",
			"signable" : true,
			"signed" : true
		},
		{
			"mongoId" : "60fea9140053e800143b0f92",
			"status" : "notified",
			"signable" : false,
			"signed" : false
		},
		{
			"mongoId" : "60febc9e0053e800143b0fbb",
			"status" : "notified",
			"signable" : true,
			"signed" : false
		}
	]
},

Once sorted, I need this result :

{
	"_id" : ObjectId("60febc9e89c5b829e72940c3"),
	"firstname" : "Louis",
	"lastname" : "Herve",
},

{
	"_id" : ObjectId("610024b189c5b829e72940c4"),
	"firstname" : "Paulette",
	"lastname" : "Besnard",
},

{
	"_id" : ObjectId("610024d989c5b829e72940c5"),
	"firstname" : "Christophe",
	"lastname" : "Pichon",
},

As :
Louis has 2./3 (signed/signable) documents/
Paulette has 1/3
Christophe 0/3

An idea on how to perform this ?

Thanks for your help

1 Like
2

Hello @muama, the following aggregation gets the desired result:

db.collection.aggregate([
{ 
    $addFields: { 
        boolean_counts: { 
            $reduce: { 
                input: "$documents", 
                initialValue: { n1: 0, n2: 0 }, 
                in: {
                    n1: { $cond: [ 
	                        { $eq: [ "$$this.signed", true ] }, 
	                        { $add: [ "$$value.n1", 1 ] }, 
	                        { $add: [ "$$value.n1", 0 ] } 
                    ] },
                    n2: { $cond: [ 
                            { $eq: [ "$$this.signable", true ] }, 
                            { $add: [ "$$value.n2", 1 ] }, 
                            { $add: [ "$$value.n2", 0 ] } 
                    ] }
                }
            }
        }
    }
},
{ 
    $sort: { 
        "boolean_counts.n1": -1, 
        "boolean_counts.n2": -1 
    }
},
{ 
    $project: { 
        firstname: 1, lastname: 1 
    } 
}
])
1 Like
3

Hi @Prasad_Saya

Your solution works, thanks for your help.
But I’ve used this, easier for me to understand

db.collectionName
.aggregate([
{
    $addFields: {
        "signed": { // Count will be stored in "signed" field
            $size: { // Get size of filtered documents
                $filter: { // Filter out signed documents
                    "input": "$documents", "as": "item", "cond": { "$eq": ["$$item.signed", true] }
                }
            }
        }
    }
}, 
{
    $sort: { // sorting users who signed the most documents
        "signed": -1,
    }
}
])
3 Likes
4

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.