How can I join array to object by id mongodb

Working with Data
,
1

I would like to merge the by_status array into the pendencias array

An example is shown below.

{
"_id" : ObjectId("620ce7b822efbc6fc6b7879a"),
"id_processo" : "entrada_veiculo",
"automovel" : [
    {
        "tipo" : "celta",
        "count" : 5
    }
],
"pendencias" : [
    {
        "id_pendencia" : "pagamento_concluido",
        "nome" : "pagamento concluido",
        "count" : 1
    },
    {
        "id_pendencia" : "pagamento_pendente",
        "nome" : "pagamento pendente",
        "count" : 4
    }
],
"by_status" : [
    {
        "id_pendencia" : "pagamento_pendente",
        "status" : "pendente",
        "count" : 4
    },
    {
        "id_pendencia" : "pagamento_concluido",
        "status" : "concluido",
        "count" : 1
    }
]
}

and I want to make the above document as below.

{
    "_id" : ObjectId("620ce7b822efbc6fc6b7879a"),
    "id_processo" : "entrada_veiculo",
    "automovel" : [
        {
            "tipo" : "celta",
            "count" : 5
        }
    ],
    "pendencias" : [
        {
            "id_pendencia" : "pagamento_concluido",
            "nome" : "pagamento concluido",
            "count" : 1,
            "by_status": [
                {
                    "id_pendencia" : "pagamento_concluido",
                    "status" : "concluido",
                    "count" : 1
                },
                {
                    "id_pendencia" : "pagamento_concluido",
                    "status" : "concluido cartao de credito",
                    "count" : 2 
                }   
            ]
        },
        {
            "id_pendencia" : "pagamento_pendente",
            "nome" : "pagamento pendente",
            "count" : 4,
            "by_status": [
                {
                    "id_pendencia" : "pagamento_pendente",
                    "status" : "pendente",
                    "count" : 1
                }   
            ]
        }
    ]
}

Here is the my code

db.estacionamento.update( { id_processo: "entrada_veiculo" },
  [ 
      { 
          $set: { 
              automovel: {
                  $reduce: {
                      input: { $ifNull: [ "$automovel", [] ] }, 
                      initialValue: { automovel: [], update: false },
                      in: {
                          $cond: [ { $eq: [ "$$this.tipo", "celta" ] },
                                   { 
                                     automovel: { 
                                        $concatArrays: [
                                            "$$value.automovel",
                                            [ { tipo: "$$this.tipo", count: { $add: [ "$$this.count", 1 ] } } ]
                                        ]
                                      }, 
                                      update: true
                                   },
                                   { 
                                      automovel: { 
                                         $concatArrays: [ "$$value.automovel", [ "$$this" ] ] 
                                      }, 
                                      update: "$$value.update" 
                                   }
                          ]
                      }
                  }
              }
          }

      },
      { 
          $set: { 
              automovel: { 
                  $cond: [ { $eq: [ "$automovel.update", false ] },
                           { $concatArrays: [ "$automovel.automovel", [ { tipo: "celta", count: 1 } ] ] },
                           { $concatArrays: [ "$automovel.automovel", [] ] }
                  ] 
              }
          }
      },
     { 
          $set: {  
              pendencias: {
                  $reduce: {
                      input: { $ifNull: [ "$pendencias", [] ] }, 
                      initialValue: { pendencias: [], update: false },
                      in: {
                          $cond: [ { $eq: [ "$$this.id_pendencia", "pagamento_pendente" ] },
                                   { 
                                     pendencias: { 
                                        $concatArrays: [
                                            "$$value.pendencias",
                                            [ { id_pendencia: "$$this.id_pendencia",nome: "$$this.nome", count: { $add: [ "$$this.count", 1 ] } } ]
                                        ]
                                      }, 
                                      update: true
                                   },
                                   { 
                                      pendencias: { 
                                         $concatArrays: [ "$$value.pendencias", [ "$$this" ] ] 
                                      }, 
                                      update: "$$value.update" 
                                   }
                          ]
                      }
                  }
              },
          }

      },
      { 
          $set: { 
              pendencias: { 
                  $cond: [ { $eq: [ "$pendencias.update", false ] },
                           { $concatArrays: [ "$pendencias.pendencias", [ { id_pendencia: "pagamento_pendente",nome: "pagamento pendente", count: 1 } ] ] },
                           { $concatArrays: [ "$pendencias.pendencias", []] }
                  ] 
              }

          }
      },
      
    { 
      $set: {
              by_status: {
                  $reduce: {
                      input: { $ifNull: [ "$by_status", [] ] }, 
                      initialValue: { by_status: [], update: false },
                      in: {
                          $cond: [ { $eq: [ "$$this.status", "concluido" ] },
                                   { 
                                     by_status: { 
                                        $concatArrays: [
                                            "$$value.by_status",
                                            [ {id_pendencia: "$$this.id_pendencia", status: "$$this.status", count: { $add: [ "$$this.count", 1 ] } } ]
                                        ]
                                      }, 
                                      update: true
                                   },
                                   { 
                                      by_status: { 
                                         $concatArrays: [ "$$value.by_status", [ "$$this" ] ] 
                                      }, 
                                      update: "$$value.update" 
                                   }
                          ]
                      }
                  }
              },
              
          }

      },
      { 
          $set: { 
              by_status: { 
                  $cond: [ { $eq: [ "$by_status.update", false ] },
                           { $concatArrays: [ "$by_status.by_status", [ {id_pendencia: "pagamento_concluido", status: "concluido", count: 1 } ] ] },
                           { $concatArrays: [ "$by_status.by_status", [] ] }
                  ] 
              }
          },  
 
      }
  ],

  {
        upsert: true,
  }
)
2

@guilherme_alves1 ,

Ok so I think I worked something out. I am not a huge fan of those tricky updates in pipelines as I believe a dedicated migration script/code will be more error prune.

However, this is my statement, please test on your dev!

db.estacionamento.update( { id_processo: "entrada_veiculo" },[{$addFields: {
    pendencias: {
     $map: {
      input: '$pendencias',
      as: 'pendencia',
      'in': {
       $mergeObjects: [
        '$$pendencia',
        {
         by_status: {
          $filter: {
           input: '$by_status',
           cond: {
            $eq: [
             '$$this.id_pendencia',
             '$$pendencia.id_pendencia'
            ]
           }
          }
         }
        }
       ]
      }
     }
    }
   }}, {$project: {
    by_status: 0
   }}]);

Now, I am not sure what is the meaning of an upsert in this case, as if there is no process_id with this id the upsert will create a document with pendencias of null… Does that make sense??

Thanks
Pavel