How to merge first record of a $or mongo query-CodePudding

I want to have “$or” query to combine multiple clauses with filtering conditions. Each clause returns a record set, and for each record set, only the first record should be returned and merged into final result.

Something like :

`db["mydata"].find({
                    $or:[
                        {   "device": "device1",
                            "reader": "x", 
                            "measurement": "temperature", 
                            "SourceTimeUtc":  { "$lte":ISODate("2023-01-11T06:07:47.280Z") },
                            $limit: 1
                        },
                        {   "device": "device1",
                            "reader": "y", 
                            "measurement": "temperature", 
                            "SourceTimeUtc":  { "$lte":ISODate("2023-01-11T06:07:47.280Z") },
                            $limit: 1
                        },
                        {   "device": "device1",
                            "reader": "x", 
                            "measurement": "humidity", 
                            "SourceTimeUtc":  { "$lte":ISODate("2023-01-11T06:07:47.280Z") },
                            $limit: 1
                        }
                    ]
                }
                )`

It shows grammar error

CodePudding user response：

One option is to use an aggregation pipeline with $facet, which allows you first to match only relevant documents and then to create separate pipelines according to your conditions:

db.collection.aggregate([
  {$match: {
      device: "device1",
      SourceTimeUtc: {$lte: ISODate("2023-01-11T06:07:47.280Z")},
      measurement: {$in: ["temperature", "humidity"]},
      reader: {$in: ["x", "y"]}
  }},
  {$facet: {
      tempX: [
        {$match: {reader: "x", measurement: "temperature"}},
        {$limit: 1}
      ],
      tempY: [
        {$match: {reader: "y", measurement: "temperature"}},
        {$limit: 1}
      ],
      humX: [
        {$match: {reader: "x", measurement: "humidity"}},
        {$limit: 1}
      ]
  }},
  {$project: {
      tempX: {$first: "$tempX"},
      tempY: {$first: "$tempY"},
      humX: {$first: "$humX"}
  }}
])

See how it works on the playground example