How to return only matching arrays item with MongoDB PHP Library

Question

I am new to MongoDB and currently I am using MongoDB hosted at Atlas.

My code uses PHP MongoDB library.

The database has a collection called "transactions" which has such a structure:

[  
   {  
      "product":"X",
      "customer":"John",
      "history":[  
         {  
            "payment_date":"2017-07-01",
            "status":"paid"
         },
         {  
            "payment_date":"2017-08-02",
            "status":"paid"
         },
         {  
            "payment_date":"2017-09-03",
            "status":"paid"
         }
      ]
   },
   {  
      "product":"Y",
      "customer":"Mary",
      "history":[  
         {  
            "payment_date":"2017-07-01",
            "status":"paid"
         },
         {  
            "payment_date":"2017-08-02",
            "status":"paid"
         },
         {  
            "payment_date":"2017-09-03",
            "status":"paid"
         }
      ]
   }
]

Based on this answer https://stackoverflow.com/a/39549711/1229477, I have the following code trying to find the history that a payment_date equal 2017-08-02

$manager = new \MongoDB\Driver\Manager(getenv('MONGODB_DSN'));
$db = new \MongoDB\Database($manager, getenv('MONGODB_DATABASE'));
$cursor = $db->transaction->find(
    ['history.payment_date' => '2017-08-02'],
    ['_id' => 0, 'history' => ['$elemMatch' => ['history.payment_date' => '2017-08-02']]]
);
return $cursor->toArray();

But it returns no items!

It I try the code in MongoShell it works!

I also tried some code using aggregate:

$manager = new \MongoDB\Driver\Manager(getenv('MONGODB_DSN'));
$db = new \MongoDB\Database($manager, getenv('MONGODB_DATABASE'));
$cursor = $db->transaction->aggregate(
    [
        [
            '$match' => [
                'history.payment_date' => "2017-08-02"
            ]
        ],
        [
            '$project' => [
                '_id' => 0,
                'history' => 1
            ]
        ]
    ]
);
return $cursor->toArray();

I followed a lot of links and I haven't success with any of them! So I would appreciate if you do not give me another link, because I have visited hundreds yet!

Answer 1

I would give this a try:

$cursor = $db->transaction->aggregate(
    [
        [
            '$match' => [
                'history.payment_date' => "2017-08-02" // limit returned documents to ones with matches
            ]
        ],
        [
            '$unwind' => "$history" // split each document into multiple documents based on history
        ],
        [
            '$match' => [
                'history.payment_date' => "2017-08-02" // only return documents that match
            ]
        ]
    ]
);