CODEWITHSUNDEEP
mongodbaggregation-framework
Israel
Israel

Reputation: 495

Aggregate nested arrays

I have multiple documents, and I'm trying to aggregate all documents with companyId = xxx and return one array with all the statuses.

So it will look like this:

[
{
    "status": "created",
    "date": "2019-03-16T10:59:59.200Z"
},
{
    "status": "completed",
    "date": "2019-03-16T11:00:37.750Z"
},
{
    "status": "created",
    "date": "2019-03-16T10:59:59.200Z"
},
{
    "status": "completed",
    "date": "2019-03-16T11:00:37.750Z"
},
{
    "status": "created",
    "date": "2019-03-16T10:59:59.200Z"
},
{
    "status": "completed",
    "date": "2019-03-16T11:00:37.750Z"
},
{
    "status": "created",
    "date": "2019-03-16T10:59:59.200Z"
},
{
    "status": "completed",
    "date": "2019-03-16T11:00:37.750Z"
}

]

The document look like this:

[
{
    "companyId": "xxx",
    "position": "",
    "section": "",
    "comment": "",
    "items": [
        {
            "any": "111",
            "name": "some name",
            "description": "some description",
            "version": "3",
            "status": [
                {
                    "status": "created",
                    "date": "2019-03-16T10:59:59.200Z"
                },
                {
                    "status": "completed",
                    "date": "2019-03-16T11:00:37.750Z"
                }
            ]
        },
        {
            "any": "222",
            "name": "some name",
            "description": "some description",
            "version": "3",
            "status": [
                {
                    "status": "created",
                    "date": "2019-03-16T10:59:59.200Z"
                },
                {
                    "status": "completed",
                    "date": "2019-03-16T11:00:37.750Z"
                }
            ]
        }
    ]
},
{
    "companyId": "xxx",
    "position": "",
    "section": "",
    "comment": "",
    "items": [
        {
            "any": "111",
            "name": "some name",
            "description": "some description",
            "version": "3",
            "status": [
                {
                    "status": "created",
                    "date": "2019-03-16T10:59:59.200Z"
                },
                {
                    "status": "completed",
                    "date": "2019-03-16T11:00:37.750Z"
                }
            ]
        },
        {
            "any": "222",
            "name": "some name",
            "description": "some description",
            "version": "3",
            "status": [
                {
                    "status": "created",
                    "date": "2019-03-16T10:59:59.200Z"
                },
                {
                    "status": "completed",
                    "date": "2019-03-16T11:00:37.750Z"
                }
            ]
        }
    ]
}

]

Any suggestion, how to implement this?

Then I want to loop over the array (in code) and count how many items in status created, and completed. maybe it could be done with the query?

Thanks in advance

Upvotes: 1

Views: 206

Answers (3)

Abhinandan Kothari
Abhinandan Kothari

Reputation: 303

If the number of documents on which you are executing the above query is too much then you should avoid using $unwind in the initial stage of aggregation pipeline.
Either you should use $project after $match to reduce the selection of fields or you can use below query: 

db.col.aggregate([
  {
    $match: {
      companyId: "xxx"
    }
  },
  {
    $project: {
      _id: 0,
      data: {
        $reduce: {
          input: "$items.status",
          initialValue: [

          ],
          in: {
            $concatArrays: [
              "$$this",
              "$$value"
            ]
          }
        }
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      newRoot: "$data"
    }
  }
])

Upvotes: 0

Nishant Bhardwaz
Nishant Bhardwaz

Reputation: 1001

In addition to the @mickl answer, you can add $project pipeline to get the result as a flat list of status and count.

db.collectionName.aggregate([
    {
        $match: { companyId: "xxx" }
    },
    {
        $unwind: "$items"
    },
    {
        $unwind: "$items.status"
    },
    {
        $replaceRoot: {
            newRoot: "$items.status"
        }
    },
    {
        $group: {
            _id: "$status",
            count: { $sum: 1 }
        }
    },
    {
        $project: {
            "status":"$_id", 
            "count":1,
            _id:0
        }
    }
])

Upvotes: 0

mickl
mickl

Reputation: 49985

You can use below aggregation:

db.col.aggregate([
    {
        $match: { companyId: "xxx" }
    },
    {
        $unwind: "$items"
    },
    {
        $unwind: "$items.status"
    },
    {
        $replaceRoot: {
            newRoot: "$items.status"
        }
    },
    {
        $group: {
            _id: "$status",
            count: { $sum: 1 }
        }
    }
])

Double $unwind will return single status per document and then you can use $replaceRoot to promote each status to root level of your document.

Additionally you can add $group stage to count documents by status.

Upvotes: 1

Related Questions