Aggregate query match using regex

[mikhael-artur]

I'm trying to get every value whose id start with '32.04.06.*'. I can get the correct data using the mongodb console like this:

db.ReferenceData.aggregate([{$unwind: '$value'},
{$match: {'value.id': {$regex: /32.04.06.*/}}},
{$group: {_id: null, value: {$push: '$value'}}},
{$project: {value: 1, _id: 0}}])

But, when I try to use it on my Laravel web app, I got an empty array. I think this is because the regex become string so it looks for value that have id: "/32.04.06.*/" where there is none.

My Data:

[{
    "name": "Wilayah",
    "is_hierarchical": 1,
    "value": [
        {
            'id': '32',
            'name': 'Jawa Barat',
            'level':0
        },
        {
            'id': '32.73',
            'name': 'Kota Bandung',
            'level':1
        },
        {
            'id': '32.73.01',
            'name': 'Sukasari',
            'level':2
        },
        {
            'id': '32.73.01.1001',
            'name': 'Sukarasa',
            'level':3
        },
        {
            'id': '32.73.01.1002',
            'name': 'Gegerkalong',
            'level':3
        },
        {
            'id': '32.73.01.1003',
            'name': 'Isola',
            'level':3
        },
        {
            'id': '32.73.01.1004',
            'name': 'Sarijadi',
            'level':3
        },
        {
            'id': '32.73.02',
            'name': 'Coblong',
            'level':2
        },
        {
            'id': '32.73.02.1001',
            'name': 'Cipaganti',
            'level':3
        },
        {
            'id': '32.73.02.1002',
            'name': 'Lebakgede',
            'level':3
        },
        {
            'id': '32.73.02.1003',
            'name': 'Sadangserang',
            'level':3
        },
        {
            'id': '32.04',
            'name': 'Kab. Bandung',
            'level':1
        },
        {
            'id': '32.04.05',
            'name': 'Cileunyi',
            'level':2
        },
        {
            'id': '32.04.05.2001',
            'name': 'Cileunyi Kulon',
            'level':3
        },
        {
            'id': '32.04.05.2002',
            'name': 'Cileunyi Wetan',
            'level':3
        },
        {
            'id': '32.04.05.2003',
            'name': 'Cimekar',
            'level':3
        },
        {
            'id': '32.04.05.2004',
            'name': 'Cinunuk',
            'level':3
        },
        {
            'id': '32.04.05.2005',
            'name': 'Cibiru Hilir',
            'level':3
        },
        {
            'id': '32.04.05.2006',
            'name': 'Cibiru Wetan',
            'level':3
        },
        {
            'id': '32.04.06',
            'name': 'Cimenyan',
            'level':2
        },
        {
            'id': '32.04.06.1001',
            'name': 'Padasuka',
            'level':3
        },
        {
            'id': '32.04.06.1002',
            'name': 'Cibeunying',
            'level':3
        },
        {
            'id': '32.04.06.2003',
            'name': 'Cimenyan',
            'level':3
        },
        {
            'id': '32.04.06.2004',
            'name': 'Mandalamekar',
            'level':3
        },
        {
            'id': '32.04.06.2005',
            'name': 'Cikadut',
            'level':3
        },
        {
            'id': '32.04.06.2006',
            'name': 'Ciburial',
            'level':3
        },
        {
            'id': '32.04.06.2007',
            'name': 'Sindanglaya',
            'level':3
        },
        {
            'id': '32.04.06.2008',
            'name': 'Mekarsaluyu',
            'level':3
        },
        {
            'id': '32.04.06.2009',
            'name': 'Mekarmanik',
            'level':3
        },
        {
            'id': '32.04.07',
            'name': 'Cilengkrang',
            'level':2
        },
        {
            'id': '32.04.07.2001',
            'name': 'Jatiendah',
            'level':3
        }
    ]
},
{
    "name": "Jenis Kelamin",
    "is_hierarchical": 0,
    "value": [
        {
            "id": "0",
            "name": "Laki-laki",
            "valid_from": "2019-08-14",
            "valid_until": "-1"
        },
        {
            "id": "1",
            "name": "Perempuan",
            "valid_from": "2019-08-14",
            "valid_until": "-1"
        }
    ]
}]

My Code:

$cursor = DB::collection('ReferenceData')->raw()->aggregate(
            [
                ['$unwind' => '$value'],
                ['$match' => ['value.id' => ['$regex' => '/32.04.06.*/']]],
                ['$group' => ['_id' => null, 'value' => ['$push' => '$value']]],
                ['$project' => ['value' => 1, '_id' => 0]]
            ]);

        $a = [];
        foreach ($cursor as $doc) {
            $a[] = $doc;
        }
        return $a;

Expected Result:

{ "value" : [ 
    { 
        "id" : "32.04.06", 
        "name" : "Cimenyan", 
        "level" : 2 
    }, 
    { 
        "id" : "32.04.06.1001", 
        "name" : "Padasuka", 
        "level" : 3 
    }, 
    { 
        "id" : "32.04.06.1002", 
        "name" : "Cibeunying", 
        "level" : 3 
    },
    { 
        "id" : "32.04.06.2003", 
        "name" : "Cimenyan", 
        "level" : 3 
    }, 
    { 
        "id" : "32.04.06.2004", 
        "name" : "Mandalamekar", 
        "level" : 3 }, 
    { 
        "id" : "32.04.06.2005", 
        "name" : "Cikadut", 
        "level" : 3 
    }, 
    { 
        "id" : "32.04.06.2006", 
        "name" : "Ciburial", 
        "level" : 3 
    }, 
    { 
        "id" : "32.04.06.2007", 
        "name" : "Sindanglaya", 
        "level" : 3 }, 
    { 
        "id" : "32.04.06.2008", 
        "name" : "Mekarsaluyu", 
        "level" : 3 }, 
    { 
        "id" : "32.04.06.2009", 
        "name" : "Mekarmanik", 
        "level" : 3 } 
    ] 
}

What I got: [], when using ['$regex' => '/32.04.06.*/'] or ['$regex' => '/32.04.06.*/'] syntax error, unexpected '/', when using ['$regex' => /32.04.06.*/]

[Smolevich]

@mikhael-artur, try such as

      $test = DB::collection('test')->raw()->aggregate(
            [
                ['$unwind' => '$value'],
                ['$match' => ['value.id' => new Regex('32.04.06.*', 'i')]],
                ['$group' => ['_id' => null, 'value' => ['$push' => '$value']]],
                ['$project' => ['value' => 1, '_id' => 0]]
            ]
        );

[Smolevich]

@mikhael-artur, can you answer me?

[mikhael-artur]

@mikhael-artur, can you answer me?

Sorry for the very late reply, I actually tried your solution last week but it didn't work. Just now, I tried it again and it worked! Looks like I made a mistake last week.

Anyway I found another solution to it

$cursor = DB::collection('ReferenceData')->raw()->aggregate(
                [
                    ['$unwind' => '$value'],
                    ['$match' => ['value.id' => ['$regex' => '^32.04.06.*']]],
                    ['$group' => ['_id' => null, 'value' => ['$push' => '$value']]],
                    ['$project' => ['value' => 1, '_id' => 0]]
                ]);

Thank you very much!

[Smolevich]

@mikhael-artur, please close issue