Aggregate nested arrays

Question

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

mickl · Accepted Answer · 2019-03-16 12:35:26Z

1

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.

edited Mar 16, 2019 at 12:35

answered Mar 16, 2019 at 12:25

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nishant Bhardwaz · Accepted Answer · 2019-03-16 12:37:52Z

0

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
        }
    }
])

answered Mar 16, 2019 at 12:37

Nishant Bhardwaz

9919 silver badges23 bronze badges

Comments

Abhinandan Kothari · Accepted Answer · 2019-03-18 10:00:26Z

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"
    }
  }
])

Collectives™ on Stack Overflow

Aggregate nested arrays

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related