Mongodb $lookup with condition

[shubaivan]

I have mongodb version 4.4.3 and I want to fetch all allerts which has tmeplates relation and which templates has deviceStatus relation

Alert document

{
    "_id" : ObjectId("6034f08a025fd735035d26c4"),
    "templates" : [ 
        {
            "$ref" : "Template",
            "$id" : ObjectId("6034f050bba852116468c993")
        }
    ]
}

and Templat document

{
    "_id" : ObjectId("6034f050bba852116468c993"),
    "deviceStatus" : {
        "value" : "hold"
    },
}

since like that link my query should return me object alert where should be full object Template

db.getCollection('Alert').aggregate([  
    {
        $lookup: {
            from: 'Template',
            localField: 'templates',
            foreignField: '_id',
            as: 'templateObject'
        }
    }
])

or odm style

    public function ff()
    {
        $documentManager = $this->getDocumentManager();
        $builder = $documentManager->createAggregationBuilder(Alert::class);
        $builder
            ->lookup('templates')
            ->alias('templatesObj');

        $aggregation = $builder->getAggregation();
        $aggregation->getIterator()->toArray();
    }

but I had empty templateObject Or this happened becuase I have templates array objects which contains two column $ref and $id instead exactly $id for correct lookup ? How to buid this query ritgh ?

When I changed structure to

"templates" : [ 
        ObjectId("6041e12c2845427b5f2ff7d3")
    ],

instead fo

"templates" : [ 
    {
        "$ref" : "Template",
        "$id" : ObjectId("6034f050bba852116468c993")
    }
]

everything works correct

My question how to reproduce the same way when I have this struture ?

    "templates" : [ 
        {
            "$ref" : "Template",
            "$id" : ObjectId("6034f050bba852116468c993")
        }
    ]

[alcaeus]

Unfortunately, current server versions are not able to handle DBRef objects in aggregation pipelines due to the field names being prefix with a dollar sign. ODM includes a ref reference style that fixes this problem as it doesn't have prefixed field names in the reference object. The downside is that it requires you to update all your references in the databases, so this may not be a feasible option if you already have a large amount of data stored in this format.

[shubaivan]

right now I'm on beginning develop and don't have legacy data. Just want to decide how to correct develop models and these relations, looks like need to use storeAs="id" if I will planing use lookup

 * @ReferenceMany(targetDocument=Template::class, inversedBy="alerts", storeAs="id")

I checked how collection works - everything correct, \Doctrine\ODM\MongoDB\Mapping\ClassMetadata::getReferenceId

but another question, maybe make sence use storeAs="id" by degault or what profit we have when we use this structure ?

    "templates" : [ 
        {
            "$ref" : "Template",
            "$id" : ObjectId("6034f050bba852116468c993")
        }
    ] 

[shubaivan]

or another question, how to reproduce this

db.getCollection('Alert').aggregate([
    {
        $lookup: {
            from: 'Template',
            let: { "template_id": "$templates.$id" },
            pipeline: [
                {
                    $match: {
                        $expr: { $in: ["$_id", "$$template_id"] }
                    }
                }
            ],
            as: 'templateObject'
        }
    }
]);

[alcaeus]

You can use storeAs="id", but I would recommend storeAs="ref". When storing references as an ID, they are not compatible with inheritance structures as we have no place to store a discriminator value. Here's a summary of what the reference above would look like.

storeAs="id"

"templates" : [ 
    "$id" : ObjectId("6034f050bba852116468c993")
] 

storeAs="dbRef"

"templates" : [ 
    {
        "$ref" : "Template",
        "$id" : ObjectId("6034f050bba852116468c993")
    }
] 

storeAs="ref"

"templates" : [ 
    {
        "id" : ObjectId("6034f050bba852116468c993")
    }
] 

As you can see, storeAs="id" stores only an object ID, while the other formats can take additional data (e.g. denormalised data for optimised queries).

As for using pipeline in $lookup, I'll refer you to doctrine/mongodb-odm#1863 which discusses this. It has not yet been implemented.

[stale[bot]]

This issue has been automatically marked as stale because it has not had any recent activity. It will be closed in a week if no further activity occurs. Thank you for your contributions.

[alcaeus]

Closing as this is to be resolved in ODM.