Query deeply nested document with dynamic key

Question

I want to return a specific subdocument which is nested in a second level array.

My problems are:

1. The document is deeply nested
2. The fields (keys) are dynamic and based on req.body values

The collection looks something like this (simplified values for the example):

leagues:[
 {
 "_id": "693ae6dc320b2bb92635361e",
 "cards": [
 {
 "_id": "600056266",
 "users": [
 {
 "_id": "EafXiJktdKSM45OcnniU4gpcGWc2",
 "vote": {
 "234": {
 "W": "56",
 "M": "543",
 "R": "322"
 },
 "235": {
 "W": "554",
 "M": "332",
 "R": "454"
 }
 }
 },
 {
 "_id": "EafXiJktdKSM45OcnniU4gpcGWPR4",
 "vote": {
 "234": {
 "W": "54",
 "M": "69",
 "R": "987"
 },
 "235": {
 "W": "85",
 "M": "751",
 "R": "65"
 }
 }
 }
 ]
 }
 ]
 },
 {
 "_id": "693ae6dc320b2bb92633ew23",
 "cards": [
 {
 "_id": "600056266",
 "users": [
 {
 "_id": "EafXiJktdKSM45OcnniU4gpcGWc2",
 "vote": {
 "234": {
 "W": "56",
 "M": "543",
 "R": "322"
 },
 "235": {
 "W": "554",
 "M": "332",
 "R": "454"
 }
 }
 },
 {
 "_id": "EafXiJktdKSM45OcnniU4gpcGWPR4",
 "vote": {
 "234": {
 "W": "54",
 "M": "69",
 "R": "987"
 },
 "235": {
 "W": "85",
 "M": "751",
 "R": "65"
 }
 }
 }
 ]
 }
 ]
}
]

I would like to write a query that could e.g. return the vote object from user with "_id": "EafXiJktdKSM45OcnniU4gpcGWc2" for card "_id": "600056266" inside league with "_id": "693ae6dc320b2bb92635361e".

I have spent hours and hours looking for some solution, but anything I have tried is not satisfying ($match, $elemMatch, projections).

Projection seems to work but only return the value from the element and not the complete 'vote' object, $match seems to work only for first level, and so on.

Can't we just build a Model.find() with multiple nested criteria?

I am starting to think that my collection structure is not good since it is hard to query. I thought it seemed logic but maybe no...

Thanks to anyone who can give a hint towards the solution

Answer 1

I think it will be difficult with a find but an aggregation pipeline could do the job. What about this one:

db.leagues.aggregate([
 { $match: { _id: "693ae6dc320b2bb92635361e" } },
 {
 $project: {
 cards: {
 $filter: {
 input: "$cards",
 cond: { $eq: [ "$$this._id", "600056266" ] }
 }
 }
 }
 },
 { $unwind: "$cards" },
 {
 $project: {
 users: {
 $filter: {
 input: "$cards.users",
 cond: { $eq: [ "$$this._id", "EafXiJktdKSM45OcnniU4gpcGWc2" ] }
 }
 }
 }
 },
 { $unwind: "$users" },
 { $replaceWith: "$users.vote" }
])

Mongo Playground

You may set first stage to

 {
 $match: {
 _id: "693ae6dc320b2bb92635361e",
 "cards._id": "600056266",
 "cards.users._id": "EafXiJktdKSM45OcnniU4gpcGWc2"
 }
 }

It may give better performance. It returns only the documents which will return a result, i.e. the following stages get only documents which let to an output.

• LeSmoox: Thank you sir! Indeed it works. I am wondering, is it mandatory to use an aggregation pipeline to get it done ? I am quite new to Mongo DB and it looks surprising to me that i can't just find this data without doing the pipeline operations. Wouldn't it be possible to use the positional operator since i know all the the _ids but not their position in the array? I know in advance that only ONE document can be returned based on the criteria i have, there will never be 2 documents with all these _id queued together.
• Wernfried Domscheit: You have two levels of nested arrays, I don't think you can do it with the positional operator. What is your problem using an aggregation pipeline?
• LeSmoox: No no, i am ok if it is the best way, it just surprised me to have to do all these 'stages' just to get some nested data, but it's fine, i have to get used to it. Before having data nested in arrays for this collection, i had a structure based on object like so: {leagues:{ _id:"XXX", cards:{ card1Id:{ user1Id:vote, user2Id:vote }, card2Id:{ user1Id:vote, user2Id:vote } } I thought it was more intuitive but it only seems to work fine to write data, finding it seems to be quite complicated. Thanks!
• Wernfried Domscheit: Yes, typically it's a poor design to have dynamic keys. You cannot create any index on such fields and usually it requires much more code to work with it.
• aneroid: Minor typo in your aggregation pipeline. In the first cards filter you have as: "card" but you've used $$this._id. And your users filter is fine, since it defaults to $$this but in your playground example you called it user - in case you wanted to use a consistent style for both filters.
• LeSmoox: I saw that too, and corrected it like this: $project: { cards: { $filter: { input: "$cards", as: "card", cond: { $eq: ["$$card._id", cardId] }, }, }, },
• Wernfried Domscheit: It's corrected, thanks for noting it.
• LeSmoox: Quick follow up question, to update the 'vote' object, isn't it enought to add a stage { $set: { vote } } that i am providing in the request ? I am trying but even though the returned object is correct, the database doesn't save anything. Should i use updateOne instead of aggregate ?
• Wernfried Domscheit: The syntax would be db.collection.updateOne({ _id: "693ae6dc320b2bb92635361e" }, [{....}]). However, it cannot contain $unwind stage, see Update with an Aggregation Pipeline You can achieve it with $map, better open a new question for it.

Answer 2

You can do this using a find query with a projection but it's harder to debug and a bit ugly. (I recommend using an aggregation pipeline like in Wernfried Domscheit's answer.)

1. Use a regular find with the sub-objects' fields
2. Then a projection with nested $map's & $filter's to get just the matching sub arrays for the card and user. Which are just aggregation stages being used in the project part of find.

The result is a nested list and if you expect at most one match per document, then combine those with an arrayElemAt with 0 to only get the first one.

db.collection.find({
 _id: "693ae6dc320b2bb92635361e",
 "cards._id": "600056266",
 "cards.users._id": "EafXiJktdKSM45OcnniU4gpcGWc2"
},
{
 // all this is in the `project` part of the find query
 _id: 0,
 votes: {
 $map: {
 input: {
 $filter: {
 input: "$cards",
 as: "c",
 cond: { $eq: ["$$c._id", "600056266"] }
 }
 },
 as: "resultCards",
 in: {
 $map: {
 input: {
 $filter: {
 input: "$$resultCards.users",
 as: "u",
 cond: { $eq: ["$$u._id", "EafXiJktdKSM45OcnniU4gpcGWc2" ] }
 }
 },
 as: "user",
 in: "$$user.vote"
 }
 }
 }
 }
})

Mongo Playground

• Wernfried Domscheit: Well, it uses find() rather than aggregate(), however, $map and $filter are aggregation stages rather than Query Predicates.
• aneroid: Noted. I've clarified that these are aggregation stages being used in the projection. But giving the OP the option of doing it with find - and that I don't recommend it; as mentioned at the beginning of my answer.