TMC is an independent, primarily volunteer organization that relies on ad revenue to cover its operating costs. Please consider whitelisting TMC on your ad blocker or making a Paypal contribution here: paypal.me/SupportTMC

MongoDB experts - help to extract records from TeslaMS?

Discussion in 'Model S: User Interface' started by FlasherZ, Nov 18, 2015.

  1. FlasherZ

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    #1 FlasherZ, Nov 18, 2015
    Last edited: Nov 18, 2015
    (Deleted)

    Post deleted... unnecessary.
     
  2. demundus

    demundus Member

    Joined:
    Jul 5, 2015
    Messages:
    269
    Location:
    Oceanside, CA
    Darn, I was hoping to pay you back for all the electrical help and advice!
     
  3. FlasherZ

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    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.
     
  4. FlasherZ

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    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!
     
  5. demundus

    demundus Member

    Joined:
    Jul 5, 2015
    Messages:
    269
    Location:
    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.
     
  6. FlasherZ

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    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.
     
  7. demundus

    demundus Member

    Joined:
    Jul 5, 2015
    Messages:
    269
    Location:
    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.
     
  8. redox

    redox Member

    Joined:
    Sep 13, 2014
    Messages:
    199
    Location:
    SF Bay Area
    db.tesla.find( { "record.8" : { $gt : "200" } } )
     
  9. FlasherZ

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    #9 FlasherZ, Dec 14, 2015
    Last edited: Dec 14, 2015
    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
    
     
  10. redox

    redox Member

    Joined:
    Sep 13, 2014
    Messages:
    199
    Location:
    SF Bay Area
    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") ?




     
  11. FlasherZ

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    #11 FlasherZ, Dec 14, 2015
    Last edited: Dec 14, 2015
    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:

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

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

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    #12 FlasherZ, Dec 14, 2015
    Last edited: Dec 14, 2015
    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.
     
  13. redox

    redox Member

    Joined:
    Sep 13, 2014
    Messages:
    199
    Location:
    SF Bay Area
    Cool. As a sanity check, you may want to ensure that the 2 queries return the same set of documents...
     
  14. FlasherZ

    FlasherZ Sig Model S + Sig Model X + Model 3 Resv

    Joined:
    Jun 21, 2012
    Messages:
    7,019
    #14 FlasherZ, Dec 15, 2015
    Last edited: Dec 15, 2015
    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:

     

Share This Page