0

I'm trying to make a MongoDB Database with a few collections. These collections have a one-to-many relationship with eachother, in the sense that:

  • One {A} has (0..*) of {B}
  • One {B} has (0..*) of {C}
  • ... and a few levels deep.

As per structure, I've got a very basic structure wherein

  • "_id" is the object's own ID
  • "Aid" and "Bid" for respectively {B} and {C} is the parent's ID.
  • There cannot be a {B} whose Aid doesn't match any {A}'s _id.
  • Any other object fields are stored separately.

Everything is an integer that is auto-incremented clientside. I can just as easily check clientside if there is an {A} whose _id matches {B}'s Aid, but I'm curious if I can use $lookup and $in serverside too. For this purpose, performance will not be terribly important as the amount of {C}'s will probably not pass 100k.

However, there exists an object {D} that will have a huge amount of objects inserted over its lifetime (say, a few billion each year), so if a $lookup is possible but performance-wise not feasible, I can live with doing validation for {D} clientside.

Problem:

I've got the following query for {B} that's failing:

db.createCollection("B", {
    validator: { $and : 
        [
            { $jsonSchema: {
                bsonType: "object",
                required: ["_id", "Aid"],
                properties: {
                    _id: {
                        bsonType: "long",
                        description: "Bid must be a number and is required!"
                    },
                    Aid: {
                        bsonType: "long",
                        description: "Aid must be a number and is required!"
                    }
                }
            } },
            { 
                Aid: { 
                    $in : [
                        { $lookup : {
                            from : "A",
                            localField : "Aid",
                            foreignField : "_id",
                            as : "A_IDs"
                        } }
                    ]
                } 
            }
        ]
    }
});

Error given: 

{
        "ok" : 0,
        "errmsg" : "cannot nest $ under $in",
        "code" : 2,
        "codeName" : "BadValue"
}

I understand that the above query is failing because a $in cannot work with the $lookup as presented above. However, what I'm asking is if the idea is possible at all, and if so then how.

Diamundo
  • 148
  • 11
  • No you cannot do that. Validators use "query expressions" for validation purposes, so anything you can also execute using `.find()`. `$lookup` is an "aggregation pipeline stage" and is **NOT** a query operator. You can only use pipeline stages with `.aggregate()`, and therefore this does not apply to document validation. See [Schema Validation](https://docs.mongodb.com/manual/core/schema-validation/) in the core documentation, which explains everything and has links to the things you **can** do. – Neil Lunn Apr 16 '18 at 08:54
  • @NeilLunn I understand that a validator uses the same syntax as a `.find()` function. However, am I then perhaps able to `find` any {A}'s that match {B}'s *A_id* inside the `createCollection` function? Or do I really have to do it clientside? – Diamundo Apr 16 '18 at 08:57
  • Just to clarify, since MongoDB 3.6 you can use `$expr` which is a "query" operator to evaluate "some" aggregation expressions. But expressions like `{ "$eq": [{ "$sum": "$items" }, 10] }` is completely different to a "pipeline stage", which is something not valid for `$expr`. – Neil Lunn Apr 16 '18 at 08:57
  • Since you are still not getting it, the **only** way to get data from "another collection" is using `$lookup`. You **CANNOT** use `$lookup` here. Forgive the bold text and the caps, but it's the only way I see that the message might sink in. Not possible. You might even note the documentation on `$jsonSchema` where various functions of "cross schema validation" are explicitly forbidden when using with MongoDB. – Neil Lunn Apr 16 '18 at 08:59
  • @NeilLunn, ah okay, so clientside-only it is. Thanks for your help! (and if you want to create an answer I'll mark it as correct) – Diamundo Apr 16 '18 at 09:01

0 Answers0