Mongo SORT with specific entry from list of attribute

Question

I am working with Mongo aggregation/filter/sorting and limit with JAVA.

I have below model objects. CustomPOJO is my database collection.

@Document(collation = "CustomPOJO")
public class CustomPOJO {

    public List<KeyValueObject> valueList;
    public String attribute1;
    public String attribute2;
    public CustomObject attribute3;

}

public class CustomObject {
    public String attribute4;
    public String attribute5;
}

public class KeyValueObject {
    public String name;
    public String value;
}

Now I have entries in my DB with below records

Record1:

{
    "valueList": [{
        "name": "field1",
        "value": "value1"
    }, {
        "name": "field2",
        "value": "value2"
    }, {
        "name": "field3",
        "value": "value3"
    }],
    "attribute1": "attribute1",
    "attribute2": "attribute2",
    "attribute3": {
        "attribute4": "attribute4",
        "attribute5": "attribute5"
    }

}

Record2:

{
    "valueList": [{
        "name": "field1",
        "value": "value4"
    }, {
        "name": "field2",
        "value": "value5"
    }, {
        "name": "field3",
        "value": "value6"
    }],
    "attribute1": "attribute11",
    "attribute2": "attribute22",
    "attribute3": {
        "attribute4": "attribute44",
        "attribute5": "attribute55"
    }

}

I am able to do sort on all fields using sort(DESC,"attribute1") or sort(DESC,"attribute3.attribute4") or sort(DESC,"attribute2"). But i am unable to sort on valueList. I want to do sort according to specific KeyValue entry inside of valueList.

For example record1 have valueList with entry field1 so all records in DB should sort according field1 and value inside it.

In general i want to sort on field inside of list attribute.

Any help will be highly appreciate.

Answer 1

You can't sort using array's key value pair in place. You could use aggregation framework.

Project the document matching key entry followed by sort on the value and remove the additional field from the document.

Something like

db.collection.aggregate(
[
  {"$set":{
    "sortdoc":{
      "$arrayElemAt":[
        {
          "$filter":{
            "input":"$valueList",
            "cond":{"$eq":["$$this.name","field1"]}
          }
        },0]}
  }},
  {"$sort":{"sortdoc.value":-1}},
  {"$project":{"sortdoc":0}}
])

https://mongoplayground.net/p/gzLQm8m2wQW

You could also model valueList as object just the way you have for attributes. Use Document class or HashMap instead of List of values and use sort(DESC,"document.field1")

@Document(collation = "CustomPOJO")
public class CustomPOJO {

  public Document document;
  public String attribute1;
  public String attribute2;
  public CustomObject attribute3;

}

Document

{
    "document": {
      "field1": "value1",
      "field2": "value2"
    },
    "attribute1": "attribute1",
    "attribute2": "attribute2",
    "attribute3": {
      "attribute4": "attribute4",
      "attribute5": "attribute5"
    }
}

Answer 2

You cannot sort documents on fields inside of Arrays directly. Generally what i do is unwind the array and then perform sort.

May be this is something that may help you.

db.collection.aggregate([
  {
    "$unwind": "$valueList"
  },
  {
    "$sort": {
      "valueList.name": -1
    }
  }
])

Mongo Playground