javascript - $gte and $lte on arrays

Question

I have schema

{ summaries:
  [
   {
     type: 'temp',
     min: 10,
     max: 40
   },
   {
     type: 'humidity',
     min: 3,
     max: 40
   }
  ],
  thresholds: [
  {
    type: 'temp',
    low: -10,
    high: 40
  },
  {
    type: 'humidity',
    low: 9,
    high: 30
  }
 ]
}

This object above is one record in collection. I want to get records if summaries.type == 'temp' and thresholds.type == 'temp' and summaries.min > thresholds.low

Summaries objects compare with thresholds object must be same type. temp compare with temp, hum compare with hum. Have any summaries object temp that min < any thresholds object temp will be returned

Example:

{ summaries:
  [
   {
     type: 'temp',
     min: 10,
     max: 40
   },
   {
     type: 'humidity',
     min: 3,
     max: 40
   }
  ],
  thresholds: [
  {
    type: 'temp',
    low: -10,
    high: 40
  },
  {
    type: 'humidity',
    low: 9,
    high: 30
  }
 ]
}

will be returned temp min = 10, threshold low = -10.

{ summaries:
  [
   {
     type: 'temp',
     min: 10,
     max: 40
   },
   {
     type: 'humidity',
     min: 3,
     max: 40
   }
  ],
  thresholds: [
  {
    type: 'temp',
    low: 20,
    high: 40
  },
  {
    type: 'humidity',
    low: 9,
    high: 30
  }
 ]
}

will be not returned because temp min = 10, threshold temp low = 20 although threshold hum low = 9

标签： javascriptnode.jsmongodbmongoose

Answer 1

您可以使用以下查询$expr

db.collection.find({
  "$expr": {
    "$gt": [
      {
        "$arrayElemAt": [
          "$summaries.min",
          { "$indexOfArray": ["$summaries.type", "temp"] }
        ]
      },
      {
        "$arrayElemAt": [
          "$thresholds.low",
          { "$indexOfArray": ["$thresholds.type", "temp"] }
        ]
      }
    ]
  }
})