CODEWITHSUNDEEP
mongodbmongodb-query
cjmling
cjmling

Reputation: 7278

MongoDB query $lookup with prefix

I have two collection rounds and summaries

A record in rounds looks like

{
   "_id": "2018-04",
   "name": "Round 2018-04" 
}

A record in summaries look like

{
   "phase": "round:2018-04",
   "userId": NumberLong(66325),
}

I want to query summaries and lookup into rounds joining based on phase of summaries into _id of rounds

PROBLEM: It will be pretty simple if there was no prefix of round: in phase.

Is there anyway to do this?

This is my code so far.

$cursor = $this->mongo->selectCollection('summaries')->aggregate([
            array('$match' => []),
            array(
                '$lookup' => [
                    'from' => 'rounds',
                    'localField' => 'phase',
                    'foreignField' => '_id',
                    'as' => 'roundDetail'
                ]
            ),
            array(
                '$unwind' => '$roundDetail',
            ),
            array(
                '$project' => [
                    'userId' => 1,
                    'roundDetail.name' => 1
                ]
            )
        ]);

MongoDB version 3.4.16

Upvotes: 1

Views: 482

Answers (1)

Ashh
Ashh

Reputation: 46441

You can use substrBytes to remove characters from the string.

$cursor = $this->mongo->selectCollection('summaries')->aggregate([
    array('$match' => []),
    array('$addFields' => [ 'phase' => [ '$substrBytes' => [ '$phase', 6, 7 ] ] ] ),
    array(
        '$lookup' => [
            'from' => 'rounds',
            'localField' => 'phase',
            'foreignField' => '_id',
            'as' => 'roundDetail'
        ]
    ),
    array(
        '$unwind' => '$roundDetail',
    ),
    array(
        '$project' => [
            'userId' => 1,
            'roundDetail.name' => 1
        ]
    )

])

Upvotes: 1

Related Questions