Querying nested document structure in mongoDb

Question

I am trying to query below document structure

{
 _id: '7ja9sjkadja',
 parent: {
     parentName: 'Super',
     firstGenerations: [
         {
            name: 'First Generation',
            secondGenerations: [
            {
                 name: 'Second Generation 1',
                 value: 'Married'
            },

            {
                 name: 'Second Generation 2',
                 value: 'Single'
            },
            {
                 name: 'Second Generation 3',
                 value: 'Single'
            }

           ]
         }


     ]
 }

}

Expected output:

{
  firstGenerationName: 'First Generation',
  totalCount: 3
  values: [
     {
        categoryName: 'Married (1)',
        count: 1,
        firstGenerationName: 'First Generation'
     },

     {
        categoryName: 'Single (2)',
        count: 2,
        firstGenerationName: 'First Generation'
     }

    ]

  }

Query tried by me:

db.generations.aggregrate([

{ $project: { 'firstGenerations': '$parent.firstGenerations'} },
{ $unwind: '$firstGenerations'},
{ $unwind: '$firstGenerations.secondGenerations'}
{
   $group: {
      _id: '$_id',
      count: { 'sum': '$secondGenerations.value' },
      firstGenerationName: { $first: '$firstGenerations.name'} 
   }
}

])

I am able to unwind properly but not able to club group functionality by taking one value from parent array and count from second array.

Any help will be appreciated

mickl · Accepted Answer · 2020-01-22 16:33:07Z

Basically you need to run $group twice to get inner counts first:

db.collection.aggregate([
    {
        $unwind: "$parent"
    },
    {
        $unwind: "$parent.firstGenerations"
    },
    {
        $unwind: "$parent.firstGenerations.secondGenerations"
    },
    {
        $group: {
            _id: {
                fgName: "$parent.firstGenerations.name",
                sgValue: "$parent.firstGenerations.secondGenerations.value"
            },
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: "$_id.fgName",
            values: {
                $push: {
                    categoryName: { $concat: [ "$_id.sgValue", " (", { $toString: "$count" }, ")" ] },
                    count: "$count",
                    firstGenerationName: "$_id.fgName"
                }
            }
        }
    },
    {
        $project: {
            _id: 0,
            firstGenerationName: "$_id",
            values: 1,
            totalCount: { $sum: "$values.count" }
        }
    }
])

Mongo Playground

Collectives™ on Stack Overflow

Querying nested document structure in mongoDb

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related