Querying nested arrays in Mongo DB-CodePudding

Given these documents:

{ 
    "sections" : [
        {
            "name" : "section 1", 
            "questions" : [
                {"q" : "color", "a" : "black"}, 
                {"q" : "size", "a" : "large"}
            ]
        }
    ]
}

and

{ 
    "sections" : [
        {
            "name" : "section 1", 
            "questions" : [
                {"q" : "color", "a" : "white"}, 
                {"q" : "size", "a" : "large"}
            ]
        }, 
        {
            "name" : "section 2", 
            "questions" : [
                {"q" : "color", "a" : "black"}, 
                {"q" : "size", "a" : "small"}
            ]
        }
    ]
}

how do I write a query for documents where the question (q) "color" is answered (a) with something starting with "b" AND the question "size" is answerd with "large" in the SAME section?

I expected the query below to do the trick, and only match the first document - but it matches both :-(

{
    sections: {
        $elemMatch: {
            questions: {
                $elemMatch: {
                    "q" : "color", 
                    "a" : {$regex: "b.*"}
                }, 
                $elemMatch: {
                    "q" : "size", 
                    "a" : "large"
                }
            }
        }
    }
}

The only solution I have found so far is to use an aggregation pipeline $unwind'ing sections, but then I need to reconstruct the original documents afterwards, and I also haven't found a good way to do that.

CodePudding user response：

aggregate

db.collection.find({
  $and: [
    {
      "sections.questions": {
        "q": "color",
        "a": "black"
      }
    },
    {
      "sections.questions": {
        "q": "size",
        "a": "large"
      }
    }
  ]
})

mongoplayground

CodePudding user response：

Query

2 nested filters
the nested one checks if and color="^b.*" (starting with b), size="large"
the outer filter checks if nested filter return empty results or not
if nested didnt returned empty results document passes

*maybe there is a declarative smaller query, but this works ok also

Test code here

aggregate(
[{"$match": 
    {"$expr": 
      {"$ne": 
        [{"$filter": 
            {"input": "$sections.questions",
              "cond": 
              {"$and": 
                [{"$in": [{"q": "size", "a": "large"}, "$$this"]},
                  {"$ne": 
                    [{"$filter": 
                        {"input": "$$this",
                          "cond": 
                          {"$and": 
                          [{"$eq": ["$$t.q", "color"]},
                          {"$regexMatch": {"input": "$$t.a", "regex": "^b.*"}}]},
                          "as": "t"}},
                     []]}]}}},
         []]}}}])