I'm trying to move data from mongodb to mysql
I used mongoexport
in order to export the data from the mongodb database to .json files
When I browse my mongodb collection using robomongo I'm getting ids in a Legacy UUID format (something like LUUID("00018e06-1de9-aa45-afb5-a2bc00ed94f7")
but in the exported .json files it appears this way:
{ "_id" : { "$binary" : "AAGOBh3pqkWvtaK8AO2U9w==", "$type" : "03" }, ...}
It there any way to convert the latter to the format I see in robomongo using Node.JS?
P.S I've seen many Q&A that reference Binary and BinData - but non of them have shown how to convert it
So, first off $binary
and BinData
are basically the same thing written/represented different ways. Using your example and this reference you can write it like so in the "Mongo Shell" mode:
BinData(3,"AAGOBh3pqkWvtaK8AO2U9w==")
Or in "strict" mode that becomes:
{ "$binary" : "AAGOBh3pqkWvtaK8AO2U9w==", "$type" : "03" }
Now, type 3 here actually refers to subtype 3 (of the binary type) per the BSON spec which means this is a "UUID (old)" subtype. Per the reference above, the string itself is actually a base64
representation of a binary string. Because of this (I am inferring) robomongo
is basically converting the UUID and displaying it for you when you are browsing the data. Let's attempt to do that manually instead in the mongo
shell, first by converting it to hex:
> var foo = BinData(3, "AAGOBh3pqkWvtaK8AO2U9w==")
> foo.hex()
00018e061de9aa45afb5a2bc00ed94f7
Now, let's plug that into a UUID constructor and see if it translates back:
> var uuid = new UUID("00018e061de9aa45afb5a2bc00ed94f7")
> uuid
BinData(3,"AAGOBh3pqkWvtaK8AO2U9w==")
And, it does - all robomongo
is doing is adding some dashes for readability. Just for completeness, with your other example I will remove the dashes and create a UUID, show the BinData
version and convert back:
> var bar = UUID("d72f21fe47808040ab3da1fb01045d3a")
> bar
BinData(3,"1y8h/keAgECrPaH7AQRdOg==")
> bar.hex()
d72f21fe47808040ab3da1fb01045d3a
Hence, what you need to do is convert between the base64 encoding in node yourself. I don't have a node.js
environment to test, but it looks like that has been covered before elsewhere on the site.
This is a working function in Node.JS that converts the binary to Hex UUID:
function Bin2HexUUID(bin){
var hex = new Buffer(bin, 'base64').toString('hex');
return hex.replace(/^(.{8})(.{4})(.{4})(.{4})(.{12})$/, function (){
return arguments[1]+"-"+arguments[2]+"-"+arguments[3]+"-"+arguments[4]+"-"+arguments[5];
});
}
//use example
var binary_id = { "$binary" : "AAGOBh3pqkWvtaK8AO2U9w==", "$type" : "03" };
console.log(Bin2HexUUID(binary_id.$binary)); //00018e06-1de9-aa45-afb5-a2bc00ed94f7