Concat int and string array fields which are in different arrays

Question

{ 
   "no" : "2020921008981",  
   "date" : ISODate("2020-04-01T05:19:02.263+0000"), 
   "sale" : { 
   "soldItems" : [
       {
         "itemId" : "5b55ac7f0550de00210a3b24", 
         "qty" : NumberInt(1), 
       },
       {
         "itemId" : "5b55ac7f0550de00210a3b25", 
         "qty" : NumberInt(2), 
       }
     ],
  "items" : [
       {
         "_id" : ObjectId("5b55ac7f0550de00210a3b24"),
         unit :"KG"
       },
       {
         "_id" : ObjectId("5b55ac7f0550de00210a3b25"),
         unit :"ML"
       }

     ]
   }
 }

Desired output :

{
 "no" : "2020921008981",
 "sale" : {}
 "qtyList" : "1 KG \n 2 ML"
}

In order to build itemQtyList output field, two fields from different arrays (string and int) should be used. Couldn't find any reference for doing that. Any idea would be appreciated.

a possible soultion: This kind of operation could easily be achieved in the programming language (javascript, java etc.) you are using mongodb with. Are the array lengths of purchaseItems and items same and position values are in sync? — ambianBeing
– ambianBeing, Commented Apr 12, 2020 at 15:47

Ashh · Accepted Answer · 2020-04-13 04:21:03Z

1

You can use below aggregation

db.collection.aggregate([
  { "$project": {
    "itemQtyList": {
      "$reduce": {
        "input": { "$range": [0, { "$size": "$sale.soldItems" }] },
        "initialValue": "",
        "in": {
          "$concat": [
            "$$value",
            { "$cond": [{ "$eq": ["$$this", 0] }, "", " \n "] },
            { "$toString": {
              "$arrayElemAt": [
                "$sale.soldItems.qty",
                "$$this"
              ]
            }},
            " ",
            { "$arrayElemAt": ["$sale.items.unit", "$$this"] }
          ]
        }
      }
    }
  }}
])

MongoPlayground

edited Apr 13, 2020 at 4:21

user8847697

answered Apr 12, 2020 at 16:24

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

3 Comments

user8847697 Over a year ago

Im still getting the null value but can't figure out where went wrong..

Ashh Over a year ago

@dineshalwis I have given you the link as well. kindly verify

dinesh alwis Over a year ago

please refer this question. stackoverflow.com/questions/61231005/…

Collectives™ on Stack Overflow

Concat int and string array fields which are in different arrays

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related