Group by multiple fields with pymongo

Working with Data Drivers
, ,
1

I have this query and i want to group by {"$dateToString": { "format": "%Y-%m-%d ", "date": "$first" } and "id13":"$samples.id13"
My data look like that:

{'_id': ObjectId('6068da8878fa2e568c42c7f1'),
 'first': datetime.datetime(2018, 1, 24, 14, 5),
 'last': datetime.datetime(2018, 1, 24, 15, 5),
 'maxid13': 12.5,
 'minid13': 7.5,
 'nsamples': 13,
 'samples': [{'c14': 'C',
              'id1': 3758.0,
              'id10': 0.0,
              'id11': 274.0,
              'id12': 0.0,
              'id13': 7.5,
              'id15': 0.0,
              'id16': 73.0,
              'id17': 0.0,
              'id18': 0.342,
              'id19': 6.3,
              'id20': 1206.0,
              'id21': 0.0,
              'id22': 0.87,
              'id23': 0.0,
              'id6': 2.0,
              'id7': -79.09,
              'id8': 35.97,
              'id9': 5.8,
              'timestamp1': datetime.datetime(2018, 1, 24, 14, 5),
              'timestamp2': datetime.datetime(2018, 1, 24, 9, 5)},
             {'c14': 'C',
              'id1': 3758.0,
              'id10': 0.0,
              'id11': 288.0,
              'id12': 0.0,
              'id13': 8.4,
              'id15': 0.0,
              'id16': 71.0,
              'id17': 0.0,
              'id18': 0.342,
              'id19': 6.3,
              'id20': 1207.0,
              'id21': 0.0,
              'id22': 0.69,
              'id23': 0.0,
              'id6': 2.0,
              'id7': -79.09,
              'id8': 35.97,
              'id9': 6.2,
              'timestamp1': datetime.datetime(2018, 1, 24, 14, 10),
              'timestamp2': datetime.datetime(2018, 1, 24, 9, 10)},
               .
               .
               .
               .

cursor=mydb1.mongodbbuckethour.aggregate([

    {
        "$match": {
            "first": {"$gte": datetime.strptime("2010-01-01 00:05:00", "%Y-%m-%d %H:%M:%S"),
                          "$lte" :datetime.strptime("2020-12-31 23:00:00", "%Y-%m-%d %H:%M:%S")}
        }
    },
    { "$unwind": "$samples" },
{
        "$match": {
            "first": {"$gte": datetime.strptime("2010-01-01 00:05:00", "%Y-%m-%d %H:%M:%S"),
                          "$lte" :datetime.strptime("2020-12-31 23:00:00", "%Y-%m-%d %H:%M:%S")}
        }
    },

{
        "$group": {
            "_id":{
                "date": {"$dateToString": { "format": "%Y-%m-%d ", "date": "$first" }},
                 "id13":"$samples.id13"
        }
      }
},
    {
        "$project": {
            "_id": 0,
            "day":"$date"

        }
    },
    {"$sort": {"day": -1}}

])

How to project only date and then sort by date?
What am i doing wrong?
Thanks in advance!

Edit: i did that and it seems to be working!:

},
    {
        "$project": {
            "_id": 0,
            "day":"$_id.date"

        }
    },
    {"$sort": {"day": -1}}

])
2

Hi Harris,

I’m not sure exactly what’s going wrong just from looking at it.

Are you hosting your database in Atlas? If so, you can use Atlas’s aggregation pipeline builder to build your pipeline stage by stage and see where things are going wrong. Once you get it working, you can export your pipeline to any language.

Another option is to use MongoDB Compass’s aggregation pipeline builder. Compass works regardless of where your database is hosted.

I never get a pipeline just right on the first try, so I really appreciate the visual pipeline builders.

If you can’t figure it out after trying a visual pipeline builder, can you post a couple of sample documents from your collection and tell us what you’re trying to achieve with the pipeline?

3

Thank you!I tried it but it doesnt seems to work.i updated my question with the documents as you told me to!

4

Woo hoo! So you’re good to go now? Can we mark this question as solved?

5

Yes of course!Thank you for your help!

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.