MongoDB Aggregate Help needed

Question

Maybe someone can help me. I have multiple Records of Images in my MongoDB. I want to query und update the structure of the returned data. Therefore i use MongoDB Aggregation in PHP.

My Records looks like this:

[
{
  "_id":{"$oid":"5fbe7d29300f22765c25b741"},
  "name":"192.168.1.64_01_20200708121713085_TIMING.jpg",
  "created":1602600596,
  "images":[
    {
      "name":"thumb_192.168.1.64_01_20200708121713085_TIMING.jpg",
      "type":"thumb",      
      "url":"https://firebasestorage.googleapis.com...",
      "size":12150
    },
    {
      "name":"resized_192.168.1.64_01_20200708121713085_TIMING.jpg",
      "type":"resized",
      "url":"https://firebasestorage.googleapis.com...",
      "size":136873
    }, 
    {
      "name":"192.168.1.64_01_20200708121713085_TIMING.jpg",
      "type":"original",
      "url":"https://firebasestorage.googleapis.com...",
      "size":2149262
    }
  ]
}, 
{
...
}
]

I want to convert the images-array to an object with keys and remove some additional data from the array and add a readable timestamp. So, the expected Result of the Query should be:

[
{
  "name":"192.168.1.64_01_20200708121713085_TIMING.jpg",
  "created":1602600596,
  "createdString": "2020-12-16 15:11:06",
  "images":{
    "thumb": {
      "name":"thumb_192.168.1.64_01_20200708121713085_TIMING.jpg",
      "url":"https://firebasestorage.googleapis.com..."
    },
    "resized": {
      "name":"resized_192.168.1.64_01_20200708121713085_TIMING.jpg",
      "url":"https://firebasestorage.googleapis.com..."
    }
  }
}, 
{
  "name":"192.168.1.64_01_20200708131711001_TIMING.jpg",
  "created":1602601234,
  ....
}
]

This is my Aggregation-Function in PHP:

$pipeline = [   
                [
                    '$match' => [
                        'created' => [
                            '$gte' => mktime(0,0,0,8,1,2020),
                            '$lt' => mktime(0,0,0,10,2,2020)
                         ]
                    ],
                ],              
                [
                    '$sort' => [
                        'created' => 1
                    ]               
                ],
                [
                    '$group' => [
                        '_id' => null,
                        'data' => [ '$push' => '$$ROOT' ],
                    ],
                ],
                [   // get every 10th images of all data
                    '$project' => [
                        'created' => '$created',
                        'allData' => [
                                '$map' => [
                                    'input' => [ 
                                        '$range' => [ 
                                            0, 
                                            [ '$size' => '$data' ], 
                                            10
                                        ] 
                                    ],    
                                'as' => 'index',
                                'in' => [ '$arrayElemAt' => [ '$data', '$$index' ] ]
                            ]
                        ]
                    ]               
                ],  
                [   // remove props
                    '$unset' => [
                        'allData._id',
                        'allData.images.size',
                        'allData.images.path',
                    ]                           
                ],

                [   // filter only thumb and resized images from input --> not working
                    '$project' => [
                        'created' => '$created',
                        'allData' => '$allData',
                        'data' => [
                            '$filter' => [
                                'input' => '$allData.images' , 
                                'as' => 'item', 
                                'cond' => [
                                    '$or' => [
                                        [
                                            '$eq' => [
                                              '$$item.type', 'thumb'
                                            ]
                                        ],
                                        [
                                            '$eq' => [
                                              '$$item.type', 'resized'
                                            ]
                                        ]
                                    ]
                                ]
                            ]
                        ]
                    ]
                ],
                [   // make array of objects with type as key
                    '$project' => [
                        'created' => '$created',
                        'createdString' => [
                            '$dateToString' => [
                              'format' => '%Y-%m-%d %H:%M:%S', 
                              'date' => [
                                '$toDate' => [
                                  '$multiply' => [
                                    1000, '$created'
                                  ]
                                ]
                              ]
                            ]   
                          ],
                        'images' => [
                            '$arrayToObject' => [
                              '$zip' => [
                                'inputs' => [
                                 '$images.type', '$images'
                                ]
                              ]
                            ]
                        ],                                
                    ]
                ]                               
            ];  

$aggregate = new \MongoDB\Driver\Command([
                'aggregate' => $collection,
                'cursor' => new stdClass,
                'pipeline' => $pipeline
            ]
        );
$manager = new \MongoDB\Driver\Manager();
$result = $manager->executeCommand('images', $aggregate);

My pipeline will work until removing some keys from the array, but i can't get it work to do the array to object conversation...

It would be great if someone can help me. Thank you very much.

user_5938139 · Answer

Well, I'll just focus on the array to object conversion part

here's the JS code for it,

db.collection.aggregate([
  {
    $project: {
      name: 1,
      created: 1,
      images: {
        $map: {
          input: "$images",
          as: "image",
          in: {
            $mergeObjects: [
              {
                k: "$$image.type",
                v: {
                  name: "$$image.name",
                  url: "$$image.url"
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $project: {
      name: 1,
      created: 1,
      images: {
        $arrayToObject: "$images"
      }
    }
  }
])

You'll have to convert objects to associative arrays. For simplicity, here's how the converted 'createdString' part of the final project stage looks like,

createdString: {
        "$dateToString": {
          "format": "%Y-%m-%d %H:%M:%S",
          "date": {
            "$toDate": {
              "$multiply": [
                1000,
                "$created"
              ]
            }
          }
        }
      }

Hope this helps. Maybe one of the PHP devs out there can help you out with converting this.

Finally, this is a typical output you should get with this aggregation,

[
  {
    "_id": ObjectId("5fbe7d29300f22765c25b741"),
    "created": 1.602600596e+09,
    "images": {
      "original": {
        "name": "192.168.1.64_01_20200708121713085_TIMING.jpg",
        "url": "https://firebasestorage.googleapis.com..."
      },
      "resized": {
        "name": "resized_192.168.1.64_01_20200708121713085_TIMING.jpg",
        "url": "https://firebasestorage.googleapis.com..."
      },
      "thumb": {
        "name": "thumb_192.168.1.64_01_20200708121713085_TIMING.jpg",
        "url": "https://firebasestorage.googleapis.com..."
      }
    },
    "name": "192.168.1.64_01_20200708121713085_TIMING.jpg"
  }
]

here's my attempt to convert it to PHP,

                    [  
                    '$project' => [
                        'name' => 1,
                        'created' => 1,
                        'images' => [
                                '$map' => ['input' => '$images'],    
                                'as' => 'index',
                                'in' => [ 
                                    '$mergeObjects' => [ 
                                        [
                                            'k' => '$$image.type',
                                            'v' => [
                                                'name'=> '$$image.name',
                                                'url'=> '$$image.url'
                                            ]
                                        ]
                                    ] 
                                ]
                            ]
                        ]
                    ] ,
                    [  
                        '$project' => [
                            'name' => 1,
                            'created' => 1,
                            'images' => [
                                '$arrayToObject': '$images'
                            ]
                        ]
                    ]

MongoDB Aggregate Help needed

1 Answers