MongoDB, Java, sort by first array entry

Question

I'm trying to sort values after executing a find on my MongoDB by Java API. The result list contains the following entries:

{
"_id": "P17-223",
"property": "P17",
"itemid": 223,
"labels": [
  {
    "language": "en",
    "value": "Greenland"
  },
  {
    "language": "es",
    "value": "Groenlandia"
  },
  {
    "language": "de",
    "value": "Grönland"
  }
]

}

I want to sort by the first entry of the array labels:

  DBCursor cursor = getCollection().find(query);
  BasicDBObject orderBy = new BasicDBObject("labels[0].value", 1);
  cursor.sort(orderBy);

Cursor values are not sorted by this code. Can you help me?

It might be a lot easier to sort this in the Java code than on the MongoDB layer. Is that OK for you, or do you insist on sorting it on the database? — Philipp
– Philipp, Commented Jul 23, 2014 at 13:58
sorting in java is fine, unless you need to query large number of documents — injecteer
– injecteer, Commented Jul 23, 2014 at 14:28
Have you tried BasicDBObject orderBy = new BasicDBObject("labels.0.value", 1); — Mike
– Mike, Commented Jul 23, 2014 at 16:26
@Mike It's working! Please post this as an answer. I will mark it as accepted. — uı6ʎɹnɯ ꞁəıuɐp
– uı6ʎɹnɯ ꞁəıuɐp, Commented Jul 24, 2014 at 13:40

Mike · Accepted Answer · 2014-07-24 14:41:57Z

7

Have you tried

BasicDBObject orderBy = new BasicDBObject("labels.0.value", 1);

It's not obvious, but the MongoDB documentation eludes to it. Using the $ sign matches the first item, but specifying the array element number seems to work. If anyone has a better document describing the behavior, please reply with the link.

From the documentation

Update Documents in an Array

The positional $ operator facilitates updates to arrays that contain embedded
documents. Use the positional $ operator to access the fields in the embedded
documents with the dot notation on the $ operator.

db.collection.update( { <query selector> }, { <update operator>: { "array.$.field" : value } } )

Documentation is here

answered Jul 24, 2014 at 14:41

Mike

3,3113 gold badges31 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Beau Smith · Accepted Answer · 2018-09-13 19:13:52Z

5

I found this question/answer when looking for How to sort MongoDB (in Meteor) by first item of array…

I used this to find all documents in the Schedules collection which are active and then sort ascending by the first day in the days array.

Schedules.find(
  {
    active: true,
  },
  {
    sort: { 'days.0': 1 },
  },
)

A sample Schedule collection document with a Monday, Wednesday, Friday schedule looks something like this:

{
  _id: 9dh3ld0d7d0d,
  userId: UJD9dKd8edo,
  active: true,
  days: [1, 3, 5],
}

answered Sep 13, 2018 at 19:13

Beau Smith

34.5k14 gold badges97 silver badges102 bronze badges

Comments

Neil Lunn · Accepted Answer · 2014-07-23 15:08:34Z

You cannot actually "sort" by a specific index of an array within a document in MongoDB. ct If you really must do this then you need the aggregation framework to "extract" the element to sort on.

I know the list form is actually deprecated, so this code is just for demonstration. Acutally define your pipeline as individual variables and feed those as argument to aggregate:

    BasicDBList pipeline = new BasicDBList();
    list.add(new BasicDBObject("$unwind","$labels"));
    list.add(new BasicDBObject("$group",
        new BasicDBObject("_id","$_id")
            .append("property", new BasicDBObject("$first","$property"))
            .append("itemid", new BasicDBObject("$first","$itemid"))
            .append("labels", new BasicDBObject("$push","$labels"))
            .append("maxLabel", new BasicDBObject("$max", "$labels.value"))
    ));
    list.add(new BasicDBObject("$sort", new BasicDBObject("maxLabel",1)));

    System.out.println(pipeline);

That gives you the serialized version which is the JSON form of:

db.collection.aggregate([ 
    { "$unwind" : "$labels" }, 
    { "$group": { 
        "_id": "$_id",
        "property": { "$first" : "$property" },
        "itemid": { "$first" : "$itemid" }, 
        "labels": { "$push" : "$labels" },
        "maxLabel": { "$max" : "$labels.value"}
    }}, 
    { "$sort" : { "maxLabel" : 1} }
])

Better applied in your code as:

collection.aggregate(unwind,group,sort);

Where those are individually declared.

Thanks Neil, puh... complicated ;) Thanks Philipp, I will try to sort the elements in the Java code.

LRBH10 · Accepted Answer · 2017-08-07 14:18:09Z

0

Since MongoDB 3.0 Java Api, you can use com.mongodb.client.model.Sorts

Exemple use :

col.find().sort(Sorts.orderBy(Sorts.descending(fieldname)));

answered Aug 7, 2017 at 14:18

LRBH10

3013 silver badges6 bronze badges

Collectives™ on Stack Overflow

MongoDB, Java, sort by first array entry

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related