Query MongoDB collection by UUID

[autom8]

Hey everyone, I’m a bit confused with MongoDB + n8n, and would really appreciate some guidance

What I have working: I have a workflow that first queries a collection in MongoDB using the “find” operation and returns the result. This part works as expected – I am returned with an UUID that essentially correlates this record with another record from a different collection. However, it seems the UUID that is returned from this first lookup is in base64 format (subType 4).

Where I am struggling: is with the 2nd lookup. I now want to query another collection that uses a field which also has a binary UUID schema (not the default _id), and match using the UUID from the first lookup. I’ve attempted many different queries, but always get returned with an error or an empty result. When I try in MongoDB Compass, it allows me to query the UUID in the 2nd collection directly by doing something like { “theField”: UUID(‘ac000000-0000-0000-0000-000000000000’) }, but if I try that same filter in n8n, I get an error returned “ERROR: Unexpected token U in JSON at position 24”. I’ve tried putting quotes around it, but then I get an empty response.

I couldn’t find any examples in the forums, but I’m assuming I need to do some kind of lookup using the base64 string that was returned from the first query – just not sure how to do that, or what that query looks like for n8n.

Any ideas or examples?

[MutedJam]

Hi @autom8, welcome to the community!

n8n does struggle a bit with non-string types in Mongo I am afraid

For object IDs you can use $toObjectId as suggested here in many cases, though I am not aware of a way to do this for the datatype you have described.

The Mongo library used in n8n would require valid JSON though which is why it fails for values { “theField”: UUID(‘ac000000-0000-0000-0000-000000000000’) }. Seeing the UUID is a base64-encoded string what happens if you simply treat this value as a string and use it as such for your lookup?

[kimus]

Hi @MutedJam ,

In that thread I’ve also suggested that the MongoDB driver should support extended JSON. At least, it’s in the docs that the Node JS should support it. And Extended JSON it’s a valid JSON.

I’ve tried some code and it’s not very hard to support this.

const { ObjectId } = require('mongodb');
const { EJSON } = require('bson'); // this is a dependency of the latest mongodb package

const doc = {
  someId: { $oid: '2022-12-16' }
  created_at: { $date: '2022-12-16' }
};
const edoc = EJSON.parse(JSON.stringify(doc));

const doc = {
  some_id: { $oid: '63a1e9d62bb1e717acc27cc8' },
  created_at: { $date: '2022-12-16' }
};
const edoc = EJSON.parse(JSON.stringify(doc));

console.log(edoc.some_id);
console.log(edoc.some_id instanceof ObjectId);

console.log(edoc.created_at);
console.log(edoc.created_at instanceof Date);

this will output:

new ObjectId("63a1e9d62bb1e717acc27cc8")
true
2022-12-16T00:00:00.000Z
true

And using the MongoClient to insert document it will work fine:

{
    "_id" : ObjectId("63a1ee2c335276614d97b448"),
    "some_id" : ObjectId("63a1e9d62bb1e717acc27cc8"),
    "created_at" : ISODate("2022-12-16T00:00:00.000Z")
}

I hope this helps.

[autom8]

@MutedJam - Thanks for the reply! I’ve tried doing the lookup with the base64 string, but it was returning an empty result. The field type in mongo is “binary”, so I’m guessing that has something to do with it. Unfortunately, I’m pretty new to mongo, so that doesn’t make this task any easier. lol

[autom8]

@kimus - Thank you for the detailed info! I will see if I can figure anything out with what you’ve shared, but as I mentioned in my other reply, I’m new to Mongo, so this might be a little over my head.

[kimus]

@autom8 sorry about the information confusion. Wasn’t for you. I’m just waiting for this feature also, and that’s why I was passioned to give detail information

Like @MutedJam said, there’s no way to do get insert/update working with non-string types in n8n. Like ObjectId (although for Date there is a weird “Date Fields” option). I guess the n8n team, or a kind contributor, needs to enhance the MongoDB node.

Also, I would much prefer that every operation was a JSON field instead of that Fields option. And a option to turn on/off Extended JSON instead of Date Fields option. I’ve tested the insert operation by parsing each entry in insertItems with EJSON, in the n8n code, and worked has expected to create ObjectId and Date object types.

[autom8]

Thanks again, @kimus!

Quick question: you mentioned there is no way to do insert/update with non-string types in n8n… but does that also apply for a normal “find” operation or a match query? I don’t need to do any updates right now, just need to be able to read the data for a record.

[kimus]

You can use $toObjectId or $toDate in the find, check above:

@MutedJam

For object IDs you can use $toObjectId as suggested here in many cases, though I am not aware of a way to do this for the datatype you have described.

So, for date like 2022-12-21 you could convert it to Date with something like:

{
  { "$expr": { "$eq": ["$dbDateField", { "$toDate": "{{ $json['someDate'] }}" }] } }
}

[autom8]

Got it. Unfortunately I don’t think those will work for me, as I need to lookup based on binary schema type UUID.

Thanks again though!

[autom8]

For what its worth – anyone who might work on a solution for this in an upcoming version… here’s another filter I can use to get it working directly inside Mongo Compass (but again doesn’t work in n8n):

{ "theField": BinData(4, <BASE64 VALUE>) }