In my previous blog post, How Many Mk1 Toyota MR2s are still on the road, or currently being restored in the UK? we looked at how I used the UK’s MOT History API to download all the MOT data since 2005 and how I crudely filtered that down to just Toyota Mk1 MR2s and performed various analysis on that data in Excel.
At the time, I knew this was an inefficient way of doing the task and vowed to re-visit it when I set up a Mongo DB server. I have been meaning to familiarise myself with MongoDB for some time and this project accelerated that by providing a real world purpose for doing so. In this post we will revisit the project and look at how we can pull out the same data as I pulled out using Excel, in a more efficient way, using more appropriate tooling. This is my first exposure to MongoDB and so this post will provide the perspective of an experienced relational database professional, using a document / JSON database product for the first time.
Installing MongoDB
Firstly, of course, I had to install MongoDB on the Debian server I had provisioned and I have documented that here.
Once the server was installed and configured, I installed a MongoDB client on my client machine. The official MongoDB client is MongoDB Compass. I logged into my server:
Setting up the Database
I created a new database and a collection called Cars (collection = table in the relational world)
I then needed to get the data into the database from the JSON files I had downloaded from the API.
Whilst we can import files from the GUI, I had 770 files to import and the GUI only supports one at a time so I needed a way to automate this. We can use mongoimport.exe on Windows – a command line utility that lets us import .json files into the database. This application is part of the MongoDB Database Tools
I created a PowerShell script to iterate through each file and then import it using mongoimport.exe, the script is below:
$log = "C:\Test\mongoimport.txt";
$files = Get-ChildItem "C:\Test\MOT" -Filter "*.json";
# loop through each JSON file
foreach ($f in $files)
{
$f | Tee-Object -FilePath $log -Append;
$success = 999;
# keep trying every five seconds until mongoimport succeeds
while ($success -gt 0)
{
# build the command
$command = "C:\mongodb-database-tools-windows-x86_64-100.10.0\bin\mongoimport.exe --host 192.168.2.28 --port 27017 --db MOTData --collection Cars --file $($f.FullName)";
# Echo the command and execute
$command | Tee-Object -FilePath $log -Append;
cmd.exe /c $command ;
$success = $LASTEXITCODE;
};
"Waiting..." | Tee-Object -FilePath $log -Append;
Start-Sleep 5;
};
This took a few hours to complete so I just let it do its thing, but once it was complete, my database looked like this:
Querying the Data
OK, so now we have the data in a database, let’s get on with the task of using MongoDB to extract the same data we had in the previous post.
MongoDB Compass allows for queries to be issued via the main GUI or a separate Mongo shell (mongosh):
The query bar in the GUI is where you type your search query, you don’t need to use the full syntax, just the part that goes within the curly braces in the find() method, Compass will wrap the find method boiler plate around it:
db.Cars.find({THIS IS YOUR SEARCH STRING})
I will use the shell here.
Return all the Toyota MR2 Mk1s
Firstly, let’s return all the Toyota Mk1 MR2s and dump them to a CSV, as I did with PowerShell on the original project. A Mk1 will have a manufacturer of Toyota, a model of MR2 and be manufactured before 1990 (there are some in 1990 but there didn’t appear to be a way to distinguish those from a Mk2 which came out in the same year)
Firstly, let’s create an index to support the query:
db.Cars.createIndex({make:1, model:1, manufactureDate:1})
The index is on make, model, manufactureDate all in ascending order (denoted by the 1, rather than -1). Also, the property names are case sensitive and MongoDB happily lets you create an index on a property that doesn’t exist!
With the index in place, our query is:
db.Cars.countDocuments({make:"TOYOTA",model:"MR2",manufactureDate:{$lt:"1990-01-01"}})
After executing our query, we get the result of 13417 back:
Good start – this number matches the number in the CSV that I had exported using PowerShell which we can see by the row numbers in the screenshot below (there is a header row, hence the extra row):
Returning all the Insights
In this section, I’ll just run through all the Insights in Numbers that were outlined in my previous blog post, in the order they were listed. I’ll show the MongoDB query that returns the number. I’ll also note the filters I used in Excel to get the equivalent result from the CSV file. Remember that the Excel sheet contains only Mk1 MR2s, whereas the MongoDB collection is all of the MOT data and therefore each MongoDB query will have to filter to Mk1 MR2s in addition to whichever other filters we need. The Excel sheet also only shows the MOT details for the single most recent MOT.
751 – the number of these Mk1s still on the road (those with an MOT that expired in 2025 or later)
As the original insights were put together between Christmas and New Year in 2024 and for simplicity’s sake, those cars with an MOT expiring sometime in 2025 or later were considered “on the road”
The MongoDB query is below:
db.Cars.aggregate([
{
// alter the motTests property - sort it in a different order
$set: {
motTests: { // property to be changed
$sortArray: {
// sort the motTests by completedDate in descending order
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
// filter the set we are aggregating to Mk1 MR2s currently on the road
$match: {
make: "TOYOTA",
model: "MR2",
manufactureDate: { $lt: "1990-01-01" },
"motTests.0.expiryDate": {$gte:"2025-01-01"} // those with an expirydate in 2025 or later
}
},
{
$count: "totalCount"
}
])
314 – the number of Mk1s still on the road in the best colour red
We just add an additional filter on the color to the above query
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
$match: {
// Mk1 MR2s still on the road
make: "TOYOTA",
model: "MR2",
manufactureDate: { $lt: "1990-01-01" },
"motTests.0.expiryDate": {$gte:"2025-01-01"},
// in the best colour
primaryColour:"Red"
}
},
{
$count: "totalCount" }
])
1118 – the number of Mk1s whose most recent MOT was a failure
Here, we just filter on the LastMOTTestResult column in the Excel file
The Mongo aggregation is below
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
$match: {
// Mk1 MR2s
make: "TOYOTA",
model: "MR2",
manufactureDate: { $lt: "1990-01-01" },
// most recent MOT was a failure
"motTests.0.testResult":"FAILED"
}
},
{
$count: "totalCount"
}
])
4226 – the number of Mk1s whose most recent MOT was a pass but has since lapsed
In Excel, we filter for those cars whose LastMOTTestResult is a pass but the expiry date is before 2025:
And the Mongo version:
db.Cars.aggregate([
{
$set: {
motTests: {
$sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
// filter the set we are aggregating to cars that match this criteria
$match: {
// Mk1 MR2s still on the road
make: "TOYOTA",
model: "MR2",
manufactureDate: { "$lt": "1990-01-01" },
"motTests.0.testResult":"PASSED",
// MOT has expired
"motTests.0.expiryDate":{$lt:"2025-01-01"}
}
},
{
$count: "totalCount" }
])
7322 – the number of Mk1s on the MOT database with no MOT history – possibly scrapped / mothballed pre 2005
Here, we are doing a simple filter on the LastMOTTestResult column for those without PASSED or FAILED as a value:
And the Mongo query:
db.Cars.countDocuments({make:"TOYOTA",model:"MR2",manufactureDate:{$lt:"1990-01-01"},motTests:[]})
1 – the number of Mk1s converted to LPG
1 – the number of Mk1s converted to Electric
These are both the same – a case of filtering on the FuelType column. Given there is only one of each, I’ve included them in the same screenshot
Let’s do some grouping here to kill two birds with one stone. This returns each fuel type, with the total number of vehicles for that fuel type:
db.Cars.aggregate([
{
// Mk1 MR2s still on the road
$match :
{
make:"TOYOTA",
model:"MR2",
manufactureDate:{$lt:"1990-01-01"}
}
},
{
// group by fuel type
$group:
{
_id: "$fuelType",
count: { $count: { } }
}
},
{
$sort: { count: -1 }
}
])
294,302 – the number of miles on the odometer of the highest mileage Mk1 still on the road
In Excel, we sort by the mileage, in descending order and filter on those cars with an MOT expiry date in future:
For this grouping within MongoDB, where we are aggregating the odometer values, I had trouble using the dot notation in the $addFields aggregation in the same way I used it in $match. According to ChatGPT, it’s not supported with addFields, only $match (though I couldn’t find any documentation to back this up) so I used $arrayElemAt instead:
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
// add an additional field to the record called lastMileage
$addFields: {
lastMileage: {$arrayElemAt: ["$motTests.odometerValue", 0] }
}
},
{
$match: {
make: "TOYOTA",
model: "MR2",
manufactureDate: { "$lt": "1990-01-01" },
"motTests.0.testResult":"PASSED",
// and the expiry date was before 2025
"motTests.0.expiryDate":{$gte:"2025-01-01"}
}
},
{
$group: {
_id: "$registration",
Total: { $max: "$lastMileage" }
}
},
{
$sort: { Total:-1}
},
{
$limit:1
}
]
)
545 – the number of miles on the odometer of the lowest mileage car still on the road
In Excel, once again, we filter on those cars with an MOT Expiry in future to satisfy the “still on the road” requirement and then sort by mileage in ascending order
Now Mongo style:
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
$addFields: {
lastMileage: {$arrayElemAt: ["$motTests.odometerValue", 0] }
}
},
{
$match: {
make: "TOYOTA",
model: "MR2",
manufactureDate: { "$lt": "1990-01-01" },
"motTests.0.testResult":"PASSED",
// and the expiry date was before 2025
"motTests.0.expiryDate":{$gte:"2025-01-01"}
}
},
{
$group: {
_id: "$registration",
Total: { $min: "$lastMileage" }
}
},
{
$sort: { Total:1}
},
{
$limit:1
}
])
706,810,742 – the total number of miles covered by all Mk1s in the list
This was easy in Excel – just sum the Last MOT odometer reading:
The number returned by the query below is 710,617,985 which is different to the one above. The reason for this is that the MongoDB query calculates the mileage of the entire MR2 Mk1 cohort by summing the most recent mileage recorded in the cars MOT history, not the mileage of the most recent MOT itself. Some odometerValue properties have a value of “UNREADABLE” and so if the most recent MOT has this value for the mileage, the MongoDB query will return the previous most recent odometer reading that is an integer, whereas my PowerShell method referenced in the previous post will return blank (which will be summed as a 0) where this is the case. The MongoDB query therefore returns a more accurate value.
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
$addFields: {
lastMileage: {$arrayElemAt: ["$motTests.odometerValue", 0] }
}
},
{
$match: {
make: "TOYOTA",
model: "MR2",
manufactureDate: { "$lt": "1990-01-01" }
}
},
{
// group by all
$group: {
_id: null,
Total: { $sum: "$lastMileage" }
}
}
])
5 – the number of Mk1s still on the road with less than 2000 miles on the clock
Again, we get MOT expiry dates in the future to satisfy the on the road requirement, we filter the mileage to less than 2000 and then look at the number of rows returned:
And in Mongo world:
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
$addFields: {
lastMileage: {$arrayElemAt: ["$motTests.odometerValue", 0] }
}
},
{
$match: {
make: "TOYOTA",
model: "MR2",
manufactureDate: { "$lt": "1990-01-01" },
"motTests.0.testResult":"PASSED",
"motTests.0.expiryDate":{$gte:"2025-01-01"},
// the mileage is < 2000
lastMileage: {$lt:2000}
}
},
{
$count: 'Count'
}
])
71 – the number of Mk1s with odometer readings in KM, suggesting a Japanese import
To achieve in Excel, we just filter the OdometerUnit column to KM:
The Mongo way:
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
$addFields: {
odoUnit: {
$ifNull: [{$arrayElemAt: ["$motTests.odometerUnit", 0] }, 0 ]
}
}
},
{
$match: {
make: "TOYOTA",
model: "MR2",
manufactureDate: { "$lt": "1990-01-01" },
odoUnit: "KM"
}
},
{
$count: 'Count'
}
])
12 – the number of Mk1s on the road that have had their stock 1.6L engine swapped for something larger
The Excel solution is to do the usual “expiry in the future” filtering and then filter on the numeric EngineSize column:
Now follows the Mongo:
db.Cars.aggregate([
{
$set: {
motTests: { $sortArray: {
input: "$motTests",
sortBy: { "completedDate": -1 }
}
}
}
},
{
$match: {
make: "TOYOTA",
model: "MR2",
manufactureDate: { "$lt": "1990-01-01" },
"motTests.0.testResult":"PASSED",
"motTests.0.expiryDate":{$gte:"2025-01-01"},
// engine size > 1600
engineSize: {$gt:1600}
}
},
{
$count: 'Count'
}
])
133,963,607 – the total number of vehicle records in the files
The “non MongoDB method” I used for this was literally to count every line in each file using PowerShell:
$lineCount = 0;
Get-ChildItem "Z:\MOTData" | foreach-Object {
$lineCount += (Get-Content $_).Length;
};
$lineCount;
The MongoDB query:
db.Cars.aggregate({
$group:
{
_id: null,
TotalCars: { $count: { } }
}
});
Return all the Toyota Mk1 MR2s – Redux
In this and the previous post, I mentioned that the way I found Mk1 MR2s in the MOT data was to use 1990 as a cut off – this was the year when the Mk2 was introduced and I would rather jettison some of the Mk1s from the data than include Mk2s.
After investigating further, I found that there was actually a way to distinguish a Mk1 from a Mk2 other than year – the engine size. Mk1s had a 1.6L engine and Mk2s had a 2L (I never knew this until now!) With this new knowledge, we can refine our results and include those that were manufactured in 1990 but weren’t a Mk2. There also looked to be some rogue records with spurious manufacture dates – 1970, 1900 etc which presumably was used to denote the manufacture date was unknown so we don’t know for sure if these are Mk1s so should exclude them.
Let’s write a query to show us all of the Mk1 MR2s based on this new classification.
In this query we are saying show me all of the Toyota MR2s manufactured before 1990 regardless of their engine size (some may have had an engine swap) AND those manufactured in 1990 with a 1600cc engine (we miss any Mk1s from 1990 that have had an engine swap, if any):
db.Cars.countDocuments({
make: "TOYOTA",
model: "MR2",
manufactureDate: {$gt:"1980-01-01"},
$or: [
{ manufactureDate: { $lt: "1990-01-01" } },
{
$and: [
{ manufactureDate: { $gte: "1990-01-01" } },
{ manufactureDate: { $lt: "1991-01-01" } },
{ engineSize: { $lte: 1600 } }
]
}
]
})
As expected, we see the count increase from our previous number of 13417 to 13782:
Show Failure Categories by Marque – Bonus
Finally, a bonus query showing something that was not in the original post – this one shows us the most common reasons a Toyota MR2 of any generation (Mk1, Mk2, Mk3) would fail an MOT.
Here, we introduced the unwind aggregation which essentially expands out the motTests array, duplicating the entire document for each element in the array so if a car has 3 MOT tests, we will end up with 3 duplicate records for that car, though the MOT Test property will be unique per record.
We also introduced a case statement to group by marque using a range of values for engine size:
db.Cars.aggregate([
{
$match: {make:"TOYOTA",model:"MR2"}
},
{
$addFields: {
Mileagerange: {// group by engine size to represent the marques
$switch: {
branches: [
{
case: {$and: [{$gte: ["$engineSize",1550]},{$lte: ["$engineSize",1650]}]},
then: "Mk1"
},
{
case: {$and: [{$gte: ["$engineSize",1950]},{$lte: ["$engineSize",2050]}]},
then: "Mk2"
},
{
case: {$and: [{$gte: ["$engineSize",1750]},{$lte: ["$engineSize",1850]}]},
then: "Mk3"
},
],
default: "unknown"
}
}
}
},
{ $unwind: "$motTests" },
{ $unwind:"$motTests.defects"},
{ $match: {"motTests.defects.type":"FAIL"}},
{
$group: {
_id: {Defect:"$motTests.defects.text",Mk:"$Mileagerange"}, // Group by defect text
failureCount: { $sum: 1 } // Count occurrences
}
},
{ $sort: { Mileagerange: 1, failureCount: -1 } }
])
The results are below:
Conclusion
In this post we revisited a previous project which sought to find various insights about the MOT history of Mk1 MR2s. Where the project had previously been done by using Excel to filter a cut-down CSV, here we used a document database designed for querying JSON documents. Did I enjoy this? Not particularly if I am honest. The time that went into this post was far in excess of what I expected. This isn’t unusual when something is new to me but I didn’t find MongoDB and its syntax particularly intuitive and I had to read the documentation and consult LLMs a fair bit to get the detail I wanted. That said, never say never – I may well find a need to use it again in future and it may come more naturally to me next time, however, MongoDB has now been checked off my list as something I have used where I had a genuine requirement to do so.