1

MongoDB version: 3.2.11

Coming from using primarily PostgreSQL, I am new to MongoDB and am having trouble constructing complex queries in the required JSON format (where in SQL I would use CTEs). To provide a simplified example - there are a number of cars with their own unique identifier vin. The cars ping when they are reserved, and when they are not reserved.

I have a number Objects in my collection engine that are structured like this:

{
    "_id" : ObjectId("1"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329250,
        "status" : "LOCKED"
    }
}
{
    "_id" : ObjectId("2"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329260,
        "status" : "LOCKED"
    }
}
{
    "_id" : ObjectId("3"),
    "vin" : "12345",
    "class_name" : "bar",
    "decoded_payload" : {
        "timestamp" : 1547329270,
        "status" : "UNLOCKED",
        "reservation_id" : "71"

    }
}
{
    "_id" : ObjectId("4"),
    "vin" : "12345",
    "class_name" : "baz",
    "decoded_payload" : {
        "timestamp" : 1547329280,
        "status" : "UNLOCKED",
        "reservation_id" : "71"

    }
}    
{
    "_id" : ObjectId("5"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329290,
        "status" : "NO SIGNAL"

    }
}
{
    "_id" : ObjectId("6"),
    "vin" : "12345",
    "class_name" : "foo",
    "decoded_payload" : {
        "timestamp" : 1547329300,
        "status" : "LOCKED"

    }
}

Records produced outside of a reservation are always of class foo (those during a reservation can vary but are never foo)

I am interested only in retrieving the status immediately before and immediately after each reservation on each vin. This can be retrieved by taking the closest record of class foo to the min timestamp per reservation_id and vin, as my objective is to retrieve all of the before/after statuses for each reservation_id made on each vin

The output for the above example would be something like

{"vin": "1234", "reservation_id": "71", "pre_status": "LOCKED", "post_status": "NO SIGNAL"}

So far I have this to get the reservations themselves, but would appreciate any recommendations on how to proceed further - would a forEach statement be appropriate in the above case?

db.getCollection('engine').find({"decoded_payload.reservation_id": {$exists: true}})

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.