Welcome to Tesla Motors Club
Discuss Tesla's Model S, Model 3, Model X, Model Y, Cybertruck, Roadster and More.
Register

MongoDB experts - help to extract records from TeslaMS?

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
(Deleted)

Post deleted... unnecessary.
 
Last edited:

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
Darn, I was hoping to pay you back for all the electrical help and advice!

As it turns out, I made the problem way too complex for what it was. I was reading about nested documents and all this crap I would need to do, when it was as simple as referring to the nested array elements with a period between them.
 

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
Okay, here's a new question where experts might be able to help. TeslaMS stores Tesla's streaming records like this:

{ "_id" : ObjectId("5664a620b4edb81004982776"), "ts" : 1449436711414, "record" : [ "1449436711414", "18", "66402.0", "86", "140", "92", "38.00", "-89.00", "-10", "D", "224", "204", "90" ] }

record is [ timestamp, speed, odometer, soc, elevation, est_heading, latitude, longitude, power, shift_state, range, est_range, heading ]

With "record" being a comma-separated list without labels, is there a way for me to use standard "find" commands (or any other commands) to search for records based on elements of the "record" list? For example, if I wanted to pull all records where the power list field is greater than 200, is there an easy way to do this without doing an export and re-import to a more structured document?

Thanks!
 

demundus

Active Member
Jul 5, 2015
1,328
857
Oceanside, CA
I'm a hands on person, really bad at explaining things abstract, however I believe this is what you need.

$elemMatch (query) MongoDB Manual 3.0

If you can't "teach yourself to fish" on this, and want to provide me a copy of the data, I could try to write a few things... else someone else can come in here and bang out an actual top-of-mind query.
 

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
I looked at that, but it seems to preclude the ability to search on one specific "field" in the record. I could use $elemMatch on record:, but it seems I can't be specific about the field - for example, asking for elemMatch with gte:300 to match on > 300 kW would return nearly every record as the odometer field has been over 300 for a very long time.

I'm thinking I'll have to refactor the database to give each element of record: its own field identifier.
 

demundus

Active Member
Jul 5, 2015
1,328
857
Oceanside, CA
I think that will be the best route. Querying nodes is a nightmare as it is. Refactoring it into a ANSI database of some sort will probably make your life alot easier. Alternatively you could push it to XML and query it there.
 

redox

Member
Sep 13, 2014
243
14
SF Bay Area
Okay, here's a new question where experts might be able to help. TeslaMS stores Tesla's streaming records like this:

{ "_id" : ObjectId("5664a620b4edb81004982776"), "ts" : 1449436711414, "record" : [ "1449436711414", "18", "66402.0", "86", "140", "92", "38.00", "-89.00", "-10", "D", "224", "204", "90" ] }

record is [ timestamp, speed, odometer, soc, elevation, est_heading, latitude, longitude, power, shift_state, range, est_range, heading ]

With "record" being a comma-separated list without labels, is there a way for me to use standard "find" commands (or any other commands) to search for records based on elements of the "record" list? For example, if I wanted to pull all records where the power list field is greater than 200, is there an easy way to do this without doing an export and re-import to a more structured document?

Thanks!

db.tesla.find( { "record.8" : { $gt : "200" } } )
 

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
db.tesla.find( { "record.8" : { $gt : "200" } } )

Close, but the problem with the comparison here is that it's a string comparison, so this will also match "21".

For > 200, you have to use something like this:
db.tesla_stream.find({ "record.8": { $regex: /^[2-9]..$/ } })

This also means that things become complex if you want to do something, say > 250:
db.tesla_stream.find({ "record.8": { $regex: /^[2-9]..$/, $gte: "250" } })

Or let's say you want to pull out all records of supercharging at > 110 kW:
db.tesla_stream.find({ "record.8": { $regex: /^-1..$/, $gte: "-110" } })

If only there were a way to cast... (int)"record.8" would be nice. :)

You helped me figure it out - I can now pull power records... thanks!

And as an interesting trivia note, my car has supercharged at a maximum of 123 kW...

Code:
> db.tesla_stream.count({ "record.8": { $regex: /^-1..$/, $gte: "-123" } })
8
> db.tesla_stream.count({ "record.8": { $regex: /^-1..$/, $gte: "-124" } })
0
 
