Converting nested array into nested object

[Mohamed_Habib]

Hey after i did an aggregation by group aggregation i found in the last stage the data below i want to replace array by object how could I do it (by adding another stage in the pipeline) Thanks
data in the final stage :

[
   {
      "_id":332,
      "vendors":[
         {
            "count":6,
            "products":[
               {
                  "count":6
               }
            ]
         }
      ]
   },
   {
      "_id":464,
      "vendors":[
         {
            "count":10,
            "products":[
               {
                  "count":10
               }
            ]
         }
      ]
   },
   {
      "_id":538,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   },
   {
      "_id":437,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":352,
      "vendors":[
         {
            "count":8,
            "products":[
               {
                  "count":8
               }
            ]
         }
      ]
   },
   {
      "_id":498,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   },
   {
      "_id":329,
      "vendors":[
         {
            "count":8,
            "products":[
               {
                  "count":8
               }
            ]
         }
      ]
   },
   {
      "_id":467,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   },
   {
      "_id":430,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":291,
      "vendors":[
         {
            "count":5,
            "products":[
               {
                  "count":5
               }
            ]
         }
      ]
   },
   {
      "_id":192,
      "vendors":[
         {
            "count":2,
            "products":[
               {
                  "count":2
               }
            ]
         }
      ]
   },
   {
      "_id":441,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":466,
      "vendors":[
         {
            "count":10,
            "products":[
               {
                  "count":10
               }
            ]
         }
      ]
   },
   {
      "_id":445,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":465,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   }
]

data i want :

{
   "192":{
      "count":2,
      "products":{
         "count":2
      }
   },
   "329":{
      "count":8,
      "products":{
         "count":8
      }
   },
   "498":{
      "count":9,
      "products":{
         "count":9
      }
   },
   "291":{
      "count":5,
      "products":{
         "count":5
      }
   },
   "332":{
      "count":6,
      "products":{
         "count":6
      }
   },
   "437":{
      "count":13,
      "products":{
         "count":13
      }
   },
   "352":{
      "count":8,
      "products":{
         "count":8
      }
   },
   "445":{
      "count":13,
      "products":{
         "count":13
      }
   },
   "538":{
      "count":9,
      "products":{
         "count":9
      }
   },
   "466":{
      "count":10,
      "products":{
         "count":10
      }
   },
   "464":{
      "count":10,
      "products":{
         "count":10
      }
   },
   "465":{
      "count":9,
      "products":{
         "count":9
      }
   },
   "430":{
      "count":13,
      "products":{
         "count":13
      }
   },
   "467":{
      "count":9,
      "products":{
         "count":9
      }
   },
   "441":{
      "count":13,
      "products":{
         "count":13
      }
   }
}

PS: i did a group in the pipeline to group by id then vendor then product using push so i got the array
using the following code

result=collection.aggregate([
      { "$project": { "object.definition.metadata.affected.@family":1,"object.definition.metadata.affected.product":1,"job_id":1,"_id":0} }, #"object.definition.metadata.affected.product":1,
      { "$group": {
          "_id": {
              "job_id":"$job_id",
              "family": "$object.definition.metadata.affected.@family",
              "product": "$object.definition.metadata.affected.product"
    
        },
        "count": {
          "$sum": 1
        }
      }
    },
    {
      "$group": {
        "_id": {
          "job_id": "$_id.job_id",
          "vendor": "$_id.family",
      #    "product":"$_id.product"
        },
        "count": {
          "$sum": "$count"
        },
        "products": {
          "$push": {
            "product": "$_id.product",
            "count": "$count",
            "products": "$products"
          }
        }
      }
    },
    {
      "$group": {
        "_id": "$_id.job_id",
   
        "vendors": {
          "$push": {
            "vendor": "$_id.vendor",
            "count": "$count",
            "products": "$products"
          }
        }
      }
    },

so i want to save values not in an array (push) but in a nested object thanks

[Takis]

Query

• uses $field to take the values we need
• create the new structure the way we want it, and replace the root

Test code here

aggregate(
[{"$replaceRoot": 
    {"newRoot": 
      {"$arrayToObject": 
        [[{"k": {"$toString": "$_id"},
            "v": {"count": {"$arrayElemAt": ["$vendors.count", 0]},
                   "products": 
                    {"count": {"$arrayElemAt": ["$vendors.count", 0]}}}}]]}}}])

[Mohamed_Habib]

@Takis

[{"$project": 
    {"_id": 0,
      "root-array": 
      {"$map": 
        {"input": {"$objectToArray": "$ROOT"},
          "in": ["$m.k", "$m.v"],
          "as": "m"}}}},
  {"$set": 
    {"root-array": 
      [{"$map": 
          {"input": "$root-array",
            "in": 
            {"$cond": 
              [{"$eq": [{"$arrayElemAt": ["$this", 0]}, "_id"]},
                {"$toString": {"$arrayElemAt": ["$this", 1]}},
                {"$let": 
                  {"vars": 
                    {"c_value": 
                      {"$getField": 
                        {"field": "count",
                          "input": 
                          {"$arrayElemAt": [{"$arrayElemAt": ["$this", 1]}, 0]}}}},
                    "in": 
                    {"count": "$c_value",
                      "products": {"count": "$c_value"}}}}]}}}]}},
  {"$replaceRoot": {"newRoot": {"$arrayToObject": "$root-array"}}}])

Thank you that is exactly what i need but when i try to update the value of the key based on the key the following this format (update_one based with “job_id” which is the key of the last result )
it update only one document and insert the other document
the desired format is below
i used field=date +“.job_id” field to filter
value_to_update=date+.$.+field_to_update (i tried .$. and also .)
and a loop in the list of job to update the document thanks

{“_id”:{“$oid”:“61a4b0cb5b60664cba5ef11d”},“29-11-2021”:[{“job_id”:467,“overlap”:{“430”:10,“437”:60,“441”:6,“445”:25,“464”:3,“467”:9,“498”:9},“non_exclusive”:0,“exclusive”:0,“No_of_object”:9,“activity”:{},“job_context”:{},“vendor_product_version”:{}},{“job_id”:498,“overlap”:{“430”:10,“437”:60,“441”:6,“445”:25,“464”:3,“467”:9,“498”:9},“non_exclusive”:0,“exclusive”:0,“No_of_object”:9,“activity”:{},“job_context”:{},“vendor_product_version”:{}},{“job_id”:430,“overlap”:{“430”:16,“437”:290,“441”:30,“445”:128,“464”:6,“467”:10,“498”:10},“non_exclusive”:0,“exclusive”:0,“No_of_object”:13,“activity”:{},“job_context”:{},“vendor_product_version”:{}},{“job_id”:437,“overlap”:{“430”:290,“437”:13156,“441”:1374,“445”:5902,“464”:156,“467”:60,“498”:60},“non_exclusive”:0,“exclusive”:0,“No_of_object”:13,“activity”:{},“job_context”:{},“vendor_product_version”:{}},{“job_id”:441,“overlap”:{“430”:30,“437”:1374,“441”:144,“445”:618,“464”:18,“467”:6,“498”:6},“non_exclusive”:0,“exclusive”:0,“No_of_object”:13,“activity”:{},“job_context”:{},“vendor_product_version”:{}},{“job_id”:445,“overlap”:{“430”:128,“437”:5902,“441”:618,“445”:2653,“464”:75,“467”:25,“498”:25},“non_exclusive”:0,“exclusive”:0,“No_of_object”:13,“activity”:{},“job_context”:{},“vendor_product_version”:{}},{“job_id”:464,“overlap”:{“430”:6,“437”:156,“441”:18,“445”:75,“464”:9,“467”:3,“498”:3},“non_exclusive”:0,“exclusive”:0,“No_of_object”:10,“activity”:{},“job_context”:{},“vendor_product_version”:{}},{“job_id”:9000,“overlap”:{“9000”:1},“non_exclusive”:0,“exclusive”:0,“No_of_object”:0,“activity”:{},“job_context”:{},“vendor_product_version”:{}}]}

[Takis]

i updated the previous answer and i made it simpler, but i dont understand the second question, if possible write it like the previous question, with sample data and expected output.

[Mohamed_Habib]

@Mohamed_Habib

[
   {
      "_id":332,
      "vendors":[
         {
            "count":6,
            "products":[
               {
                  "count":6
               }
            ]
         }
      ]
   },
   {
      "_id":464,
      "vendors":[
         {
            "count":10,
            "products":[
               {
                  "count":10
               }
            ]
         }
      ]
   },
   {
      "_id":538,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   },
   {
      "_id":437,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":352,
      "vendors":[
         {
            "count":8,
            "products":[
               {
                  "count":8
               }
            ]
         }
      ]
   },
   {
      "_id":498,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   },
   {
      "_id":329,
      "vendors":[
         {
            "count":8,
            "products":[
               {
                  "count":8
               }
            ]
         }
      ]
   },
   {
      "_id":467,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   },
   {
      "_id":430,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":291,
      "vendors":[
         {
            "count":5,
            "products":[
               {
                  "count":5
               }
            ]
         }
      ]
   },
   {
      "_id":192,
      "vendors":[
         {
            "count":2,
            "products":[
               {
                  "count":2
               }
            ]
         }
      ]
   },
   {
      "_id":441,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":466,
      "vendors":[
         {
            "count":10,
            "products":[
               {
                  "count":10
               }
            ]
         }
      ]
   },
   {
      "_id":445,
      "vendors":[
         {
            "count":13,
            "products":[
               {
                  "count":13
               }
            ]
         }
      ]
   },
   {
      "_id":465,
      "vendors":[
         {
            "count":9,
            "products":[
               {
                  "count":9
               }
            ]
         }
      ]
   }
]

i have the following data obtained after aggregation which contain nested object and array
l2={185: [{‘vendors’: ‘monal’,
‘count’: 1,
‘products’: [{‘product’: ‘monal’,
‘count’: 1,
‘versions’: [{‘version’: ‘', ‘count’: 1}]}]},
{‘vendors’: ‘nibbleblog’,
‘count’: 1,
‘products’: [{‘product’: ‘nibbleblog’,
‘count’: 1,
‘versions’: [{‘version’: ‘3.7.1c’, ‘count’: 1}]}]},
{‘vendors’: ‘ftpd_project’,
‘count’: 1,
‘products’: [{‘product’: ‘ftpd’,
‘count’: 1,
‘versions’: [{‘version’: ‘0.2.1’, ‘count’: 1}]}]}],
190:[ {‘vendors’: ‘oracle’,
‘count’: 11,
‘products’: [{‘product’: ‘identity_manager_connector’,
‘count’: 1,
‘versions’: [{‘version’: ‘9.0’, ‘count’: 1}]},
{‘product’: ‘enterprise_repository’,
‘count’: 1,
‘versions’: [{‘version’: ‘12.1.3.0.0’, ‘count’: 1}]},
{‘product’: ‘georaster’,
‘count’: 1,
‘versions’: [{‘version’: ‘18c’, ‘count’: 1}]},
{‘product’: ‘communications_diameter_signaling_router’,
‘count’: 4,
‘versions’: [{‘version’: ‘8.2.1’, ‘count’: 1},
{‘version’: ‘8.2’, ‘count’: 1},
{‘version’: ‘8.0.0’, ‘count’: 1},
{‘version’: ‘8.1’, ‘count’: 1}]},
{‘product’: ‘enterprise_manager_base_platform’,
‘count’: 3,
‘versions’: [{‘version’: ‘13.2.0.0.0’, ‘count’: 1},
{‘version’: ‘13.3.0.0.0’, ‘count’: 1},
{‘version’: ‘12.1.0.5.0’, ‘count’: 1}]},
{‘product’: ‘goldengate_stream_analytics’,
‘count’: 1,
‘versions’: [{‘version’: '’, ‘count’: 1}]}]},
{‘vendors’: ‘opendesign’,
‘count’: 1,
‘products’: [{‘product’: ‘drawings_software_development_kit’,
‘count’: 1,
‘versions’: [{‘version’: ‘*’, ‘count’: 1}]}]}]}

i want to update it in the following document which already exists (so using update_one and filter by job_id which are the keys of my list of dictionaries above 185 et 190
the collection before update is :
{
“24-11-2021”: [{
“job_id”: 185,
“overlap”: {
“467”: 9,
“498”: 9,
“430”: 10,
“437”: 61,
“441”: 6,
“445”: 25,
“464”: 3
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}, {
“job_id”: 190,
“overlap”: {
“467”: 9,
“498”: 9,
“430”: 10,
“437”: 61,
“441”: 6,
“445”: 25,
“464”: 3
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}, {
“job_id”: 430,
“overlap”: {
“467”: 10,
“498”: 10,
“430”: 16,
“437”: “295”,
“441”: 30,
“445”: 128,
“464”: 6
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}, {
“job_id”: 437,
“overlap”: {
“467”: 61,
“498”: 61,
“430”: 295,
“437”: 13618,
“441”: 1398,
“445”: 6005,
“464”: 159
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}, {
“job_id”: 441,
“overlap”: {
“467”: 6,
“498”: 6,
“430”: 30,
“441”: 144,
“445”: 618,
“464”: 18,
“437”: 1398
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}, {
“job_id”: 445,
“overlap”: {
“467”: 25,
“498”: 25,
“430”: 128,
“441”: 618,
“445”: 2653,
“464”: 75,
“437”: 6005
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}, {
“job_id”: 464,
“overlap”: {
“467”: 3,
“498”: 3,
“430”: 6,
“441”: 18,
“445”: 75,
“464”: 9,
“437”: 159
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}, {
“job_id”: 9000,
“overlap”: {
“9000”: 1
},
“non_exclusive”: 0,
“exclusive”: 0,
“No_of_object”: 0,
“activity”: {},
“job_context”: {},
“vendor_product_version”: {}
}]
}

i want to update the field ‘vendor_product_version’ following its job_id which is the key of my previous list of dictionary thanks

[Seghosimhe_David]

Hello Mohamed, I want to achieve an array of Objects the way you did it above, How can I do that