Querying nested arrays in Mongo DB

Question

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.

you want to just filter the documents without changing anything on them? — Takis
– Takis, Commented Oct 13, 2021 at 11:54

Takis · Accepted Answer · 2021-10-13 17:50:44Z

1

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"}},
                     []]}]}}},
         []]}}}])

edited Oct 13, 2021 at 17:50

answered Oct 13, 2021 at 17:23

Takis

8,7552 gold badges17 silver badges28 bronze badges

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

1 Comment

Claus Nielsen Over a year ago

Phew... that looks horrible. But it does the job - thanks a lot. 😃

Collectives™ on Stack Overflow

Querying nested arrays in Mongo DB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related