Concat int array field inside an array of object into one string field in mongodb aggregate

Question

I would like to concat int array field values inside an array of objects into one string field after dividing them (by 10).

Heres the existing document format:

{ 
  "no" : "2020921008981",  
  "date" : ISODate("2020-04-01T05:19:02.263+0000"), 
  "sale" : { 
   "soldItems" : [
       {
         "itemRefId" : "5b55ac7f0550de00210a3b24", 
         "soldPrice" : NumberInt(800), 
       },
       {
         "itemRefId" : "5b55ac7f0550de00210a3b25", 
         "soldPrice" : NumberInt(1000), 
       }
     ] 
   }
 }

Expected result :

{ 
  "no" : "2020921008981",  
  "date" : ISODate("2020-04-01T05:19:02.263+0000"),  
  "priceList" : "8.0 \n 10.0"
}

The attempt with $reduce :

 priceList: {
            $reduce: {
                input: "$sale.soldItems.soldPrice",
                initialValue: "",
                in: {
                    $cond: [ { "$eq": [ { $toString: { $divide: [ "$$value", 10 ] } }, "" ] }, "$$this", { $concat: [ { $toString: { $divide: [ "$$value", 10 ] } }, "\n", "$$this" ] } ]
                }
            }
        }

But end up getting "errmsg" : "$divide only supports numeric types, not string and double" error. Any idea would be appreciated.

Have posted an answer based on the requirement. See if that works. Also in your expected o/p ... 800/10 is 80 not 8.0 and similarly 1000/10 is 100 not 10. Did u mean to dive by 100? — ambianBeing
– ambianBeing, Commented Apr 12, 2020 at 14:24

Dĵ ΝιΓΞΗΛψΚ · Accepted Answer · 2020-04-13 06:22:30Z

0

db.case.aggregate([
    {
        $set: {
            priceList: {
                $reduce: {
                    input: {
                        $map: {
                            input: "$sale.soldItems.soldPrice",
                            in: { $toString: { $divide: ["$$this", 10] } }
                        }
                    },
                    initialValue: "",
                    in: { $concat: ["$$value", "$$this", " \n "] }
                }
            }
        }
    },
    {
        $project: {
            _id: 0,
            no: 1,
            date: 1,
            priceList: 1
        }
    }
])

edited Apr 13, 2020 at 6:22

user8847697

answered Apr 12, 2020 at 14:36

Dĵ ΝιΓΞΗΛψΚ

5,8044 gold badges16 silver badges30 bronze badges

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

2 Comments

user8847697 Over a year ago

Thanks buddy this works as intended. I just put a question for a new scenario related to this. stackoverflow.com/questions/61173858/…

Dĵ ΝιΓΞΗΛψΚ Over a year ago

@dineshalwis i'll have a look at it as soon as i can. what platform you building your app in? node/php/.net? just curious :-)

ambianBeing · Accepted Answer · 2020-04-12 14:22:05Z

Try the following aggregation query, where the idea is to:

First divide the field soldPrice by 10 or required divisor using $divide
Convert it into string and concat using $toString and $concat
the appender \n gets appended after each reduce op,remove that from the end using $rtrim
create the new field using $addFields

Query:

db.collection.aggregate([
  {
    $addFields: {
      "itemPriceList": {
        $rtrim: {
          input: {
            $reduce: {
              input: "$salesOrder.purchaseItems",
              initialValue: "",
              in: {
                $concat: [
                  "$$value",
                  {
                    $toString: {
                      $divide: [
                        "$$this.soldPrice",
                        10
                      ]
                    }
                  },
                  "\n"
                ]
              }
            }
          },
          chars: "\n"
        }
      }
    }
  }
]);

Result:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "caseNumber": "2020921008981",
    "itemPriceList": "80\n100",
    "salesOrder": {
      "purchaseItems": [
        {
          "itemRefId": "5b55ac7f0550de00210a3b24",
          "soldPrice": 800
        },
        {
          "itemRefId": "5b55ac7f0550de00210a3b25",
          "soldPrice": 1000
        }
      ]
    },
    "startTime": ISODate("2016-05-18T16:00:00Z")
  }
]

Plaground Test Link

Collectives™ on Stack Overflow

Concat int array field inside an array of object into one string field in mongodb aggregate

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related