CODEWITHSUNDEEP
regexmongodbnosqlmongodb-query
Sam
Sam

Reputation: 1

Mongo DB searching for occurances by date

So I've got a large dataset stored in my MonogDB of each time a song has been played in my itunes library, so each document is contains the artist name, song name, and date/time it was played. I currently am able to use the following query to search for the most occurances of a song in the database, which basically gives me the total number of times i had played it:

db.apple.aggregate([{ $sortByCount: "$song" }])

Returns:

{ "_id" : "Fireflies (feat. Grieves)", "count" : 336 }
{ "_id" : "Cinderella (feat. Ty Dolla $ign)", "count" : 267 }
{ "_id" : "Check", "count" : 241 }
{ "_id" : "100 Grandkids", "count" : 240 }
{ "_id" : "Late For the Sky (feat. Slug & Aesop Rock)", "count" : 226 }

This returns the total number of plays i have on a song, over the 5 years of plays i have in the database. What i was hoping to be able to do is create a query where it returns the total number of plays of a song for a specific year. I have the following query:

db.apple.find({"playTime" : {$regex : ".*2019*"}}).pretty()

This one returns all the songs that were played in a year but i can't figure out how i would combine these two queries.

Upvotes: 0

Views: 414

Answers (3)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

If playTime is a string of type ISO 8601 format, then you can try this :

db.apple.aggregate([{
    $match: {
        $expr: {
            $eq: [2019, {
                $year: {
                    $dateFromString: {
                        dateString: '$playTime'
                    }
                }
            }]
        }
    }
}, { $sortByCount: "$song" }])

Or in case if you can change it to/have ISODate() then :

db.apple.aggregate([{
    $match: {
        $expr: {
            $eq: [2019, {
                $year: '$playTime'
            }]
        }
    }
}, { $sortByCount: "$song" }])

Ref : $year,$dateFromString,$match or $isoWeekYear

Upvotes: 0

prasad_
prasad_

Reputation: 14297

Assuming playTime is a string data type ({ "playTime" : "2017-06-17T06:04:40.230Z" }), extract the first 4 characters of the string using the $substrCP and convert to an integer and match with an input year. The $sortByCount stage will remain as it is. The conversion to integer is optional; if not used the input year should be a string.

For example (using integer year):

var INPUT_YEAR = 2017

db.test.aggregate( [
  { 
      $match: { 
          $expr: { 
              $eq: [ INPUT_YEAR,  { $toInt: { $substrCP: [ "$playTime", 0, 4 ] } }  ]  
          } 
      } 
  },
  { 
      $sortByCount:  "$song" 
  }
] )

Upvotes: 1

sudhanva
sudhanva

Reputation: 709

Since you already have the queries ready, you just need to put them both in the same aggregation pipeline as JBone suggested in the comments. If your queries work as you have mentioned, this will do the trick:

db.apple.aggregate([
{ $sortByCount: "$song" },
{ $match: { "playTime" : {$regex : ".*2019*"} } }
])

Upvotes: 0

Related Questions