How do i search and query based on time?

Working with Data
,
1

Here is a document in my collection in mongodb 4.4.12(could not use latest 5.0.6 because of the Intel AVX support required by mongodb 5.0.0 and above

I really want to use the native mongodb time series but for now i can’t
Now i got the context out of the way

Now back to the document, here it is

{
    _id: ObjectId('61fbeb4e41691f4d9f012434'),
    time_stamp: ISODate('2022-02-03T14:48:11.000Z'),
    trading_pair: '1INCH-BTC',
    price: 0.0000439,
    status: 'online',
    trading_disabled: false
}

and these documents get inserted every 1 minute, sometimes a minute is skipped and 2 minute range before next insert, even though i run the cronjob every 1 minute, but many factors that does not allow every minute always

{
    _id: ObjectId('61fbeb4e41691f4d9f012432'),
    time_stamp: ISODate('2022-02-03T14:45:11.000Z'),
    trading_pair: '1INCH-BTC',
    price: 0.0000437,
    status: 'online',
    trading_disabled: false
},
{
    _id: ObjectId('61fbeb4e41691f4d9f012433'),
    time_stamp: ISODate('2022-02-03T14:47:11.000Z'),
    trading_pair: '1INCH-BTC',
    price: 0.0000438,
    status: 'online',
    trading_disabled: false
},
{
    _id: ObjectId('61fbeb4e41691f4d9f012434'),
    time_stamp: ISODate('2022-02-03T14:48:11.000Z'),
    trading_pair: '1INCH-BTC',
    price: 0.0000439,
    status: 'online',
    trading_disabled: false
},

Please note i insert many documents all at once using same exact ISODate, so i can have like 100 documents all using same ISODate with different and unique values for trading_pair…which is why i want to query by date.

Eventually i will move over to using the native timeseries MongoDB's New Time Series Collections | MongoDB

What i want to do is be able to

  1. I want to be able to return only the most recent single document when i search. So it will return all documents from the most recent InsertMany …so i can always get the most recent price data for all trading_pair

  2. How do i perform percentage change to calculate percentage price difference for a trading_pair between different ISODate for like last 5 minutes, 24 hours, 7 days? This part is the one i really want to see how to do. I am new to MongoDB , in the sense i haven’t used in PRODUCTION app before but now it is that time to fo it, and i want to do it right(need your help here)

2

for context i am using the mongodb golang driver GitHub - mongodb/mongo-go-driver: The Official Golang driver for MongoDB incase that changes anything

If you need clarifications on anything i can add details

Thanks in advance

3

Hello @Bradley_Benjamin, welcome to the MongoDB Community forum!

This can be used for your first query - get all the trading pairs with the price at the latest date.

db.collection.aggregate([
  { 
    $sort: { trading_pair: 1, time_stamp: -1 } 
  },
  { 
    $group: { 
        _id:  "$trading_pair",
        latest_date: { $first: "$time_stamp" },
        recent_price:  { $first: "$price" }
    }
  }
])
1 Like
4

@Prasad_Saya

I think i want to sort by time_stamp alone, trading_pairs will not be an index, like i mentioned…there will be multiple rows of documents inserted using InsertMany with same ISODate, so am thinking time_stamp will eb the only secondary index

Am i right? in that case how will the query look like? By the way i will have to convert these queries to the mongo-go-driver version

5

@Bradley_Benjamin, I can see the output might look like this from the above query. Is that what you are looking for?

{
        "_id" : "1INCH-BTC",
        "latest_date" : ISODate("2022-02-03T14:49:11Z"),
        "recent_price" : 439
}
{
        "_id" : "ANOTHER",
        "latest_date" : ISODate("2022-02-03T14:49:11Z"),
        "recent_price" : 999
}
6

But what documents with same date looks like, and i want to return every field except for ObjectId

{
    _id: ObjectId('61fbeb4e41691f4d9f012432'),
    time_stamp: ISODate('2022-02-03T14:45:11.000Z'),
    trading_pair: 'ONE-ONE',
    price: 0.0000437,
    status: 'online',
    trading_disabled: false
},
{
    _id: ObjectId('61fbeb4e41691f4d9f012433'),
    time_stamp: ISODate('2022-02-03T14:45:11.000Z'),
    trading_pair: 'TWO-TWO',
    price: 0.0000438,
    status: 'online',
    trading_disabled: false
},
{
    _id: ObjectId('61fbeb4e41691f4d9f012434'),
    time_stamp: ISODate('2022-02-03T14:45:11.000Z'),
    trading_pair: 'THREE-THREE',
    price: 0.0000439,
    status: 'online',
    trading_disabled: false
},

so i want something like this from query, to return latest InsertMany documents

{
    time_stamp: ISODate('2022-02-03T14:45:11.000Z'),
    trading_pair: 'ONE-ONE',
    price: 0.0000437,
    status: 'online',
    trading_disabled: false
},
{
    time_stamp: ISODate('2022-02-03T14:45:11.000Z'),
    trading_pair: 'TWO-TWO',
    price: 0.0000438,
    status: 'online',
    trading_disabled: false
},
{
    time_stamp: ISODate('2022-02-03T14:45:11.000Z'),
    trading_pair: 'THREE-THREE',
    price: 0.0000439,
    status: 'online',
    trading_disabled: false
},
7

Anyway to edit my post after i post? New to the forum.
I do not see an edit button

8

@Bradley_Benjamin, try this query to get all the fields, except the _id.

db.test.aggregate([
  { 
    $sort: { trading_pair: 1, time_stamp: -1 } 
  },
  { 
    $group: { 
        _id:  "$trading_pair",
        latest: { $first: "$$ROOT" },
    }
  },
  {
     $replaceWith: "$latest"
  },
  {
      $project: { _id: 0 }
  }
])
1 Like
9

Any reason why you still using trading_pair as sort? order does not matter, as long as i get all documents based on the latest ISODate i am fine

I need the way i want because the result will be consumed by another API

10

Here is what am using to return all documents in the collection

collection.Find(context.Background(), filter, options.Find().SetProjection(option))

using GitHub - mongodb/mongo-go-driver: The Official Golang driver for MongoDB

So will have to modify to use your query format

11

That is because a trading pair may have more than one document (with different dates) and you want the latest one (one document only). The group stage gives you one document per trading pair with the latest date.

12

There is going to be only 1 trading_pair per InsertMany based on time_stamp, there wont be more than 1. The timestamp is used to group all documents separated by timesstamp

which is why all i need is to return all documents for latest time_stamp

13

Just clarify this one thing for me. Can the collection have more than one document with the same trading pair and different timestamps?

15

yes, separated by unique time_stamp that groups all documents belonging to a time together

to clarify, there is a cronjob every minute that does bulk InsertMany of like 100 documents with same exact ISODDate time_stamp

Think of it like getting the current price of a crypto based on the time_stamp, so i can return all documents of latest time_stamp so i can extract price data from it

16

@Bradley_Benjamin, the golang version of the same aggregation query:

sortStage := bson.D{{"$sort", bson.D{{"trading_pair", 1}, {"time_stamp", -1}}}}
groupStage := bson.D{{"$group", bson.D{{"_id","$trading_pair"}, {"latest", bson.D{{"$first", "$$ROOT"}}}}}}
replaceStage := bson.D{{"$replaceWith", "$latest"}}
projectStage := bson.D{{"$project", bson.D{{"_id", 0}}}} 

cursor, err := collection.Aggregate(ctx, mongo.Pipeline{sortStage, groupStage, replaceStage, projectStage})

var results []bson.M

if err != nil {
    fmt.Println("Failed to Aggregate: ", err)
}
if err = cursor.All(ctx, &results); err != nil {
    fmt.Println("cursor.All() error:", err)
}

fmt.Println(results)
2 Likes
17

ok will test this in a bit…thanks for the help

Any ideas of how to approach the #2 question?

  1. How do i perform percentage change to calculate percentage price difference for a trading_pair between different ISODate for like last 5 minutes , 24 hours , 7 days ? This part is the one i really want to see how to do. I am new to MongoDB , in the sense i haven’t used in PRODUCTION app before but now it is that time to fo it, and i want to do it right(need your help here)
18

Lets take the scenario for the calculation of percentage change in the last 5 mins. I think, another idea is to take an example scenario where the change is calculated between the latest time stamp and an hour before that. What is the calculation for the percentage change of the price between the values of these two time stamps?

The first information needed is, what formula are you using for this. I Googled generally with this search string “percentage change based on time formula” and found some sites explaining the calculation. I’d like to know what is the formula you want to use (or you have on your mind)? That, can be applied to build a query.

19

sorry i was blocked from posting for another 20 hours, for posting too much for first day on forum

the query for getting latest documents by latest time_stamp worked great…thanks for that

regarding the percentage change calculation

5 minutes percentage change:

Percent change = [(current latest value - value 5 minutes ago)/value 5 minutes ago] * 100

example:

1
current latest value = 45
value 5 minutes ago = 40

Percent change = [(45 - 40)/40] * 100 = 12.5
that is 12.5% up

2
current latest value = 45
value 5 minutes ago = 50

Percent change = [(45 - 50)/50] * 100 = -10
that is 10% down

and so on for other periods like 24 hours, 7 days etc

20

@Bradley_Benjamin, here is the aggregation query which can return a result like follows for a trading pair (assuming relevant data exists):

{
        "latest_time_stamp" : ISODate("2022-02-03T14:45:00Z"),
        "latest_price" : 999,
        "trading_pair" : "2INCH-BTC",
        "prev_time_stamp" : ISODate("2022-02-03T14:40:00Z"),
        "prev_price" : 678,
        "percent_change" : 47.34513274336283
}

The Query:

db.collection.aggregate([
  { 
    $sort: { trading_pair: 1, time_stamp: -1 } 
  },
  { 
    $group: { 
        _id:  "$trading_pair",
        docs: { $push: "$$ROOT" }, 
        latest_time_stamp: { $first: "$time_stamp" }, 
        latest_price: { $first: "$price" }
    }
  },
  { 
    $addFields: {
        prev_doc: {
           $arrayElemAt: [
               { $filter: {
                      input: "$docs", 
                      as: "doc",
                      cond: { $eq: [ "$$doc.time_stamp", { $subtract: [ "$latest_time_stamp", 5 * 60 * 1000 ] } ] }
                }}, 0
           ]
        }
    }
  },
  {
     $project: {
         trading_pair: "$_id",
         _id: 0,
         latest_time_stamp: 1,
         latest_price: 1,
         prev_time_stamp: "$prev_doc.time_stamp",
         prev_price: "$prev_doc.price",
         percent_change: {
             $divide: [ { $multiply: [ { $subtract: [ "$latest_price", "$prev_doc.price" ] }, 100 ] }, "$prev_doc.price" ]
         }
    }
  },
])
2 Likes
21

can you help post the query with the mongo-go-driver? like the last one you helped with
thanks