MongoDb count elements of array in array by $size

Question

I need help to count the elements of an array when it is in another array.

My command, when I tried to select the second array is,

db.artysci.aggregate([
   {
      $project: {
          _id:0,
         nazwa: 1,
         nazwisko: 1,
         numberOfSongs: { "album": {$size: "$utwor"}}
              
      }
   }
] )

Grid:

db.artysci.insert({
    imie: 'Laurie',
    nazwisko: 'Adkins',
    rok_debiutu: 2006,
    kraj_pochodzenia: ['Wielka Brytania'],
    gatunek: 'neo soul',
    album: [{
            tytul:"19",
            rok_edycji:2007,
            gatunek: 'pop',
            typ_nosnika: 'CD',
            utwor: [{
                    numer: 1,
                    tytul_utworu: 'Daydreamer',
                    dlugosc_trwania: 3.41
                    },
                    {
                    numer: 2,
                    tytul_utworu: 'Best for Last',
                    dlugosc_trwania: 4.19
                    },
                    {
                    numer: 3,
                    tytul_utworu: 'Chasing Pavements',
                    dlugosc_trwania: 3.31
                    }
                    ]
            }]
          })

Output when counting by $size:"$album",

{ 
    "nazwisko" : "Adkins", 
    "numberOfSongs" : {
        "album" : NumberInt(3)
    }
}

How can I count elements of an array in an array by $size?

halfer · Accepted Answer · 2020-06-28 12:43:28Z

1

You can achieve this using Map and then summing it up. It works..

db.artysci.aggregate({
    "$project": {
        _id: 0,
        nazwa: 1,
        nazwisko: 1,
        "numberOfAlbums": { "$size": { $ifNull: ["$album", []] } },
        "numberOfSongs": {
            "$sum": {
                "$map": {
                    "input": "$album",
                    "in": { "$size": { $ifNull: ["$$this.utwor", []] } }
                }
            }
        }
    }
})

edited Jun 28, 2020 at 12:43

halfer

20.2k20 gold badges111 silver badges208 bronze badges

answered Jun 28, 2020 at 9:30

Faizul Hassan

17012 bronze badges

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

1 Comment

Kacper Kwietniewski Over a year ago

Faizul Hassan, can you help me with one more thing, please? I need to do avg/60(in secounds) of all songs. Is it good way to do it? "avgOfSongTime": { "$avg": { "$map": { "input": "$album", "$map":{ "input": "$utwor", "in": { "$avg": { $ifNull: ["$$this.dlugosc_trwania", []] } } } } } }

Faizul Hassan · Accepted Answer · 2020-06-28 11:31:19Z

@Kacper,

Here is the soultion for your second question.

Yes, you can achieve it in either way, using the above method or using unwind and do the average..

Lets see an example using unwind:

Without divide/second:

 db.notifications.aggregate([
       { $unwind: "$album" },
       { $unwind: "$album.utwor" },
       {
          $group: {
             _id: "$_id",
             avgDuration: { $avg: "$album.utwor.dlugosc_trwania" }
          }
       },
    ]);

With divide/second:

db.notifications.aggregate([
   { $unwind: "$album" },
   { $unwind: "$album.utwor" },
   {
      $group: {
         _id: "$_id",
         avgDuration: { $avg: { $divide: ["$album.utwor.dlugosc_trwania", 60] } }
      }
   },
]);

Gibbs · Accepted Answer · 2020-06-28 09:30:31Z

0

You can use $unwind and $group to get the counts.

db.collection.aggregate([
  {
    $unwind: "$album"
  },
  {
    $unwind: "$album.utwor"
  },
  {
    $group: {
      _id: 0,
      total: {
        $sum: 1
      }
    }
  }
])

Play

If you need more information, add it to your question.

answered Jun 28, 2020 at 9:30

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

Collectives™ on Stack Overflow

MongoDb count elements of array in array by $size

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related