java mongodb 3.0: find value in internal array of documents

Question

I'm using java and mongodb v.3.0.7. I have a list of player with internal array of games with scores. This is a test that insert a document:

public void insertPlayer(String id_device) throws ParseException{
    DateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'", Locale.ENGLISH);
    db.getCollection("player").insertOne(
                    new Document()
                            .append("nikname", "Guest")
                            .append("password", "Guest")
                            .append("my_id", "")
                            .append("id_device", id_device)
                            .append("language", "Italian")
                            .append("games", asList(
                            new Document()
                                    .append("gamename", "PPA")
                                    .append("banned", false)
                                    .append("date", format.parse("2014-10-01T00:00:00Z"))
                                    .append("score", 11),
                            new Document()
                                    .append("gamename", "Test2game")
                                    .append("banned", false)
                                    .append("date", format.parse("2014-01-16T00:00:00Z"))
                                    .append("score", 17)))
            );
}

To find if a player is banned from a particular game I'm doiing this:

public boolean isBanned(String id_device){
    FindIterable<Document> iterable = db.getCollection("player").find(eq("id_device", "machine1"));

    System.out.println(iterable.first());
    List<Document> dl = (List<Document>)iterable.first().get("games");
    for(int i=0;i<dl.size();i++){
        Document d = dl.get(i);
        System.out.println(d.getString("gamename"));
        if(d.getString("gamename").equals("PPA")){
            boolean ban = d.getBoolean("banned");
            return ban;
        }
    }

There is a faster way using embedded mongodb methods that find the document:

new Document()
.append("gamename", "PPA")
.append("banned", false)
.append("date", format.parse("2014-10-01T00:00:00Z"))
.append("score", 11),

giving id_device and gamename? thanks

Yes. Your find query should be something like this: `db.player.find({"id_device":"machine1", "games.gamename":"PPA","games.banned":true})`. This will return banned player list if exists (`.size()`>0). — Valijon, Jan 16 '16 at 09:34
thanks for the answer, but I need to know if it is possible to have the nested document or the value because, for example, in the future I will need to get the score of the player with id_device and gamename... — sefiroths, Jan 16 '16 at 09:42
Yes, you can. But, you need to change `find` to `aggregate`. [https://docs.mongodb.org/manual/aggregation/](https://docs.mongodb.org/manual/aggregation/). Take a look to this post where the question was similar how to fetch/exclude subdocument by query[http://stackoverflow.com/a/34754323/3710490](http://stackoverflow.com/a/34754323/3710490). If you want, I can help you to make MongoDB shell query, but then you need to change to java. — Valijon, Jan 16 '16 at 09:46
I tryed AggregateIterable iterable = db.getCollection("player").aggregate(asList( new Document("$match", new Document("id_device", id_device).append("games.gamename", "PPA")), new Document("$group", new Document("_id", "$id_device").append("games.gamename","PPA")))); but gives me error, if I delete append("games.gamename","PPA") gives me only one record {{id_: machine1}} — sefiroths, Jan 16 '16 at 10:40
Can you provide more details for your usecase? Can a game repeat for given player? What exactly output do you expect? — Valijon, Jan 16 '16 at 12:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100841/discussion-between-valijon-and-sefiroths). — Valijon, Jan 16 '16 at 12:18

Valijon · Accepted Answer · 2016-01-16T12:26:05.717

To achieve that, you need to aggregate your data. https://docs.mongodb.org/manual/aggregation/

Depending on your usecase, aggregation may change (more query, more pipeline steps).

Here is your data:

{
    "_id" : ObjectId("569a30a30586bcb40f7d2531"),
    "my_id" : "",
    "id_device" : "machine1",
    "language" : "Italian",
    "games" : [ 
        {
            "gamename" : "PPA",
            "banned" : true,
            "date" : ISODate("2014-10-01T00:00:00.000Z"),
            "score" : 11
        }, 
        {
            "gamename" : "Test2game",
            "banned" : false,
            "date" : ISODate("2014-01-16T00:00:00.000Z"),
            "score" : 17
        }
    ]
}

I assume:

You have a list of unique players. Each of them play unique games.
(Example: player1 never plays PPA twice) 
So, you need to search a game where the player is banned and return 
all information for that game.

The aggregation would be:

db.player.aggregate([
    {$match:{ "id_device" : "machine1"}},
    {$unwind: "$games"},
    {$match:{ "games.gamename" : "PPA", "games.banned" : true}}
])

Result

[ 
    {
        "_id" : ObjectId("569a30a30586bcb40f7d2531"),
        "my_id" : "",
        "id_device" : "machine1",
        "language" : "Italian",
        "games" : {
            "gamename" : "PPA",
            "banned" : true,
            "date" : ISODate("2014-10-01T00:00:00.000Z"),
            "score" : 11
        }
    }
]

Some difference, if your players may play same game more than once (different date), you can change your aggregation pipelines.

{
    "_id" : ObjectId("569a30a30586bcb40f7d2531"),
    "my_id" : "",
    "id_device" : "machine1",
    "language" : "Italian",
    "games" : [ 
        {
            "gamename" : "PPA",
            "banned" : false,
            "date" : ISODate("2014-10-01T00:00:00.000Z"),
            "score" : 11
        }, 
        {
            "gamename" : "Test2game",
            "banned" : false,
            "date" : ISODate("2014-01-16T00:00:00.000Z"),
            "score" : 17
        }, 
        {
            "gamename" : "PPA",
            "banned" : true,
            "date" : ISODate("2014-04-18T00:00:00.000Z"),
            "score" : 23
        }, 
        {
            "gamename" : "Foo",
            "banned" : true,
            "date" : ISODate("2015-03-03T00:00:00.000Z"),
            "score" : 2
        }, 
        {
            "gamename" : "Foo",
            "banned" : false,
            "date" : ISODate("2015-04-28T00:00:00.000Z"),
            "score" : 2
        }
    ]
}

So to query id_device and gamename "PPA", we define our aggregation this way:

db.player.aggregate([
    {$match:{ "id_device" : "machine1"}},
    {$unwind: "$games"},
    {$match:{ "games.gamename" : "PPA"}},
    {$group: {_id:{"_id":"$_id", "my_id":"$my_id", "id_device":"$id_device","language":"$language"}, "games" : {$push:"$games"}}},
    {$project: {"_id":"$_id._id", "my_id":"$_id.my_id", "id_device": "$_id.id_device", "language":"$_id.language", "games":"$games"}}
])

Result:

[ 
    {
        "_id" : ObjectId("569a30a30586bcb40f7d2531"),
        "games" : [ 
            {
                "gamename" : "PPA",
                "banned" : false,
                "date" : ISODate("2014-10-01T00:00:00.000Z"),
                "score" : 11
            }, 
            {
                "gamename" : "PPA",
                "banned" : true,
                "date" : ISODate("2014-04-18T00:00:00.000Z"),
                "score" : 23
            }
        ],
        "my_id" : "",
        "id_device" : "machine1",
        "language" : "Italian"
    }
]

As you see, you can add/modify pipeline steps and get desired result.

java mongodb 3.0: find value in internal array of documents

1 Answers1