write mongodb driver query with php fetch records from embedded array

Question

Filter item from records array it should only return two items because records.score = 100 having only two items instead this it return me all the records. Can you please help on this thanks.

I have multiple records wherein I would like to fetch only filter records. where I am doing mistake please guide and suggest.

stdClass Object
(
    [_id] => e5s65d5e5s65d5s65d44f12
    [records] => Array
        (
            [0] => stdClass Object
                (
                    [date] => MongoDB\BSON\UTCDateTime Object
                        (
                            [milliseconds] => 1609923848000
                        )

                    [score] => 100
                    [country] => US
                    [state] => Connecticut
                    [city] => Berlin
                    
                )

            [1] => stdClass Object
                (
                    [date] => MongoDB\BSON\UTCDateTime Object
                        (
                            [milliseconds] => 1609923501000
                        )

                    [score] => 100
                    [country] => US
                    [state] => California
                    [city] => Barstow
                    
                )

            [2] => stdClass Object
                (
                    [date] => MongoDB\BSON\UTCDateTime Object
                        (
                            [milliseconds] => 1609923157000
                        )

                    [score] => 145
                    [country] => US
                    [state] => Alabama
                    [city] => Alexander City
                    
                )

            [3] => stdClass Object
                (
                    [date] => MongoDB\BSON\UTCDateTime Object
                        (
                            [milliseconds] => 1609923108000
                        )

                    [score] => 150
                    [country] => US
                    [state] => Alaska
                    [city] => Anchorage
                    
                )

        )

)


$mng = new MongoDB\Driver\Manager("mongoatlas/");
$filter = ['records.score' => '100'];

$query = new MongoDB\Driver\Query($filter, ['sort' => ['records.date' => 1], 'limit' => 6]);

$rows = $mng->executeQuery("db.table", $query);

expected result should be only two item whose state is Connecticut, California because their score are 100

Answer 1

Try this one

$command = new MongoDB\Driver\Command([ 'aggregate' => 'collection',

'pipeline' => [
    ['$unwind' => '$records'],
    ['$match' => ['records.score' => '100']],
    ['$sort' => ['records.date' => 1]],
    ['$limit' => 6]
],
'cursor' => new stdClass,

]);

$cursor = $mng->executeCommand('database', $command);