Convert Sql query to Mongodb query

[harris (harris gekas)]

i have a query in sql that i want to convert in mongodb with python pymongo.

cursor=execute("select avg(id13)::numeric(10,2) from timestamppsql where timestamp1<='2011-01-01 00:05:00'")

This is the code i tested in mongodb:

cursor=mydb1.mongodbtime.aggregate({
    '$group': {
        "timestamp1":{ "$lte" : datetime.strptime("2011-01-01 00:05:00", "%Y-%m-%d %H:%M:%S") },

        "avg_id13": {"$avg": "$id13"}
    }
})

The output is this:

pipeline must be a list

How can i fix that?Any help would be appreciated.

[steevej (Steeve Juneau)]

The pipeline must be a list. So it must starts with [ and ends with ]. Each object in the list is a stage. Simply add then around your object $group.

[Ronan_Merrick]

Hi,

To apply a filter, you need to use a $match stage. That’s like your WHERE clause in SQL.

You also need to wrap the pipeline inside square brackets.

Try this:

cursor = mydb1.mongodbtime.aggregate(
  [
      {
        "$match": {
           "timestamp1": {"$lte": datetime.strptime("2011-01-01 00:05:00", "%Y-%m-%d %H:%M:%S")}
        }
      },
      {
        "$group": {
          "_id": "null",
          "avg_id13": {
            "$avg": "$id13"
          }
        }
      }
    ]
)

[Mark_Smith]

Hi Harris,

@steevej and @Ronan_Merrick have both provided useful answers, so I won’t repeat anything they’ve said. I just wanted to drop in a maybe helpful suggestion that you check out the high-level documentation on the aggregation framework.

Conceptually a call to aggregate starts with all the documents in a collection, and then passes them through a series of aggregation stages which will do things like filter out documents (with $match for example), modify them (with stages like $project and $lookup or transform them into something else (with stages like $group). The documents output from each stage are passed to the next stage for more modification, until the end of the pipeline, where whatever documents are emitted from the final stage are then provided back to the client.

That’s why the stages are provided as a list - it’s a sequence of operations to be applied to the documents. It’s also why you need to do a $match to filter the documents that match your WHERE criteria, before doing a $group on the documents that remain, to get the average applied to all the documents, as @Ronan_Merrick suggested.

I hope this was helpful!

[harris (harris gekas)]

Thank you @Mark_smith , @steevej and @Ronan_Merrick you were all realy helpful!!