Iterate over double nested array in mongodb

Question

I have the following document:

{
  "_ID": "234",
  "sub": {
    "abcId": "123",
    "subElems": [
      {
        "abcId": "345",
        "subElems": [
          {
            "abcId": "676",
            "subElems": [
              {
                "abcId": "567"
              },
              {
                "abcId": "567b",
                "crit1": false,
                "crit2": "someId",
                "crit3": "2013-07-30T22:00:00.000+0000",
                "crit4": "ABC"
              },
              {
                "abcId": "567c",
                "crit1": true,
                "crit3": "2013-07-30T22:00:00.000+0000",
                "crit4": "ABC"
              },
              {
                "abcId": "567d",
                "crit1": true,
                "crit3": "2018-11-30T22:00:00.000+0000",
                "crit4": "ABC"
              }
            ]
          },
                    {
            "abcId": "678",
            "subElems": [
              {
                "abcId": "568"
              },
              {
                "abcId": "568b",
                "crit1": false,
                "crit2": "someId",
                "crit3": "2013-07-30T22:00:00.000+0000",
                "crit4": "ABC"
              },
              {
                "abcId": "568c",
                "crit1": true,
                "crit3": "2013-07-30T22:00:00.000+0000",
                "crit4": "ABC"
              },
              {
                "abcId": "568d",
                "crit1": true,
                "crit3": "2018-11-30T22:00:00.000+0000",
                "crit4": "ABC"
              }
            ]
          }
        ]
      }
    ]
  }
}

I need to iterate over all sub.subElems.0.subElems (in this case the nested documents with abcId 676 and 678)

Each of those 2 again have an array of objects called subElems.

I need to find all ojects in this subElems array that match the following criteria (AND, not OR):

crit1 = true
crit4 = ABC
crit2 = null
crit3 = more than a year ago

Given the collectin above, I need:

567c and 568c

I have a tried various aggregations.

I thought I could do something like unwind sub.subElems.0.subElems and then access its subElems array and iterate over that.

What I don't know how to do is select a nested aray of objects (whose path I know) but then iterate over the elements in this array and again iterate over an array in those objects.

Thanks for help and tips!

Ashh · Accepted Answer · 2018-10-18 12:44:40Z

3

You can try below aggregation

Basically you need to use $map with each array and $filter with the last one.

db.collection.aggregate([
  { "$addFields": {
    "sub.subElems": {
      "$map": {
        "input": "$sub.subElems",
        "as": "s1",
        "in": {
          "abcId": "$$s1.abcId",
          "subElems": {
            "$map": {
              "input": "$$s1.subElems",
              "as": "s2",
              "in": {
                "abcId": "$$s2.abcId",
                "subElems": {
                  "$filter": {
                    "input": "$$s2.subElems",
                    "as": "s3",
                    "cond": {
                      "$and": [
                        { "$eq": ["$$s3.crit1", true] },
                        { "$eq": ["$$s3.crit4", "ABC"] },
                        { "$eq": ["$$s3.crit2", undefined] },
                        { "$lt": ["$$s3.crit3", "2018-11-30T22:00:00.000+0000"] }
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }}
])

edited Oct 18, 2018 at 12:44

answered Oct 18, 2018 at 11:09

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

3 Comments

user3629892 Over a year ago

Hi, thanks a lot! How do I change this so that I get the entire document back?

user3629892 Over a year ago

And why excactly did you put "abcId": "$$s1.abcId" ? I removed that and i still seems to work.

Ashh Over a year ago

Yes It will work but your abcId key will be vanished from the nested arrays and You will only get the subElems which we have defined inside the in expression of $map function

Collectives™ on Stack Overflow

Iterate over double nested array in mongodb

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related