查询时间戳字段比使用PHP的MongoDB中的另一个时间戳字段更旧

Question

How can I obtain an object from a MongoDB collection where a specific field1 (timestamp or date) is older/newer than another specific field2 (timestamp or date)?

Given the following example object:

// MongoDB 3.2
{
  name: 'test',
  updated_on: Timestamp(1474416000, 0),
  export: {
    active: true,
    last_exported_on: Timestamp(1474329600, 0)
  }
}

This object should match a query like: where export.active is true and updated_on > export.last_exported_on

I've tried it with the aggregation framework, since I've read that $where can be very slow, but without any success.

// PHP 5.4 (and MongoDB PHP lib. http://mongodb.github.io/mongo-php-library)
$collection->aggregate([
  ['$project' => [
    'dst' => ['$cmp' => ['updated_on', 'export.last_exported_on']],
    'name' => true
  ]],
  ['$match' => ['dst' => ['$gt' => 0], 'export.active' => ['$eq' => true]]],
  ['$limit' => 1]
]);

I can change timestamps into date or anything else, but I don't see the problem in the type.

Edit: Not all objects have the last_exported_on or the export fields at all. Besides that both can be null or empty or 000000.

Answer 1

That's because after you do the $project you end up only with the dst and _id fields, so you cannot $match on export.active. You need to match on export.active before the projection. After that you need another match on the dst field.

[
    {
        $match: {
            "export.active": true
        }
    },
    {
        $project: {
            dst: {
                $cmp: [
                    "$updated_on",
                    "$export.last_exported_on"
                ]
            }
        }
    },
    {
        $match: {
            dst: 1
        }
    }
]

Edit

Alternatively, you can make sure to preserve export.active and to spare another $match:

[
    {
        $project: {
            "export.active": 1,
            cmp: {
                $cmp: [
                    "$updated_on",
                    "$export.last_exported_on"
                ]
            }
        }
    },
    {
        $match: {
            cmp: 1,
            "export.active": true
        }
    }
]