Spring data-Mongo DB Query embedded array

Question

I have the document in mongo collection called (CustomerInformation) with following structure.

        {     "_id" : ObjectId("58f5e68c8205281d68bbb290"), 
            "_class" : "com.test.dataservices.entity.CustomerInformation", 
            "organizationInformation" : {
                "_id" : "123", 
                "companyName" : "Test1", 
                "ibanNumber" : "12345e", 
                "address" : "estates", 
                "contractInformation" : {
                    "duration" : NumberInt(0), 
                    "contractType" : "Gold", 
                    "totalUsers" : NumberInt(0)
                }, 
                "users" : [
                    {

                        "firstName" : "testuser1", 
                        "emailAddress" : "testuser1@test.com", 
                        "password" : "test1@123", 
                        "userAccessType" : "admin"
                    }, 
                    {

                        "firstName" : "testuser2", 
                        "emailAddress" : "testuser2@test.com", 
                        "password" : "test2@123", 
                        "userAccessType" : "user"
                    }
                ]
            }
        }

Now i want retrieve only the user information with matching emailAddress and Password. I am trying as follows.

Criteria elementMatchCriteria = Criteria.where("organizationInformation.users").
    elemMatch(Criteria.where("emailaddress").is("testuser1@test.com").and("password").is(test1@123));

    BasicQuery query = new BasicQuery(elementMatchCriteria.getCriteriaObject());

  CustomerInformation customer =mongoOperations.findOne(query, CustomerInformation.class);

I am getting the complete document with all users array , i want to retrieve only matching user information emailAddress and password. Whats the wrong in my Query or data model? Any suggestions? Thank you!

Answer 1

Use positional projection.

Criteria elementMatchCriteria = Criteria.where("organizationInformation.users").elemMatch(Criteria.where("emailAddress").is("testuser1@test.com").and("password").is("test1@123"));
Query query = Query.query(elementMatchCriteria);
query.fields().position("organizationInformation.users", 1);
CustomerInformation customer =mongoOperations.findOne(query, CustomerInformation.class);

Answer 2

You can use aggregation query with $unwind to achieve this

db.collection.aggregate([
    {
        $unwind:"$organizationInformation.users"
    },
    {
        $match:{
            "organizationInformation.users.emailAddress":"testuser1@test.com",
            "organizationInformation.users.password":"test1@123"
        }
    },
    {
        $project:{
            "organizationInformation.users":1
        }
    }
 ])

Result is:

{
    "_id" : ObjectId("58f5e68c8205281d68bbb290"),
    "organizationInformation" : {
        "users" : {
            "firstName" : "testuser1",
            "emailAddress" : "testuser1@test.com",
            "password" : "test1@123",
            "userAccessType" : "admin"
        }
    }
}

OR

db.collection.aggregate([
    {
        $unwind:"$organizationInformation.users"
    },
    {
        $match:{
            "organizationInformation.users.emailAddress":"testuser1@test.com",
            "organizationInformation.users.password":"test1@123"
        }
    },
    {
        $project:{
            user: "$organizationInformation.users"
        }
    }
])

Result is:

{
    "_id" : ObjectId("58f5e68c8205281d68bbb290"),
    "user" : {
        "firstName" : "testuser1",
        "emailAddress" : "testuser1@test.com",
        "password" : "test1@123",
        "userAccessType" : "admin"
    }
}