How do I group by on a dbref in MongoDB with Doctrine

Question

I want to do an aggregation query where I can group by a dbref. This is what I've tried.

$aggregation = array(
    array(
        '$group' => array(
            '_id' => '$foreign.$id',
            'doc' => array(
                '$last' => '$$ROOT'
            )
        )
    ),
    array(
        '$sort' => array(
            'date' => -1
        )
    )
);

return $this->getDocumentManager()->getDocumentCollection('AppBundle:Collection')->aggregate($aggregation)->toArray();

But this attempt fails because you're not allowed to use the second $ sign in '$foreign.$id'. How can I do this query?

Answer 1

After a lot of searching I've found this Bug Ticket on the monogdb Jira which deals with basically this / an similar issue.

The last comment on this ticket offers an workaround to resolve the issue as an command line code. I've taken this solution and built it into an aggregation query which solved my problem.

$aggregation = array(
    array(
        '$addFields' => array(
            'foreignId' => array(
                '$arrayToObject' => array(
                    '$map' => array(
                        'input' => array(
                            '$objectToArray' => '$foreign'
                        ),
                        'in' => array(
                            'k' => array(
                                '$cond' => array(
                                    array(
                                        '$eq' => array(
                                            array(
                                                '$substrCP' => array(
                                                    '$$this.k', 0, 1
                                                )
                                            ),
                                            array(
                                                '$literal' => '$'
                                            )
                                        )
                                    ),
                                    array(
                                        '$substrCP' => array(
                                            '$$this.k',1,['$strLenCP' => '$$this.k']
                                        )
                                    ),
                                    '$$this.k'
                                )
                            ),
                            'v' => '$$this.v'
                        )
                    )
                )
            )
        )
    ),
    array(
        '$group' => array(
            '_id' => '$foreignId',
            'doc' => array(
                '$last' => '$$ROOT'
            )
        )
    ),
    array(
        '$sort' => array(
            'date' => -1
        )
    )
);
$this->getDocumentManager()->getDocumentCollection('AppBundle:Collection')->aggregate($aggregation)->toArray();

This query gives me the correct and expected result.