How to match $all and equal to conditions in $elemMatch operator?

[turivishal (Vishal Turi)]

I am stuck in element match in array condition,

Sample Documents:

[
  {
    "_id": 1,
    "rooms": [
      { date: ISODate("2021-02-25T00:00:00.000Z"), status: true, otherfield: true },
      { date: ISODate("2021-02-26T00:00:00.000Z"), status: true, otherfield: true },
      { date: ISODate("2021-02-27T00:00:00.000Z"), status: true, otherfield: true },
      // there will be same date's document in rooms array like below row is similar to first row
      { date: ISODate("2021-02-25T00:00:00.000Z"), status: true, otherfield: true }
    ]
  },
  {
    "_id": 2,
    "rooms": [
      { date: ISODate("2021-02-25T00:00:00.000Z"), status: true, otherfield: true },
      { date: ISODate("2021-02-26T00:00:00.000Z"), status: false, otherfield: true },
      { date: ISODate("2021-02-27T00:00:00.000Z"), status: true, otherfield: true }
    ]
  },
  {
    "_id": 3,
    "rooms": [
      { date: ISODate("2021-02-25T00:00:00.000Z"), status: true, otherfield: true }
    ]
  }
]

Condition criteria:

• rooms array should have 2 dates 2021-02-25T00:00:00.000Z & 2021-02-26T00:00:00.000Z and both should have status true

as per condition it should select first document see below screenshot,

1) Try: - this selecting 2 documents because $in means OR condition in date field, and $all will not work because date inside $elemMatch is a single sting,

db.collection.find({
  rooms: { 
    $elemMatch: { 
      date: { 
        $in: [
          ISODate("2021-02-25T00:00:00.000Z"), 
          ISODate("2021-02-26T00:00:00.000Z")
        ]
      },
      status: true 
    } 
  }
})

2) Try: - this selecting 2 documents because both condition will match in different elements of rooms,

db.collection.find({
  "rooms.date": {
    $all: [
      ISODate("2021-02-25T00:00:00.000Z"), 
      ISODate("2021-02-26T00:00:00.000Z")
    ]
  },
  rooms: { $elemMatch: { status: true } }
})

Please suggest any possible way, thank you.

[steevej (Steeve Juneau)]

Let me try.

I use variables to build my queries. This way, it is easier to correct errors by editing a single line rather than the whole query. In addition, I seldom do braces and brackets errors.

date_1 = ISODate("2021-02-25T00:00:00.000Z") ;
date_2 = ISODate("2021-02-26T00:00:00.000Z") ;
status_and_date_1 = { status : true , "date" : date_1 } ;
status_and_date_2 = { status : true , "date" : date_2 } ;
rooms_match_1 = { "rooms" : { "$elemMatch" : status_and_date_1 } } ;
rooms_match_2 = { "rooms" : { "$elemMatch" : status_and_date_2 } } ;
clauses = [ rooms_match_1 , rooms_match_2 ] ;
query = { "$and" : clauses } ;
db.rooms.find( query ) ;

The result query being:

{
	"$and" : [
		{
			"rooms" : {
				"$elemMatch" : {
					"status" : true,
					"date" : ISODate("2021-02-25T00:00:00Z")
				}
			}
		},
		{
			"rooms" : {
				"$elemMatch" : {
					"status" : true,
					"date" : ISODate("2021-02-26T00:00:00Z")
				}
			}
		}
	]
}

with the result set being:

{
	"_id" : 1,
	"rooms" : [
		{
			"date" : ISODate("2021-02-25T00:00:00Z"),
			"status" : true,
			"otherfield" : true
		},
		{
			"date" : ISODate("2021-02-26T00:00:00Z"),
			"status" : true,
			"otherfield" : true
		},
		{
			"date" : ISODate("2021-02-27T00:00:00Z"),
			"status" : true,
			"otherfield" : true
		},
		{
			"date" : ISODate("2021-02-25T00:00:00Z"),
			"status" : true,
			"otherfield" : true
		}
	]
}

[turivishal (Vishal Turi)]

Thank you for the solution, it is really helpful