Comparison between nested child field with outside field

Question

I want to compare

requested_completion_utc > status_changed_utc.time and status_changed_utc.status=6, where the status_changed_utc.time is from the record that has status_changed_utc.status = 6.

status_changed_utc.time is nested and requested_completion_utc is outside the array. How do I compare nested field with outside field.

{
    "_id": { "oid": "5ce726ae92e2247db561a2f2" },
    "created_utc": { "date": "2019-05-23T23:03:10.000+0000" },
    "requested_completion_utc": { "date": "2019-05-23T00:00:00.000+0000" },
    "status_changed_utc": [

      {
        "status": 1,
        "time": { "date": "2019-05-23T23:03:10.000+0000" }
      },
      {
        "status": 2,
        "time": { "date": "2019-05-23T23:05:03.000+0000" }
      },
      {
        "status": 4,
        "time": { "date": "2019-05-23T23:05:06.000+0000" }
      },
      {
        "status": 5,
        "time": { "date": "2019-05-23T23:05:07.000+0000" }
      },
      {
        "status": 6,
        "time": { "date": "2019-05-23T23:05:09.000+0000" }
      }
    ]
  },
  {
    "_id": { "oid": "5ce726ae92e2247db561a2f3" },
    "created_utc": { "date": "2019-05-23T23:03:10.000+0000" },
    "requested_completion_utc": { "date": "2019-05-25T06:00:00.000+0000" },
    "status_changed_utc": [
      {
        "status": 1,
        "time": { "date": "2019-05-23T23:03:10.000+0000" }
      },
      {
        "status": 2,
        "time": { "date": "2019-05-23T23:05:03.000+0000" }
      },
      {
        "status": 3,
        "time": { "date": "2019-05-23T23:05:06.000+0000" }
      },
      {
        "status": 4,
        "time": { "date": "2019-05-25T05:05:07.000+0000" }
      },
      {
        "status": 6,
        "time": { "date": "2019-05-25T05:55:09.000+0000" }
      }
    ]
  }

I have tried doing this but nothing comes up

db.test.find( 
  { 
    $expr: { 
      $gt: [ 
        "$requested_completion_utc", 
        {"$status_changed_utc.time", "$status_changed_utc.status": 6} 
      ] 
    } 
  } 
);

user_12066677 · Answer

You can use an $elemMatch operator to make this type of check. Also don't forget to select the inner properties of the objects (in your case date). Docs are here: https://docs.mongodb.com/manual/reference/operator/query/elemMatch/

db.test.find({
  "status_changed_utc": {
    $elemMatch: {
      "status": 6,
      "time.date": {
        $gt: "requested_completion_utc.date"
      }
    }
  }
})

Comparison between nested child field with outside field

1 Answers