CODEWITHSUNDEEP
pythonsqlmongodbpymongo
xaroulis gekas
xaroulis gekas

Reputation: 165

Conver "distinct on" query from Sql in Mongodb with Pymongo

I am trying to convert this query from sql:

select distinct on (id13) id13, timestamp1 
from oneindextwocolumnsfalse3years 
where timestamp1>='2010-01-01 00:00:00' and timestamp1<='2015-01-01 00:55:00' 
order by id13,timestamp1 desc

To mongodb like that:

mydb1.mongodbindextimestamp1.aggregate([

    {
        "$match": {
            "timestamp1": {"$gte": datetime.strptime("2010-01-01 00:00:00", "%Y-%m-%d %H:%M:%S"),
                          "$lte" :datetime.strptime("2015-01-01 00:55:00", "%Y-%m-%d %H:%M:%S")}
        }
    },

{
        "$group": {
            "_id":{
                "id_13":"$id13"
        },

      }
},
    {
        "$project": {
            "_id": 0,
            "id13":1,
            "timestamp1":1
        }
    },
    {"$sort": {"id13": 1,"timestamp1":-1}}

])

But it doesn't seems to work.Do you have something to suggest?I am doing something wrong but i cant find what!

Upvotes: 1

Views: 48

Answers (1)

Dĵ ΝιΓΞΗΛψΚ
Dĵ ΝιΓΞΗΛψΚ

Reputation: 5689

give this pipeline a try:

db.collection.aggregate(
    [
        {
            $match: {
                timestamp: {
                    $gte: ISODate("2020-01-01"),
                    $lte: ISODate("2022-01-01")
                }
            }
        },
        {
            $group: {
                _id: "$id13", //define the grouping key
                doc: { $first: "$$ROOT" } //pick the first doc from each group
            }
        },
        {
            $replaceWith: "$doc" //promote the doc to root
        },
        {
            $project: {
                _id: 0,
                id13: "$id13",
                timestamp: "$timestamp"
            }
        },
        {
            $sort: {
                id13: 1,
                timestamp: -1
            }
        }
    ]
)

https://mongoplayground.net/p/Cwzic2cMfgd

Upvotes: 1

Related Questions