mongoDB - Query to aggregate nested array of objects

user_11939387 0
Viewed 88

I've set up a mongoDB instance an trying to query a document that has a nested array in it. Here is a snippet of the document structure:

{
  "_id": {
    "$oid": "624ee3332e72fcb84abb0c09"
  },
  "ticker": "AAPL",
  "quoteSummary": {
    "result": [
      {
        "incomeStatementHistoryQuarterly": {
          "incomeStatementHistory": [
            {
              "maxAge": 1,
              "endDate": {
                "raw": 1640390400,
                "fmt": "2021-12-25"
              },
              "totalRevenue": {
                "raw": 123945000000,
                "fmt": "123.94B",
                "longFmt": "123,945,000,000"
           }]
         },
         "defaultKeyStatistics": {
              "maxAge": 1,
              "priceHint": {
                "raw": 2,
                "fmt": "2",
                "longFmt": "2"
              },
             "enterpriseValue": {
                "raw": 2970925924352,
                "fmt": "2.97T",
                "longFmt": "2,970,925,924,352"
              },
              "forwardPE": {
                "raw": 26.193598,
                "fmt": "26.19"
              }
         }
       }]
  }

}

I would like only to select certain fields of the "defaultKeyStatistcs" Field within the nested array. Maybe also some arrays underneath. Could you please tell me how to ?

I tried the following way, but keyStatistics is always null:

db.collectioin.aggregate(
    [{$project:
        {_id:0, 
        ticker:'AAPL', 
        keyStatistics:
        { $arrayElemAt: [
            '$querySummary.result', 
            {
                'defaultKeyStatistics' : 1
            } 
        ]}
        }
    }]
);

Result:

[
  {
    "ticker": "AAPL",
    "keyStatistics": null
  }
]

What am I doing wrong ?

graphql mongodb
1 Answers

found a workaround solution, but I think there is a more optimal way:

db.my_collection.aggregate([
    {
        '$match': {
            'ticker': 'AAPL'
        }
    }, 
    {
        '$addFields': {
            'result': '$quoteSummary.result'
    }
    },
    {
        '$project': {
            '_id': 0, 
            'ticker': 0, 
            'quoteSummary': 0
        }
    }
])

this leads to a satisfying result, which I would have wanted to achieve:

{
    "result": [
        {
            "incomeStatementHistoryQuarterly": {
                "incomeStatementHistory": [
                    {
                        "maxAge": 1,
                        "endDate": {
                            "raw": 1640390400,
                            "fmt": "2021-12-25"
                        },
                        "totalRevenue": {
                            "raw": 123945000000,
                            "fmt": "123.94B",
                            "longFmt": "123,945,000,000"
                        }]
            },
            "defaultKeyStatistics": {
                "maxAge": 1,
                "priceHint": {
                    "raw": 2,
                    "fmt": "2",
                    "longFmt": "2"
                },
                "enterpriseValue": {
                    "raw": 2970925924352,
                    "fmt": "2.97T",
                    "longFmt": "2,970,925,924,352"
                },
                "forwardPE": {
                    "raw": 26.193598,
                    "fmt": "26.19"
                }
            }
        }
    ]
}
user_119393870
Related