Mongodb - How to convert ids to JUUID format from mongo shell script output

awgtek
October 4, 2024
66 views
3 votes
3 Answers

With MongoDB id stored in Java LUUID (legacy representation), when a script is run to retrieve these ids, the output shows them in binary format:

var myStringArray = ["string1",
    "string2"];

for (var i = 0; i < myStringArray.length; i++) {

    let outputs = db.getCollection("MyCollection").aggregate(
        {
            '$match': {
                "field1": myStringArray[i]
            }
        }

        , { $project: { _id: 1, "field1": 1 } }
    ).map(function (el) { return EJSON.stringify(el) });

    console.log(outputs);
}

Output:

[
  '{"_id":{"$binary":{"base64":"Z0raDiKJKdjkWnqi5TvWjQ==","subType":"03"}},"field1":"string1"}'
]
[
  '{"_id":{"$binary":{"base64":"a0xH8uquuKwwbsRc4LggvA==","subType":"03"}},"field1":"string2"}'
]

Would prefer to see the "_id" shown similar to "_id": JUUID("ACB8AEEA-D247-4C6B-BC20-B8E05C146F30").

NOTE: I am not asking how to store in subtype 4 (standard) UUID format. The MongoDB driver is set to UuidRepresentation.JAVA_LEGACY and this will not change.

Answers

Chosen as BEST ANSWER

I came up with the following solution which works for my purposes. It borrows from this old version of some helper functions I came across. Big shout out to @aneroid for assistance with the methods.

var myStringArray = ["string1",
    "string2"];

for (var i = 0; i < myStringArray.length; i++) {

    let outputs = db.getCollection("MyCollection").aggregate(
        {
            '$match': {
                "field1": myStringArray[i]
            }
        }

        , { $project: { _id: 1, "field1": 1 } }
    ).map(function (el) {
        el._id = toJUUID(JUUID(el._id.toString('hex', 3)))
        return EJSON.stringify(el);
    });

    console.log(outputs);

} 

function JUUID(uuid) {
    var hex = uuid.replace(/[{}-]/g, ""); // remove extra characters
    var msb = hex.substr(0, 16);
    var lsb = hex.substr(16, 16);
    msb = msb.substr(14, 2) + msb.substr(12, 2) + msb.substr(10, 2) + msb.substr(8, 2) + msb.substr(6, 2) + msb.substr(4, 2) + msb.substr(2, 2) + msb.substr(0, 2);
    lsb = lsb.substr(14, 2) + lsb.substr(12, 2) + lsb.substr(10, 2) + lsb.substr(8, 2) + lsb.substr(6, 2) + lsb.substr(4, 2) + lsb.substr(2, 2) + lsb.substr(0, 2);
    hex = msb + lsb;
        var lsb = hex.substr(16, 16);
    msb = msb.substr(14, 2) + msb.substr(12, 2) + msb.substr(10, 2) + msb.substr(8, 2) + msb.substr(6, 2) + msb.substr(4, 2) + msb.substr(2, 2) + msb.substr(0, 2);
    lsb = lsb.substr(14, 2) + lsb.substr(12, 2) + lsb.substr(10, 2) + lsb.substr(8, 2) + lsb.substr(6, 2) + lsb.substr(4, 2) + lsb.substr(2, 2) + lsb.substr(0, 2);
    hex = msb + lsb;
    
    var base64 = HexToBase64(hex);
    return new BinData(3, base64);
}
function toJUUID(binData) {
    var hex = binData.toString('hex');
    var msb = hex.substr(0, 16);
    var lsb = hex.substr(16, 16);
    msb = msb.substr(14, 2) + msb.substr(12, 2) + msb.substr(10, 2) + msb.substr(8, 2) + msb.substr(6, 2) + msb.substr(4, 2) + msb.substr(2, 2) + msb.substr(0, 2);
    lsb = lsb.substr(14, 2) + lsb.substr(12, 2) + lsb.substr(10, 2) + lsb.substr(8, 2) + lsb.substr(6, 2) + lsb.substr(4, 2) + lsb.substr(2, 2) + lsb.substr(0, 2);
    hex = msb + lsb;
    return hex.substr(0, 8) + '-' + hex.substr(8, 4) + '-' + hex.substr(12, 4) + '-' + hex.substr(16, 4) + '-' + hex.substr(20, 12);
}

