0

Below is my query which works on two tables to get data

db.accounts.aggregate([
    {
        $lookup: {
            from: 'accounts_destinations',
            localField: "_id",
            foreignField: "acc_Dest_id",
            as: "accountDestinationDetails"
         },
     },
  {
    $group: {
      _id: "$type",
      record: {
           $push:  {_id: "$_id", description: "$description" , costing: "$accountDestinationDetails.costing"}
       }
    }
  },
  {
    "$group": {
      "_id": null,
      "data": {
        "$push": {
          "k": {
            $toString: "$_id"
          },
          "v": "$record"
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$arrayToObject": "$data"
      }
    }
  }
])

Here I'm facing the issue that the accountDestinationDetails.costing is returning as an Array, whereas I want it as a plain object.

//Output of the above query

{
    "domestic" : [ 
        {
            "_id" : ObjectId("2s05fea52f804ab99b12e7c0"),
            "description" : "Domestic account with local mapping",
            "costing" : [ 
                35
            ]
        }, 
        {
            "_id" : ObjectId("2s05aet52f740ab99b12f6h0"),
            "description" : "Domestic account with non mapping",
            "costing" : []
        }
    ],
    "international" : [ 
       {
            "_id" : ObjectId("2s05tqz32f740fr00b12f6h0"),
            "description" : "International account with local mapping",
            "costing" : []
        }
    ]
}

the only problem here is costing field is coming as an array (costing[], costing[35]).

What I tried

I tried to unwind the costing field like below

 {$unwind: "$accountDestinationDetails.costing"}

but it disturbs the join and I get only records which have mapping instead of a LEFT join. Below is the output if I use unwind

{
        "domestic" : [ 
            {
                "_id" : ObjectId("2s05fea52f804ab99b12e7c0"),
                "description" : "Domestic account with local mapping",
                "costing" : [ 
                    35
                ]
            }
        ],
}
The JOKER
  • 453
  • 8
  • 21
  • 1
    Have you used preserveNullAndEmptyArrays... `{$unwind: {path: "$accountDestinationDetails.costing", preserveNullAndEmptyArrays: true }}` – Ashh Apr 15 '20 at 07:34
  • @Ashh - yes i have tried it, it returns the result will array object even if there is no value. like costing[], costing[35]. It is not getting rid of array structure – The JOKER Apr 15 '20 at 07:40
  • @Ashh $unwind: {path: "$accountDestinationDetails", preserveNullAndEmptyArrays: true }} if I try to unwind the complete accountDestinationDetails object, then I'm getting the desired result. May be you can raise answer for that, with the changes. – The JOKER Apr 15 '20 at 09:23

0 Answers0