Complex MongoDB find queries on large documents in Java

Question

How do I make a MongoDB query using BasicDBObjects in Java, when I wish to find all documents that contain an array of nested documents, where one of those nested documents meets all the specified criteria?

Taking the example data:

[
   {
      "_id":"blood_0",
      "type":"O",
      "list":[
         {
            "firstname":"John",
            "lastname":"Smith",
            "zipcode":"12345"
         },
         {
            "firstname":"John",
            "lastname":"Hamilton",
            "zipcode":"54627"
         },
         {
            "firstname":"Ben",
            "lastname":"Brick",
            "zipcode":"12345"
         },
         {
            "firstname":"William",
            "lastname":"Tell",
            "zipcode":"15487"
         }
      ]
   },
   {
      "_id":"blood_1",
      "type":"AB",
      "list":[
         {
            "firstname":"Mary",
            "lastname":"Smith",
            "zipcode":"12345"
         },
         {
            "firstname":"John",
            "lastname":"Henry",
            "zipcode":"54624"
         },
         {
            "firstname":"Jacob",
            "lastname":"Tell",
            "zipcode":"19283"
         },
         {
            "firstname":"William",
            "lastname":"Dirk",
            "zipcode":"15999"
         }
      ]
   }
]

If I only want to return the objects that contain a contact in the list that meets the criteria of firstname = William, lastname = Tell how would I go about doing that? The queries I am doing are not grouping the criteria, so I would get two results where I actually only should be getting one.

How would I do the same query but also checking for type = AB, as well as the other criteria, which would return no results?

docs.mongodb.org/manual/reference/method/db.collection.find/… — Kostas Kryptos
– Kostas Kryptos, Commented Dec 22, 2014 at 21:12
I know how the query is supposed to work if I was going to query MongoDB, I don't know how to recreate it in Java using BasicDBObject. — Joe
– Joe, Commented Dec 22, 2014 at 21:29

Rob Moore · Accepted Answer · 2014-12-23 04:19:18Z

You are looking for the $elemMatch operator. It restricts the query operators to a single element within the array of values.

In the shell your query will look like:

db.people.find( { list : { $elemMatch : { lastName:"Smith", firstName: "John" } } } )

To add the blood type:

db.people.find( { 
       type : "AB",
       list : { $elemMatch : { lastName:"Smith", firstName: "John" } } 
} )

This gets a bit verbose using the Java Driver.

DBObject elemMatch = new BasicDBObject();
elemMatch.put("lastName","Smith");
elemMatch.put("firstName","John");

DBObject query = new BasicDBObject();
query.append( "type", "AB");
query.append( "list", elemMatch);

Pass that query to one of the find() methods on the collection and you should get the documents you are looking for.

Note that the $elemMatch query operator will return the entire document, including all of the elements in the array. There is a similarly named projection operator to limit the array elements returned to only those matched.

HTH - Rob.

Markus W Mahlberg · Accepted Answer · 2014-12-22 21:52:20Z

0

First things first. I really think your model is utterly wrong. Nested arrays which potentially grow indefinetly are bad for multiple reasons:

If the document exceeds it's padding when new members are written to this array, the document needs to be migrated within a data file. That is a pretty costly operation and you want to prevent it as much as you can.
BSON documents are limited to 16MB. So per blood type you could only have a limited number of people.
All queries tend to be a bit more complicated, the according code more bloated and hence slower.

So how to do it? Take these documents:

{
  _id: ObjectId(),
  firstName: "Mary",
  lastName: "Smith",
  zip: "12345",
  bt: "AB"
},
{
  _id: ObjectId(),
  firstName: "John",
  lastName: "Smith",
  zip: "12345",
  bt: "0"
}

With indices set like

db.people.ensureIndex({lastName:1,firstName:1})
db.people.ensureIndex({bt:1})

on the MongoDB shell, you can get what you want with

db.people.find({ lastName:"Smith", firstName: "John"})

or

db.people.find({ bt: "AB" })

This query for example translates to the following

MongoClient client = new MongoClient("localhost");

DB db = client.getDB("yourDB");
DBCollection coll = db.getCollection("yourCollection");

BasicDBOBject query = new BasicDBObject("bt","AB");
DBCursor cursor = coll.find(query);

try {

  while( cursor.hasNext() ) {

    System.out.println( cursor.next() );

  }

} finally {

    cursor.close();

}

You might find the MongoDB introduction for working with a Java driver interesting.

edited Dec 22, 2014 at 21:52

answered Dec 22, 2014 at 21:34

Markus W Mahlberg

21k6 gold badges70 silver badges97 bronze badges

4 Comments

Joe Over a year ago

That data is simply an example to provide a visual of what I was asking as I am not permitted to post the data model I am working with, the arrays are not indefinite. I did not create the data model, nor do I have the time or permission to make changes to it. I am simply asking how to write the query using the Java Mongo drivers to successfully get the correct results I am looking for.

Markus W Mahlberg Over a year ago

Well, I have put an example and a link to the docs. As a side note, I'd like to add that the model is everything in MongoDB. So I'd like to point you to the modelling docs.

Joe Over a year ago

Your example shows a basic find query, and you reorganized my data... My question is related to finding documents based on searching nested arrays of objects against criteria. If you need a better idea of what my data is, imagine a collection of customer profiles, each document has an array of contact method objects. I want to search against those contact method objects using multiple criteria.

Markus W Mahlberg Over a year ago

Mind reading the links I provided you with? Or the basic documentation? ;) I am asking because if you did, you should have come along using the so called 'dot.notation' for searching in arrays of subdocuments without specifying an array index. So you'd query for list.firstname and list.lastname, to stay within your example (which you should have marked clearly as artificial).

Collectives™ on Stack Overflow

Complex MongoDB find queries on large documents in Java

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related