Mongo Nested array field selection

Question

Below is mongodb document is like this and these nested array field selection:

object :

    {
        "_id": {
            "$oid": "5de775b53ec85e73da2b6d8a"
        },
        "vpg_id": 2,
        "year": 2019,
        "am_data": {
            "822": {
                "am_name": "Unmanaged ",
                "no_of_mnths": 12,
                "total_invoice": 14476.15,
                "total_bv_invoice": 1840,
                "opp_won_onetime_amt": 0,
                "one_time_quota": 0,
                "recurring_quota": 200,
                "opp_won_rec_amt": 0,
                "avg_total_invoice": 1206.3458333333333,
                "avg_total_bv_invoice": 153.33333333333334,
                "avg_opp_won_onetime_amt": 0,
                "avg_one_time_quota": 0,
                "avg_opp_won_rec_amt": 0,
                "avg_recurring_quota": 16.666666666666668
            },
           "2155": {
                "am_name": "Daniel Schiralli",
                "no_of_mnths": 12,
                "total_invoice": 396814.66000000003,
                "total_bv_invoice": 577693.3200000001,
                "opp_won_onetime_amt": 4792.5,
                "one_time_quota": 14400,
                "recurring_quota": 4800,
                "opp_won_rec_amt": 345,
                "avg_total_invoice": 33067.888333333336,
                "avg_total_bv_invoice": 48141.11000000001,
                "avg_opp_won_onetime_amt": 399.375,
                "avg_one_time_quota": 1200,
                "avg_opp_won_rec_amt": 28.75,
                "avg_recurring_quota": 400
            }
        }
    }

I want to select only no_of_mnths and am_name from all am_data arrays.

The keys 822 and 2155 is dynamic.

It will change so i cannot directly give it in query. How i can approach to get this data. Don't want

Any help ?

Serkan Arslan · Accepted Answer · 2019-12-06 06:24:23Z

1

You can use $objectToArray operator to get rid of the dynamic keys.

db.getCollection('Test').aggregate([

    { $project: {"keys": { "$objectToArray": "$$ROOT.am_data" }} },
    { $unwind : "$keys"},
    { $project: {"am_name":"$keys.v.am_name", "no_of_mnths":"$keys.v.no_of_mnths" } }
])

Result:

[{
    "_id" : ObjectId("5de775b53ec85e73da2b6d8a"),
    "am_name" : "Unmanaged ",
    "no_of_mnths" : 12
},
{
    "_id" : ObjectId("5de775b53ec85e73da2b6d8a"),
    "am_name" : "Daniel Schiralli",
    "no_of_mnths" : 12
}]

answered Dec 6, 2019 at 6:24

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

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

Comments

Jairo Malanay · Accepted Answer · 2019-12-06 05:06:02Z

the key should not change. base on your description you need to adjust the schema, instead of am_data Object it should be an array instead.

{
     "_id": {
         "$oid": "5de775b53ec85e73da2b6d8a"
        },
     "vpg_id": 2,
     "year": 2019,
     "am_data": [
        {
            "id": "822",
             "am_name": "Unmanaged ",
             "no_of_mnths": 12,
             "total_invoice": 14476.15,
             "total_bv_invoice": 1840,
             "opp_won_onetime_amt": 0,
             "one_time_quota": 0,
             "recurring_quota": 200,
             "opp_won_rec_amt": 0,
             "avg_total_invoice": 1206.3458333333333,
             "avg_total_bv_invoice": 153.33333333333334,
             "avg_opp_won_onetime_amt": 0,
             "avg_one_time_quota": 0,
             "avg_opp_won_rec_amt": 0,
             "avg_recurring_quota": 16.666666666666668
        },
        {
            "id": "2155",
             "am_name": "Daniel Schiralli",
             "no_of_mnths": 12,
             "total_invoice": 396814.66000000003,
             "total_bv_invoice": 577693.3200000001,
             "opp_won_onetime_amt": 4792.5,
             "one_time_quota": 14400,
             "recurring_quota": 4800,
             "opp_won_rec_amt": 345,
             "avg_total_invoice": 33067.888333333336,
             "avg_total_bv_invoice": 48141.11000000001,
             "avg_opp_won_onetime_amt": 399.375,
             "avg_one_time_quota": 1200,
             "avg_opp_won_rec_amt": 28.75,
             "avg_recurring_quota": 400
        }
     ]
}

Collectives™ on Stack Overflow

Mongo Nested array field selection

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related