hope you're fine.

I cannot seem to find a way to aggregate the following document by 'equity id'.

{
    "_id": {
        "$oid": "6001dc246192c700013e8252"
    },
    "user": "blablabla",
    "_type": "User::Individual",
    "created_at": {
        "$date": "2021-01-15T18:17:11.130Z"
    },
    "integrations": [{
        "_id": {
            "$oid": "6001dc62e7a0970001258da8"
        },
        "status": "completed",
        "authentication_failed_msg": null
    }],
    "portfolios": [{
        "_id": {
            "$oid": "6001dc62e7a0970001258da9"
        },
        "_type": "SimplePortfolio",
        "transactions": [{
            "_id": {
                "$oid": "6001dc62e7a0970001258daa"
            },
            "settlement_period": 2,
            "expenses": 0,
            "source": "integration",
            "_type": "Transaction::Equity::Buy",
            "date": {
                "$date": "2020-03-02T00:00:00.000Z"
            },
            "shares": 100,
            "price": 13.04,
            "equity_id": "abcd"
        }, {
            "_id": {
                "$oid": "6001dc62e7a0970001258dab"
            },
            "settlement_period": 2,
            "expenses": 0,
            "source": "integration",
            "_type": "Transaction::Equity::Buy",
            "date": {
                "$date": "2020-03-02T00:00:00.000Z"
            },
            "shares": 1000,
            "price": 1.03,
            "equity_id": "efgh"

I tried something like

db.collection.aggregate([{"$unwind": {'$portfolios.transactions'}},
                         {"$group" : {"_id": "$equity_id"}}])

Got error InvalidDocument: cannot encode object: {'$portfolios.transactions'}, of type: <class 'set'>

Ideally what I want a list grouped by user and equity_id and a sum of its shares. Does anyone know if the error is caused by my aggregation or the document structure?

CodePudding user response：

You should $unwind twice.

db.collection.aggregate([
  {
    "$unwind": "$portfolios"
  },
  {
    "$unwind": "$portfolios.transactions"
  },
  {
    "$group": {
      "_id": "$portfolios.transactions.equity_id"
    }
  }
])

mongoplayground