I have a data set that goes in and out of idle and I want to give each idle session an id
{
_id: ObjectId('67ba93d7f883e7c506027022'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:00:00.000Z'),
status: 'active',
location: { lat: 40.7128, lng: -74.006 },
prev_status: null,
prev_timestamp: null,
new_idle_session: 1
},
{
_id: ObjectId('67ba93d7f883e7c506027023'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:05:00.000Z'),
status: 'idle',
location: { lat: 40.713, lng: -74.007 },
prev_status: 'active',
prev_timestamp: ISODate('2025-02-22T10:00:00.000Z'),
new_idle_session: 1
},
{
_id: ObjectId('67ba93d7f883e7c506027024'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:10:00.000Z'),
status: 'idle',
location: { lat: 40.7135, lng: -74.008 },
prev_status: 'idle',
prev_timestamp: ISODate('2025-02-22T10:05:00.000Z'),
new_idle_session: 0
},
{
_id: ObjectId('67ba93d7f883e7c506027025'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:15:00.000Z'),
status: 'idle',
location: { lat: 40.714, lng: -74.009 },
prev_status: 'idle',
prev_timestamp: ISODate('2025-02-22T10:10:00.000Z'),
new_idle_session: 0
},
{
_id: ObjectId('67ba93d7f883e7c506027026'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:20:00.000Z'),
status: 'active',
location: { lat: 40.7145, lng: -74.01 },
prev_status: 'idle',
prev_timestamp: ISODate('2025-02-22T10:15:00.000Z'),
new_idle_session: 0
},
{
_id: ObjectId('67ba93d7f883e7c506027027'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:30:00.000Z'),
status: 'idle',
location: { lat: 40.715, lng: -74.011 },
prev_status: 'active',
prev_timestamp: ISODate('2025-02-22T10:20:00.000Z'),
new_idle_session: 1
},
{
_id: ObjectId('67ba93d7f883e7c506027028'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:40:00.000Z'),
status: 'idle',
location: { lat: 40.7155, lng: -74.012 },
prev_status: 'idle',
prev_timestamp: ISODate('2025-02-22T10:30:00.000Z'),
new_idle_session: 0
},
{
_id: ObjectId('67ba93d7f883e7c506027029'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T10:50:00.000Z'),
status: 'active',
location: { lat: 40.716, lng: -74.013 },
prev_status: 'idle',
prev_timestamp: ISODate('2025-02-22T10:40:00.000Z'),
new_idle_session: 0
},
{
_id: ObjectId('67ba93d7f883e7c50602702a'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T11:00:00.000Z'),
status: 'idle',
location: { lat: 40.7165, lng: -74.014 },
prev_status: 'active',
prev_timestamp: ISODate('2025-02-22T10:50:00.000Z'),
new_idle_session: 1
},
{
_id: ObjectId('67ba93d7f883e7c50602702b'),
device_id: 'ABC123',
timestamp: ISODate('2025-02-22T11:10:00.000Z'),
status: 'idle',
location: { lat: 40.717, lng: -74.015 },
prev_status: 'idle',
prev_timestamp: ISODate('2025-02-22T11:00:00.000Z'),
new_idle_session: 0
}
]
I would like every time a session id to be looking like this
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:00:00.000Z"),
"status": "active",
"location": { "lat": 40.7128, "lng": -74.006 },
"new_idle_session": 1,
"idle_session_id": 1
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:05:00.000Z"),
"status": "idle",
"location": { "lat": 40.713, "lng": -74.007 },
"new_idle_session": 1,
"idle_session_id": 1
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:10:00.000Z"),
"status": "idle",
"location": { "lat": 40.7135, "lng": -74.008 },
"new_idle_session": 0,
"idle_session_id": 1
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:15:00.000Z"),
"status": "idle",
"location": { "lat": 40.714, "lng": -74.009 },
"new_idle_session": 0,
"idle_session_id": 1
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:20:00.000Z"),
"status": "active",
"location": { "lat": 40.7145, "lng": -74.01 },
"new_idle_session": 0,
"idle_session_id": 1
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:30:00.000Z"),
"status": "idle",
"location": { "lat": 40.715, "lng": -74.011 },
"new_idle_session": 1,
"idle_session_id": 2
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:40:00.000Z"),
"status": "idle",
"location": { "lat": 40.7155, "lng": -74.012 },
"new_idle_session": 0,
"idle_session_id": 2
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T10:50:00.000Z"),
"status": "active",
"location": { "lat": 40.716, "lng": -74.013 },
"new_idle_session": 0,
"idle_session_id": 2
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T11:00:00.000Z"),
"status": "idle",
"location": { "lat": 40.7165, "lng": -74.014 },
"new_idle_session": 1,
"idle_session_id": 3
},
{
"device_id": "ABC123",
"timestamp": ISODate("2025-02-22T11:10:00.000Z"),
"status": "idle",
"location": { "lat": 40.717, "lng": -74.015 },
"new_idle_session": 0,
"idle_session_id": 3
}
]
And I used this but its setting it all to 4
"$setWindowFields": {
"partitionBy": "$device_id",
"sortBy": { "timestamp": 1 },
"output": {
"idle_session_id": {
"$sum": "$new_idle_session"
}
}
}
}
What can I partition by to get thie idle sessions that stay idle or end with idle to get numbered correctly from 1 to 3 versus all 4