CODEWITHSUNDEEP
javascriptmongodbaggregation-framework
emonigma
emonigma

Reputation: 4406

Aggregate nested documents with missing data

I have a mongoDB collection with data such as:

{
    "_id" : ObjectId("..."),
    "records" : [
        ISODate("2020-04-19T00:49:18.945Z"),
        {
            "_id" : ObjectId(""),
            "date" : ISODate("2020-05-07T04:49:55.643Z"),
            "text" : "someText"
        }
    ],
}

The value in records is different due to version upgrades.

I want to aggregate records.text across all documents ignoring the missing data. The code from MongoDB: Aggregate and flatten an array field

db.collection.aggregate({$unwind : "records"},
                      {$project: {_id: 1, 'text': '$records.text'}})

throws:

path option to $unwind stage should be prefixed with a '$': records

and fixing the error from these directions to accommodate empty fields:

db.collection.aggregate({$unwind : "records", includeEmpty: false},
                      {$project: {_id: 1, 'text': '$records.text'}})

throws

A pipeline stage specification object must contain exactly one field.

How can I aggregate values from nested arrays with possibly empty values?

Upvotes: 1

Views: 197

Answers (2)

mickl
mickl

Reputation: 49945

You can filter out the empty ones using $exists:

db.collection.aggregate([
    { $unwind: "$records" },
    { $match: { "records.text": { $exists: true } } },
    { $project: { _id: 1, text: "$records.text" }}
    {$group: {_id: "$text", count: {$sum: 1}}},
    {$sort: {count: -1}}
])

Mongo Playground

Upvotes: 1

ngShravil.py
ngShravil.py

Reputation: 5048

In your first query, you are missing the '$', since record is a field value, so you should prefix it with '$'. The final query will be:

db.collection.aggregate({$unwind : "$records"},
                      {$project: {_id: 1, 'text': '$records.text'}})

I hope this works for you.

Upvotes: 1

Related Questions