MongoDB query inside nested objects

Question

I am trying to query my document which looks like this

"approvals" : {
    "REV" : "",
    "SS" : "",
    "ABC" : {
        "status" : "Sent for approval",
        "approved_at" : "",
        "approved_by" : "",
    },
    "XYZ" : {
        "status" : "Approved",
        "approved_by" : "[email protected]",
        "approved_at" : ISODate("2020-01-06T09:48:22.777Z"),
    },....
}

The Approval object can have multiple sub documents like ABC,XYZ...etc, I need to query if this "approval" has any sub document whose "status" is not in APPROVED state

I am using MongoDb 2.6.10

Odin
– Odin

2020-01-06 11:45:59 +00:00
Commented Jan 6, 2020 at 11:45 — Odin
– Odin, Commented Jan 6, 2020 at 11:45

mickl · Accepted Answer · 2020-01-06 10:41:56Z

1

You need to run $objectToArray to scan your dynamic keys. Then you need $anyElementTrue with $map to check if there's any Approved value:

db.collection.find(
    { 
        $expr: { 
            $anyElementTrue: { 
                $map: { 
                    input: { $objectToArray: "$approvals" },
                    in: { $ne: [ "$$this.v.status", "Approved" ] } 
                }
            } 
        }
})

Mongo Playground

answered Jan 6, 2020 at 10:41

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Odin Over a year ago

Getting Error: error: { "$err" : "Can't canonicalize query: BadValue unknown top level operator: $expr", "code" : 17287 }

mickl Over a year ago

@Odin you need to upgrade your MongoDB server at least to 3.4.4 since you need $objectToArray as the only way to dynamically read values when keys are unknown

Collectives™ on Stack Overflow

MongoDB query inside nested objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related