MongoDB.live, free & fully virtual. June 9th - 10th. Register Now MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Querying array for subdocuments

I’m an MongoDB-newbie, so please allow following question:

I have thousands of documents with structure like below. First, I would like to query in array “Torschützen” just for subdocuments where field “Bemerkung” is not empty. How can this be done?

{ 
    "_id" : ObjectId("5e89e93b5ee91429cc9cf7dc"), 
    "Saison" : "1415", 
    "Jahr" : "2014/15", 
    "Liga" : "2. Landesliga Süd", 
    "Ak" : "KMS", 
    "AkBezeichnung" : "Kampfmannschaft", 
    "Runde" : NumberInt(5), 
    "HeimMs" : "Mühlbach/Pzg.", 
    "AuswMs" : "SC ikarus Pfw.", 
    "Datum" : ISODate("2014-08-31T13:00:00.000+0000"), 
    "HeimTore" : NumberInt(0), 
    "AuswTore" : NumberInt(5), 
    "Schiedsrichter" : "", 
    "NichtGewertet" : false, 
    "Torschützen" : [
        {
            "Torfolge" : "1 : 0", 
            "SpielerName" : "Grünwald Hannes", 
            "Gegnername" : "", 
            "Minute" : NumberInt(12), 
            "Bemerkung" : "", 
            "AnzahlTore" : NumberInt(1)
        }, 
        {
            "Torfolge" : "2 : 0", 
            "SpielerName" : "Krameter Silvio", 
            "Gegnername" : "", 
            "Minute" : NumberInt(22), 
            "Bemerkung" : "", 
            "AnzahlTore" : NumberInt(1)
        }, 
        {
            "Torfolge" : "3 : 0", 
            "SpielerName" : "Grüll Marco", 
            "Gegnername" : "", 
            "Minute" : NumberInt(52), 
            "Bemerkung" : "Foulelfmeter", 
            "AnzahlTore" : NumberInt(1)
        }, 
        {
            "Torfolge" : "4 : 0", 
            "SpielerName" : "Grünwald Hannes", 
            "Gegnername" : "", 
            "Minute" : NumberInt(71), 
            "Bemerkung" : "", 
            "AnzahlTore" : NumberInt(1)
        }, 
        {
            "Torfolge" : "5 : 0", 
            "SpielerName" : "Krameter Silvio", 
            "Gegnername" : "", 
            "Minute" : NumberInt(78), 
            "Bemerkung" : "", 
            "AnzahlTore" : NumberInt(1)
        }
    ]
}

And second, is it possible to get just fields of subdocuments from array “Torschützen” , without explizitly eleminating fields outside this array, e.g. _id: 0, Saison: 0, Liga: 0, …?

Thank you in advance for your support!

You can do something like this. however, you can modify it and create a more efficient query to have a smaller output.

db.getCollection("test").aggregate(
    [
        { 
            "$unwind" : { 
                "path" : "$Torschützen", 
                "preserveNullAndEmptyArrays" : false
            }
        }, 
        { 
            "$match" : { 
                "Torschützen.Bemerkung" : { 
                    "$exists" : true, 
                    "$ne" : ""
                }
            }
        }, 
        { 
            "$project" : { 
                "_id" : 0.0, 
                "Saison" : 0.0, 
                "Liga" : 0.0
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
);

And sorry, I’m not sure if understood your second question correctly but basically by adding a $project stage to the aggregation pipeline the output contains only that part of document you’re looking for.

Hope this helps. You may see this page: https://docs.mongodb.com/manual/aggregation/ for more details. This one https://docs.mongodb.com/manual/tutorial/query-array-of-documents/ may help as well.

Thank you very much for your quick reply, that was exactly what I searched for!!!

I tried it without using aggregation framework, just with a “normal” find:

db.Spiele.find({…, ‘Torschützen.Bemerkung’: {’$exists’: true, ‘$ne’: ‘’}})

But I didn’t get right response, so is my request just possible with aggregation (unfortunately I’m not familar with it at the moment)?

And concerning my second question: Yes, you are right, it’s simple, just using “{’$project’: {’_id’: 0, ‘Torschützen’: 1}”