MongoDB nested Aggregate Grouping

Question

Sample Data:

[
    {type: 'partial', jobId: '121', browser: 'chrome', status:'true', jobName:'one'},
    {type: 'partial', jobId: '122', browser: 'chrome', status:'false', jobName:'two'},
    {type: 'partial', jobId: '121', browser: 'firefox', status:'false', jobName:'one'},
    {type: 'partial', jobId: '122', browser: 'firefox', status:'true', jobName:'two'},
    {type: 'full', jobId: '123', browser: 'chrome', status:'true', jobName:'three'},
    {type: 'full', jobId: '123', browser: 'chrome', status:'true', jobName:'three'},
    {type: 'full', jobId: '123', browser: 'chrome', status:'false', jobName:'three'},
    {type: 'full', jobId: '124', browser: 'firefox', status:'false', jobName:'four'},
]

Output Needed:

[
  {
    "type": "partial",
    "browsers": [
      {
        "browser": "chrome",
        "jobIds": [
          {
            "jobId": "121",
            "results": [
              {
                "jobName": "one",
                "status": "true",
              },
            ]
          },
          {
            "jobId": "122",
            "results": [
              {
                "jobName": "two",
                "status": "false"
              },
            ]
          }
        ]
      },
      {
        "browser": "firefox",
        "testIds": [
          {
            "jobId": "121",
            "results": [
              {
                "jobName": "one",
                "status": "false"
              },
            ]
          },
          {
            "jobId": "122",
            "results": [
              {
                "jobName": "two",
                "status": "true"
              },
            ]
          }
        ]
      }
    ]
  },
  {
    "type": "full",
    "browsers": [
      {
        "browser": "chrome",
        "jobIds": [
          {
            "jobId": "123",
            "results": [
              {
                "jobName": "three",
                "status": "true"
              },
              {
                "jobName": "three",
                "status": "true"
              },
              {
                "jobName": "three",
                "status": "false"
              }
            ]
          },
        ]
      },
      {
        "browser": "firefox",
        "testIds": [
          {
            "jobId": "124",
            "results": [
              {
                "jobName": "four",
                "status": "false"
              },
            ]
          },
        ]
      }
    ]
  }
]

I understand how to use group, but then I don't understand how to make the nested grouping. I tried the below query, it is not fetching needed results, I don't know how to proceed further.

   db.collection.aggregate([
  {
    $match: {
      jobId: {
        "$exists": true
      }
    }
  },
  {
    $sort: {
      _id: -1
    }
  },
  {
    $group: {
      _id: {
        type: "$type",
        browser: "$browser",
        jobId: "$jobId"
      },
      results: {
        $push: {
          jobName: "$jobName",
          status: "$status",
          type: "$type",
          jobId: "$jobId"
        }
      }
    }
  },
  {
    $addFields: {
      results: {
        $slice: [
          "$results",
          30
        ]
      }
    }
  },
  {
    $group: {
      _id: "$_id.browser",
      results: {
        $push: {
          results: "$results"
        }
      }
    }
  },
  
])

Need fetch recent 30 results, that's why I added $addFields in query.

https://mongoplayground.net/p/pt3H1O445GA

it seems partial vs total and then browsers on each of those, grouping by browser and task @turivishal — user12582392
– user12582392, Commented Dec 8, 2020 at 10:06
do you only have 2 browsers, or you need a general solution? @hardcode — user12582392
– user12582392, Commented Dec 8, 2020 at 10:23
need a general solution, not particularly two browsers and jobId also not anything particular — hardcode
– hardcode, Commented Dec 8, 2020 at 10:24

turivishal · Accepted Answer · 2020-12-08 10:31:45Z

2

$group by type, browser and jobId and make results array
$group by type and browser and make jobs array
$group by type and make browsers array

db.collection.aggregate([
  { $match: { jobId: { $exists: true } } },
  { $sort: { _id: -1 } },
  {
    $group: {
      _id: {
        type: "$type",
        browser: "$browser",
        jobId: "$jobId"
      },
      results: {
        $push: {
          jobName: "$jobName",
          status: "$status"
        }
      }
    }
  },
  { $addFields: { results: { $slice: ["$results", 30] } } },
  {
    $group: {
      _id: {
        type: "$_id.type",
        browser: "$_id.browser"
      },
      browser: { $first: "$_id.browser" },
      jobIds: {
        $push: {
          jobId: "$_id.jobId",
          results: "$results"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.type",
      type: { $first: "$_id.type" },
      browsers: {
        $push: {
          browser: "$_id.browser",
          jobIds: "$jobIds"
        }
      }
    }
  },
  { $project: { _id: 0 } }
])

Playground

answered Dec 8, 2020 at 10:31

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

4 Comments

hardcode Over a year ago

In "jobIds" group whether it is possible to sort it by "jobName"?

turivishal Over a year ago

in second stage pass sorting for jobName like { $sort: { _id: -1, jobName: 1 } },

hardcode Over a year ago

I added $sort after second stage, but it is not working mongoplayground.net/p/ipQ_zXsmudL

turivishal Over a year ago

Check your playgorund again becuase you have added $sort stage in last second stage, you need to add form first second stage look playground

Collectives™ on Stack Overflow

MongoDB nested Aggregate Grouping

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related