Get document by min size of array in Mongodb

Question

I have mongo collection:

{
    "_id" : 123,
    "index" : "111",
    "students" : [ 
        {
            "firstname" : "Mark",
            "lastname" : "Smith"),
        }
    ],
}


{
    "_id" : 456,
    "index" : "222",
    "students" : [ 
        {
            "firstname" : "Mark",
            "lastname" : "Smith"),
        }
    ],
}

{
    "_id" : 789,
    "index" : "333",
    "students" : [ 
        {
            "firstname" : "Neil",
            "lastname" : "Smith"),
        },
      {
            "firstname" : "Sofia",
            "lastname" : "Smith"),
        }
    ],
}

I want to get document that has index that is in the set of the given indexes, for example givenSet = ["111","333"] and has min length of students array. Result should be the first document with _id:123, because its index is in the givenSet and studentsArrayLength = 1, which is smaller than third.

I need to write custom JSON @Query for Spring Mongo Repository. I am new to Mongo and am stuck a bit with this problem.

I wrote something like this:

@Query("{'index':{$in : ?0}, length:{$size:$students}, $sort:{length:-1}, $limit:1}")
Department getByMinStudentsSize(Set<String> indexes);

And got error: error message '$size needs a number'

Should I just use .count() or something like that?

Answer 1

you should use the aggregation framework for this type of query.

1. filter the result based on your condition.

2. add a new field and assign the array size to it.

3. sort based on the new field.

4. limit the result.

the solution should look something like this:

db.collection.aggregate([
  {
    "$match": {
      index: {
        "$in": [
          "111",
          "333"
        ]
      }
    }
  },
  {
    "$addFields": {
      "students_size": {
        "$size": "$students"
      }
    }
  },
  {
    "$sort": {
      students_size: 1
    }
  },
  {
    "$limit": 1
  }
])

working example: https://mongoplayground.net/p/ih4KqGg25i6

Answer 2

You are getting the issue because the second param should be enclosed in curly braces. And second param is projection

@Query("{{'index':{$in : ?0}}, {length:{$size:'$students'}}, $sort:{length:1}, $limit:1}")
Department getByMinStudentsSize(Set<String> indexes);

Below is the mongodb query :

db.collection.aggregate(
    [
        { 
            "$match" : { 
                "index" : { 
                    "$in" : [
                        "111", 
                        "333"
                    ]
                }
            }
        }, 
        { 
            "$project" : { 
                "studentsSize" : { 
                    "$size" : "$students"
                }, 
                "students" : 1.0
            }
        }, 
        { 
            "$sort" : { 
                "studentsSize" : 1.0
            }
        }, 
        { 
            "$limit" : 1.0
        }
    ], 
    { 
        "allowDiskUse" : false
    }
);