Question 115361 in Mongodb Question posted in
The official documentation can be found here.

mongoDB count deeply nested elements on more then one condition

Please, help , I need to find count() of all _a[]._p[] elements having at least one of:

_a[]._p[].s.d.t[].dateP=2022 and _a[]._p[].s.d.t[].tF="N"

and

_a[]._p[].s.c.t[].dateP=2022 and _a[]._p[].s.c.t[].tF="N"

in following type of document:

{
"_id": ObjectId("5c05984246a0201286d4b57a"),
f: "x",
"_a": [
  {
    "_p": [
      {
        "pid": 2,
        "s": {
          "d": {
            "t": [
              {
                id: 1,
                "dateP": "20200-09-20",
                
              },
              {
                id: 2,
                "dateP": "2022-09-20",
                "tF": "N"
              }
            ]
          },
          "c": {
            "t": [
              {
                id: 3,
                "dateP": "20300-09-22"
              },
              {
                id: 4,
                "dateP": "2022-09-23",
                "tF": "N"
              }
            ]
          }
        }
      }
    ]
  }
]
}

In my attempt I can count only the documents that partially match the condition , but not sure if this is correct when there is more nested arrays and not sure how to do it faster and count the _p elements inside the _a:

db.collection.count({ "_a._p.s.c.t":{ $elemMatch:{ tF:"N" , dateP: /^2022/i  }  } })

The expected result from playground need to look as follow:

 { total: 1 }

Since the _a._p having s.d.t with id:2 and s.c.t with id:4 match the above condition

Playground

Tags:

2

Answers


  1. 0

    and the solution should be like:

    update: only count element

    db.collection.aggregate([
  {
    $project: {
      sct: {
        "$size": "$_a._p.s.c.t"
      },
      scd: {
        "$size": "$_a._p.s.d.t"
      }
    }
  },
  
])

    MONGO_PALYGROUND

    Login or Signup to reply.
  2. 0

    Here’s one way you can do it without "$unwind", although the "$reduce" nesting levels seem very error prone. I hope you test this with lots of data before depending on it.

    db.collection.aggregate([
  {
    "$match": {
      "_a._p.s.d.t": {
        "$elemMatch": {
          "dateP": {"$regex": "^2022"},
          "tF": "N"
        }
      },
      "_a._p.s.c.t": {
        "$elemMatch": {
          "dateP": {"$regex": "^2022"},
          "tF": "N"
        }
      }
    }
  },
  {
    "$project": {
      "ap": "$_a._p"
    }
  },
  {
    "$project": {
      "docCount": {
        "$reduce": {
          "input": "$ap",
          "initialValue": 0,
          "in": {
            "$sum": [
              "$$value",
              {
                "$reduce": {
                  "input": "$$this",
                  "initialValue": 0,
                  "in": {
                    "$sum": [
                      "$$value",
                      {
                        "$cond": [
                          {
                            "$and": [
                              {
                                "$reduce": {
                                  "input": "$$this.s.c.t",
                                  "initialValue": false,
                                  "in": {
                                    "$or": [
                                      "$$value",
                                      {
                                        "$and": [
                                          {"$eq": ["$$this.tF", "N"]},
                                          {
                                            "$regexMatch": {
                                              "input": "$$this.dateP",
                                              "regex": "^2022"
                                            }
                                          }
                                        ]
                                      }
                                    ]
                                  }
                                }
                              },
                              {
                                "$reduce": {
                                  "input": "$$this.s.d.t",
                                  "initialValue": false,
                                  "in": {
                                    "$or": [
                                      "$$value",
                                      {
                                        "$and": [
                                          {"$eq": ["$$this.tF", "N"]},
                                          {
                                            "$regexMatch": {
                                              "input": "$$this.dateP",
                                              "regex": "^2022"
                                            }
                                          }
                                        ]
                                      }
                                    ]
                                  }
                                }
                              }
                            ]
                          },
                          1,
                          0
                        ]
                      }
                    ]
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "totalCount": {"$sum": "$docCount"}
    }
  }
])

    Try it mongoplayground.net.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search