Last edited:

redox

Member
Sep 13, 2014
243
14
SF Bay Area
Close, but the problem with the comparison here is that it's a string comparison, so this will also match "21".

I was afraid of that, and bad me only tested with a couple of test cases that went well (where the string compare did the right thing)... No QA job for me!
However, have you tried http://stackoverflow.com/questions/3521601/is-it-possible-to-cast-in-a-mongodb-query ? It seems like it would do the right thing...

Something like: db.tesla.find("record.8 > 200") ?




 

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
I was afraid of that, and bad me only tested with a couple of test cases that went well (where the string compare did the right thing)... No QA job for me!
However, have you tried http://stackoverflow.com/questions/3521601/is-it-possible-to-cast-in-a-mongodb-query ? It seems like it would do the right thing...

Something like: db.tesla.find('record.8 > 200') ?

EDIT: Oh FFS. I give up. This installation of vBulletin does something really stupid in displaying the code below. After editing, it shows properly the first time; then click reload, and it butchers the lines with greater-than symbols by chopping out a few characters. Not even NOPARSE works. If you're using any browser (reproduced on iPad, Windows, and Mac using Safari, Chrome, Firefox, and Edge), click "Reply with Quote" if you want to look at the good code. If viewing via Tapatalk, you'll see it ok.

The proper syntax would be:

db.tesla_stream.find('this.record[8] > 200')

Just to see what performance might look like, I ran a query:

db.tesla_stream.find('this.record[8] > 400');

...and it ran for a few minutes! My guess is that the performance would be so horrible trying to use this method anyway, so I'll stick to regex. Using Javascript comparisons requires the Javascript engine to evaluate EVERY document in the collection, from what I'm reading. With 10.6M records in my streaming database, that's not going to be pretty.
 
Last edited:

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
Some performance comparisons:

Code:
[NOPARSE]
112:~>time mongo tesla --eval 'db.tesla_stream.find({ "record.8": { $regex: /^[4-9]..$/, $gte: "400" } }).forEach(printjson)'
MongoDB shell version: 3.0.7
connecting to: tesla
0.048u 0.011s 0:16.40 0.3%      0+0k 880+0io 6pf+0w
[/NOPARSE]

vs.

Code:
[NOPARSE]
113:~>time mongo tesla --eval 'db.tesla_stream.find("this.record[8] > 400").forEach(printjson)'
MongoDB shell version: 3.0.7
connecting to: tesla
0.054u 0.004s 4:46.99 0.0%      0+0k 992+0io 6pf+0w
[/NOPARSE]

So for 10.6M records, 16.4 seconds vs. 4 minutes, 47 seconds.

$regex it is. :)

EDIT: Modified to add NOPARSE tags so vBulletin will stop its insanity. But the insanity continues.
 
Last edited:

FlasherZ

Sig Model S + Sig Model X + Model 3 Resv
Jun 21, 2012
7,028
1,014
Cool. As a sanity check, you may want to ensure that the 2 queries return the same set of documents...

In this case it does, but there are a few cases in which life gets more difficult. For example, if you wanted all records with value less than 230, you would have to use a more complex selection - you'd need all records that start with "-", along with single-digit and two-digit numbers, and then you'd need all three-digit records with string value $lt 230 (but don't apply that criteria to negative, single-digit, and double-digit records, or you'll lose some).

In that case, it may be easier just to use the Javascript and wait 5 minutes compared to building a more complex query. :)

There may be a way to optimize this a bit better (it's quite late for me), but here's what I come up with for getting values less than 230:

db.tesla_stream.find({ $or: [ { "record.8" : { $regex: /^[0-9]{1,2}$/ }} , { "record.8" : { $regex: /^-/ } } , { "record.8" : { $regex: /^[0-9]..$/ , $lte: "230" } } ] } )
 
Last edited:

About Us

Formed in 2006, Tesla Motors Club (TMC) was the first independent online Tesla community. Today it remains the largest and most dynamic community of Tesla enthusiasts. Learn more.

Do you value your experience at TMC? Consider becoming a Supporting Member of Tesla Motors Club. As a thank you for your contribution, you'll get nearly no ads in the Community and Groups sections. Additional perks are available depending on the level of contribution. Please visit the Account Upgrades page for more details.


SUPPORT TMC
Top