How to use nested query using &or with &any in mongodb?-CodePudding

I'm learning mongoDB queries and have a problem given my collection looks like:

"filename": "myfile.png",
"updatedCoordinates": [
{
    "xmin": 537.640869140625,
    "xmax": 1049.36376953125,
    "ymin": 204.90736389160156,
    "ymax": 714.813720703125,
    "label": "LABEL_0",
    "status": "UNCHANGED"
},
{
    "xmin": 76.68355560302734,
    "xmax": 544.8860473632812,
    "ymin": 151.90313720703125,
    "ymax": 807.1371459960938,
    "label": "LABEL_0",
    "status": "UNCHANGED"
}],

"predictedCoordinates": [
{
    "xmin": 537.640869140625,
    "xmax": 1049.36376953125,
    "ymin": 204.90736389160156,
    "ymax": 714.813720703125,
    "status": "UNCHANGED",
    "label": "LABEL_0"
}
]

and the eligible values of status are: UNCHANGED, CHANGED, UNDETECTED

How would I query: Get all the in instances from the db where status == CHANGED / UNDECTED for ANY of the values inside either updatedCoordinates or predictedCoordinates ?

It means that if status of minimum of 1 entry inside either updated or predicted is set to changed or undetected, it's eligible for my query.

I tried:

{"$or":[{"updatedCoordinates.status": "CHANGED"}, {"predictedCoordinates.status": "CHANGED"}]}

With Python dict, I can query as:

def find_eligible(single_instance:dict):
    for key in ["predictedCoordinates", "updatedCoordinates"]:
        for i in single_instance[key]:
            if i["status"] in ["CHANGED", "UNDETECTED]: return True
    return False

But retrieving 400K instances first just to filter a few ones is not a good idea.

CodePudding user response：

Try running this query:

db.collection.find({
  "$or": [
    {
      "updatedCoordinates.status": {
        "$in": [
          "CHANGED",
          "UNDETECTED"
        ]
      }
    },
    {
      "predictedCoordinates.status": {
        "$in": [
          "CHANGED",
          "UNDETECTED"
        ]
      }
    }
  ]
})

Mongodb playground link: https://mongoplayground.net/p/Qda-G5L1mbR

CodePudding user response：

Simple use of Mongo's dot notation allows access to nested values in arrays / objects, like so:

db.collection.find({
  "updatedCoordinates.status": "CHANGED"
})

Mongo Playground