Output:

[
  '{"_id":"d8098922-0eda-4a67-8dd6-3be5a27a5ae4","field1":"string1"}'
]
[
  '{"_id":"acb8aeea-f247-4c6b-bc20-b8e05c146f30","field1":"string2"}'
]

(Edit)

The closest to getting a hex-representation is to use .toString('hex', 3).

db.collection.insertOne({_id: BinData(3, "Z0raDiKJKdjkWnqi5TvWjQ==") ,"field1":"string1"})
var doc = db.collection.findOne({})
doc._id.toString('hex')  // 674ada0e228929d8e45a7aa2e53bd68d

// to verify that 
Binary.createFromHexString(doc._id.toString('hex', 3), 3)
// returns Binary.createFromBase64('Z0raDiKJKdjkWnqi5TvWjQ==', 3)

Binary.createFromHexString("674ada0e228929d8e45a7aa2e53bd68d", 3)
// returns Binary.createFromBase64('Z0raDiKJKdjkWnqi5TvWjQ==', 3)

For your data and code, use this:

db.collection.insertMany([
  { "_id": BinData(3, "Z0raDiKJKdjkWnqi5TvWjQ=="), "field1": "string1" },
  { "_id": BinData(3, "a0xH8uquuKwwbsRc4LggvA=="), "field1": "string2" },
]);

var myStringArray = ["string1", "string2"];

for (var i = 0; i < myStringArray.length; i++) {
    let output = db.getCollection("collection").aggregate(
        { $match: { "field1": myStringArray[i] } },
        { $project: { _id: 1, "field1": 1 }
    }).map(function (el) {
        el._id = el._id.toString('hex', 3)  // NOTE this line
        return el
    });
    console.log(output);
}

Output is:

{
  _id: '674ada0e228929d8e45a7aa2e53bd68d',
  field1: 'string1'
}
{
  _id: '6b4c47f2eaaeb8ac306ec45ce0b820bc',
  field1: 'string2'
}

With the latest MongoDB v8.0 release, you have a new option of $toUUID

sample data:

db.uuid.insertOne({
    "_id":Binary.createFromBase64('Z0raDiKJKdjkWnqi5TvWjQ==', 3),
    "field1":"string1"
});

example syntax of $toUUID:

db.uuid.aggregate([
  {
    $set: {
      uuid: {
        $toUUID: "$_id"
      }
    }
  }
])

sample output:

{
  _id: Binary.createFromBase64('Z0raDiKJKdjkWnqi5TvWjQ==', 3),
  field1: 'string1',
  uuid: UUID('674ada0e-2289-29d8-e45a-7aa2e53bd68d')
}

Prior to MongoDB v8.0, you may use $function to perform UUID conversion with javascript’s hex function. After the conversion, you can use $concat and $substrCP to reform your UUID string.

Please be reminded:

$function may decrease performance. From official doc,

Executing JavaScript inside an aggregation expression may decrease performance. Only use the $function operator if the provided pipeline operators cannot fulfill your application’s needs.

$function is deprecated in future versions of MongoDB. From official doc,

Starting in MongoDB 8.0, server-side JavaScript functions ($accumulator, $function, $where) are deprecated. MongoDB logs a warning when you run these functions.

db.collection.aggregate([
  {
    "$set": {
      "uuid": {
        "$function": {
          "body": "function(id) {return id.hex()}",
          "args": [
            "$_id"
          ],
          "lang": "js"
        }
      }
    }
  },
  {
    "$set": {
      "uuid": {
        "$concat": [
          {
            "$substrCP": [
              "$uuid",
              0,
              8
            ]
          },
          "-",
          {
            "$substrCP": [
              "$uuid",
              8,
              4
            ]
          },
          "-",
          {
            "$substrCP": [
              "$uuid",
              12,
              4
            ]
          },
          "-",
          {
            "$substrCP": [
              "$uuid",
              16,
              4
            ]
          },
          "-",
          {
            "$substrCP": [
              "$uuid",
              20,
              12
            ]
          }
        ]
      }
    }
  }
])

Mongo Playground

Please signup or login to give your own answer.

Click here to cancel reply.

Mongodb – How to convert ids to JUUID format from mongo shell script output

Answers