MongoDB experts - help to extract records from TeslaMS?

[FlasherZ]

(Deleted)

Post deleted... unnecessary.

 

[demundus]

Darn, I was hoping to pay you back for all the electrical help and advice!

 

[FlasherZ]

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]

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]

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]

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]

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]

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]

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...

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

 

[redox]

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]

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.

 

[FlasherZ]

Some performance comparisons:

[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.

[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.

 

[redox]

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

$regex it is.

Cool. As a sanity check, you may want to ensure that the 2 queries return the same set of documents...

 

[FlasherZ]

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" } } ] } )