Query an element of an array with exactly matching fields MongoDB in Java

Question

I would like to query a one/more matching fields of an array elements(can include subdocuments too) that is into document.

For example:

My collection includes documents below:

{ 
    "_id": 1, 
    "index": 1,
    "elements":[
        {
            "name":"test",
            "date":"Mon Sep 01 01:00:00 EEST 2014" , 
            "tag":1
        }, 
        {
            "name": "test2",
            "date": "Mon Sep 01 01:00:00 EEST 2014",
            "tag": 2
        },
        {
            "name": "test",
            "date": "Mon Sep 01 02:00:00 EEST 2014",
            "tag": 3
        }
    ]
},
{ 
    "_id":2, 
    "index":2, 
    "elements":[ 
        {
            "name":"test",
            "date":"Mon Sep 01 01:00:00 EEST 2014" ,
            "tag":1
        }, 
        {
            "name": "test2",
            "date": "Mon Sep 01 01:00:00 EEST 2014", 
            "tag":2
        },
        {   
            "name":"test",
            "date":"Mon Sep 01 01:10:00 EEST 2014", 
            "tag":3
        }
    ]
},
{ 
    "_id": 3,
    "index": 3,
    "elements": [ 
        {
            "name": "test",
            "date": "Mon Sep 01 01:00:00 EEST 2014", 
            "tag":1
        },
        { 
            "name": "test2",
            "date": "Mon Sep 01 01:00:00 EEST 2014", 
            "tag":2
        },
        {
            "name": "test",
            "date": "Mon Sep 01 01:10:00 EEST 2014", 
            "tag":3
        }
    ]
}

---

I want my query result to return me a document like below:

{ 
    "_id":1,
    "index": 1,
    "elements":[
        { 
            "name":"test",
            "date":"Mon Sep 01 02:00:00 EEST 2014" ,
            "tag":3
        }
    ]
}

To provide this I wrote a query

Date dCriteria = new SimpleDateFormat("dd/MM/yy HH:mm:ss").parse("01/09/2014 05:00:00");

Query find = new Query( Criteria.where("index").is(3) );  //To find matching documents
find.fields().elemMatch(
    "elements",
    Criteria.where("name").is("test").and("date").gte(dCriteria)));

mongotemplate.findOne( find, Document.class );

Which means in MongoDB shell command as:

db.collection.find(
    { "index": 3 },
    { "elements": { 
        "$elemMatch": {
            "name": "test", 
            "date": { 
                "$gte": { "$date":"2014-09-01T02:00:000Z" }
            }
        }
    }
 )          

But it return the following result:

{
    "_id": 0, 
    "index": 0, 
    "elements":[
        {
            "name": "test",
            "date": "Mon Sep 01 01:00:00 EEST 2014",
            "tag":1
        }
    ]
}

It is ok to omit _id and index fileds but it returns first macting element of array due to matching criteria , either matching "name":"test" or "date" is greater than equal dCriteria, but what I want is to match both criterias at the same time.

To make this I must use $elemMatch operator to query exatcly matches more than one field at the same time of an array element. But I have no idea how to use its syntax in my projection.

Answer 1

MongoDB stores dates as UTC, which is also generally best practice for storing dates in general since it allows for conversion to and from various timezones without working out an original point of origin.

As such it is most certain that the actual dates here are indeed UTC, but you are passing in a value constructed from a different timezone. The driver actually serializes the BSON request with the epoch timestamp and noting a "Date" BSON type. This means that your input is now "skewed" by the timezone difference. In this case 3 hours are taken off to represent UTC so the converted date is 3 hours earlier than you think.

If you have source data for a date that comes in for a specific timezone then you need to convert this first, both as input and as presentation in user interface if you intend to show values in local time.

So either convert or construct as UTC:

    // org.joda.time as Date constructor is deprecated
    Date dCriteria = new DateTime(2014,9,1,1,10,DateTimeZone.UTC).toDate();

    Query find = new Query( Criteria.where("index").is(3) );  //To find matching documents
    find.fields().elemMatch(
            "elements",
            Criteria.where("name").is("test").and("date").gte(dCriteria)
    );

    System.out.println(find);

Where you are unsure of how the resulting query will be structured, print it out for general debugging. You really should be doing this at a logging level which you can suppress in production.

As formed above this is the same as the shell query you reference, but your code as it stands is not since it is using a local time and not UTC.

You also almost always really want to do your element matching in the query rather than in the projection. The "first" match obtained can then be projected using the positional $ operator once you move your $elemMatch to the query portion of your statement. This ensures you don't get an empty array when none of the elements in the array actually match your condition.