Error in $or and $arrayElemAt

Working with Data
1

Hi,

I want to get the records where the last value of my object is an array and in this array, i check “STATUS” attribute (string value).

I find this query :

db.getCollection('Test').find({
        $expr: { "$eq": [ { "$arrayElemAt": [ "$CLOSING.STATUS", -1 ] }, "WRITABLE" ]}
})

This query is ok but i want put this into $or

db.getCollection('Test').find({
      $or: [ 
           {  CLOSING: null  },
           {  $expr: { "$eq": [ { "$arrayElemAt": [ "$CLOSING.STATUS", -1 ] }, "WRITABLE" ]} }
     ]
})

However, it’s not successfull. MongoDB return this :

Error: error: {
	"ok" : 0,
	"errmsg" : "$arrayElemAt's first argument must be an array, but is string",
	"code" : 28689,
	"codeName" : "Location28689"
}

Can you help me ?

Thanks in advance.

2

Hi @Florian_CHENE ,

For me the queries work with the following document:

{
  "_id": {
    "$oid": "6310a1622786520682ce36e8"
  },
  "CLOSING": [
    {
      "STATUS": "WRITABLE"
    }
  ]
}

Maybe some of the documents you have does not have “CLOSING” as array and then obviously elementArrayAt fails.

Ty

3

This is CLOSING example :

{
            "UID" : "377D3DE711704D03BE0C3008F6641A8D",
            "CREATIONTIMESTAMP" : ISODate("2022-09-01T12:14:00.249Z"),
            "USER" : {
                "UID" : "9121B944EFA7471DB313C88A8526034B",
                "IDENTIFICATION" : {
                    "DENOMINATION1" : "CHENE",
                    "DENOMINATION2" : "Florian"
                }
            },
            "STATUS" : "WRITABLE"
        }

i’m agree with you but without $or my queries is ok. How can i fix my problem ?

4

Is it possible that sometimes CLOSING is a subdocument with a STATUS field? I can get the error mentioned if I run db.test.insertOne( { "CLOSING": { "STATUS": "WRITABLE" } } )

image
image1401×469 62 KB

5

I changed my attribute “CLOSING” by “CLOSED” and the queries work because i have another records with CLOSING attribute (object type) but i don’t understand why my querie work without $or. Can you explain me ?

Thanks for your answer.

6

Queries are not executed directly, but the query planner first makes optimizations on them.

I think your first query is optimized so that the documents without an array in “CLOSING.STATUS” are filtered. but when you use “CLOSING:null” it gives up that optimization and uses all documents whether STATUS is an array or not, causing this error.

7

Okay, i understand your answer.

Thanks all