New to MongoDB; how do I query this document?

I would like to Select just one of the counties; I am trying “name.ByCounty.County” : “King”. But I get no data in the result set.

In addition, how do I access elements of the array of DailyStates that is nested in the by county data

The document is structured as follows:

{
    "_id": {
        "$oid": "5eba4e63e4c68d0631a26bbe"
    },
    "name": "Washington",
    "byCounty": [{
        "DailyStats": [{
            "TotalConfirmed": 5174,
            "TotalDeaths": 346,
            "DailyConfirmed": 272,
            "DailyDeaths": 15,
            "Date": "4/19/2020"
        }, {
            "TotalConfirmed": 5174,
            "TotalDeaths": 346,
            "DailyConfirmed": 0,
            "DailyDeaths": 0,
            "Date": "4/20/2020"
        }, {
            "TotalConfirmed": 5293,
            "TotalDeaths": 360,
            "DailyConfirmed": 119,
            "DailyDeaths": 14,
            "Date": "4/21/2020"
        }, {
            "TotalConfirmed": 5379,
            "TotalDeaths": 373,
            "DailyConfirmed": 86,
            "DailyDeaths": 13,
            "Date": "4/22/2020"
        }, {
            "TotalConfirmed": 5532,
            "TotalDeaths": 385,
            "DailyConfirmed": 153,
            "DailyDeaths": 12,
            "Date": "4/23/2020"
        }],
        "StateOrTerritory": "US",
        "Latitude": 47.49137892,
        "Longitude": -121.8346131,
        "County": "King",
        "Population": 2252782
    }, {
        "DailyStats": [{
            "TotalConfirmed": 804,
            "TotalDeaths": 36,
            "DailyConfirmed": 45,
            "DailyDeaths": 2,
            "Date": "4/19/2020"
        }, {
            "TotalConfirmed": 835,
            "TotalDeaths": 36,
            "DailyConfirmed": 31,
            "DailyDeaths": 0,
            "Date": "4/20/2020"
        }, {
            "TotalConfirmed": 868,
            "TotalDeaths": 38,
            "DailyConfirmed": 33,
            "DailyDeaths": 2,
            "Date": "4/21/2020"
        }, {
            "TotalConfirmed": 886,
            "TotalDeaths": 38,
            "DailyConfirmed": 18,
            "DailyDeaths": 0,
            "Date": "4/22/2020"
        }, {
            "TotalConfirmed": 879,
            "TotalDeaths": 41,
            "DailyConfirmed": -7,
            "DailyDeaths": 3,
            "Date": "4/23/2020"
        }],
        "StateOrTerritory": "US",
        "Latitude": 46.45738486,
        "Longitude": -120.7380126,
        "County": "Yakima",
        "Population": 250873
    }]
}

Hello @Mark_Friedman :wave:

I would like to Select just one of the counties

To select and print an element of the byCounty array field, use the $ array projection operator.

how do I access elements of the array of DailyStates that is nested in the by county data

What is it you want do with the elements of the byCounty.DailyStats nested array? Any specific query you are trying?

1 Like

compare one day’s activity to the next;
calculate a 5-day moving average of the data

To print the details for County = "King", use the query:

db.collection.find( 
  { "byCounty.County": "King" }, 
  { "byCounty.$": 1 }
).pretty()

To get an average you use an Aggregation Framework query. The five days (for averages) are specified with a range of dates by START_DT and END_DT. You will use the Date object type rather than the string date the Date field has for date comparison.

COUNTY = "King"
START_DT = ISODate("2020-04-19T00:00:00Z")
END_DT = ISODate("2020-04-23T00:00:00Z")

db.collection.aggregate( [
  { 
      $unwind: "$byCounty" 
  },
  { 
      $unwind: "$byCounty.DailyStats" 
  },
  { 
      $match: { 
          $expr: { $and: [ 
                     { $gte: [ { $toDate: "$byCounty.DailyStats.Date" }, START_DT  ] },
                     { $lte: [ { $toDate: "$byCounty.DailyStats.Date" }, END_DT  ] }
          ] }, 
          "byCounty.County": COUNTY  
      }
  },
  { 
      $group: { 
          _id: "Average", 
          average_daily_deaths: { $avg: "$byCounty.DailyStats.DailyDeaths" } 
      } 
  }
] )

The output:

{ "_id" : "Average", "average_daily_deaths" : 10.8 }

Hi @Mark_Friedman,

For ideas on how to get moving averages, take a look at this SO answer by Buzz Moschetti (ex MongoDB Architect), which uses the aggregation framework.

Unlike the example that Prasad gave above (which is a good example of getting the average of a group of documents), the SO answer will give you averages for every x consecutive days in your data set. If you have 50 documents (1 document per day) and want 5 day moving averages, then you will get 46 resulting documents back with an average of the proceeding 5 days.

1 Like