CODEWITHSUNDEEP
regexmongodbnestedfieldembedded-documents
Pramod Kumavat
Pramod Kumavat

Reputation: 61

MongoDB - find count of field in nested document

I want to find the count of all occurrences of the field "36" from the the following json: The count should be 2. The field is present in any of the data.TL.TXXX documents. I tried the find() method of mongoDB, but could only search in one document at a time. Probably I need a regex here. Can someone help me out:

{
    "_id" : ObjectId("1115dd31af82eb3ca8028188"),
    "data" : {
        "TL" : {
            "T001" : {
                "11" : "05012017",
                "13" : "0",
                "28" : "000",
                "29" : "00000",
                "30" : "01012017",
                "31" : "01122014",
                "36" : "10000",
                "37" : "3000",
                "38" : "29.81",
                "39" : "1",
                "44" : "03",
                "02" : "NOT DISCLOSED",
                "04" : "10",
                "05" : "1",
                "08" : "16122014"
            }
        }
    }
},
{
    "_id" : ObjectId("345222ddaf82eb1b262be44f"),
    "data" : {
        "TL" : {
            "T004" : {
                "10" : "19052013",
                "11" : "15062013",
                "12" : "37903",
                "13" : "0",
                "28" : "00000000",
                "29" : "000000000000000000",
                "30" : "01052013",
                "31" : "01062011",
                "44" : "03",
                "02" : "NOT DISCLOSED",
                "04" : "10",
                "05" : "1",
                "08" : "27062011",
                "09" : "08052013"
            },
            "T005" : {
                "11" : "10012017",
                "12" : "114525",
                "13" : "8853",
                "28" : "00000300000300000",
                "29" : "000000XXX0000000010",
                "30" : "01012017",
                "31" : "01022014",
                "36" : "100000",
                "37" : "10000",
                "44" : "03",
                "45" : "6714",
                "02" : "NOT DISCLOSED",
                "04" : "10",
                "05" : "1",
                "08" : "27062011",
                "09" : "12122016"
            },
        }
    }
}

Upvotes: 1

Views: 445

Answers (1)

Ashh
Ashh

Reputation: 46441

You can use below aggregation

db.collection.aggregate([
  { $project: { "data.TL": { $objectToArray: "$data.TL" }}},
  { $unwind: "$data.TL" },
  { $project: { data: { $objectToArray: "$data.TL.v" }}},
  { $unwind: "$data" },
  { $group: { _id: "$data.k", count: { $sum: 1 }}}
]);

MongoPlayground

Upvotes: 1

Related Questions