MongoDB Aggregation pipeline, update an existing elements in an array after string split

Question

Sample Doc :

{
  bioupdate: [
    {
      date: "02/03/2020",
      ts: "1583133621197-02/03/2020_15:20:21",
      status: "1"
    },
    {
      date: "02/03/2020",
      ts: "1583135570542-02/03/2020_15:52:50",
      status: "1"
    },
    {
      date: "02/03/2020",
      ts: "1583135586272-02/03/2020_15:53:06",
      status: "0"
    },
    {
      date: "21-03-2020:17:35:08",
      ts: 1584783308231,
      status: "1"
    }
  ]
}

Below is the code I've tried with aggregation pipeline splitting the string with first '-' and take the first element which is epoch timestamp and save it to the same field to an existing array.

db.novelmodel.aggregate([
  {$match: {pin: "JAIN"}},
  {
    $project: {
    pin: 1,
     bioupdate: {
      $filter: {
         input: "$bioupdate",
         as: "bioupdateArray",

         cond: { $and: [
          {$arrayElemAt:[{$split:["$$bioupdateArray.ts", "-"]}, 0]}
           ] }
      }
   }
  }
},

  {$out:"novelmodel"}
]);

It gives me an error message: "errmsg" : "$split requires an expression that evaluates to a string as a first argument, found: double".I'm not sure how filter to take only the date which has delimiter '-' in a string

You may want to use the $map aggregation operator instead of the $filter. $map allows you replace the existing string value with the transformed value for all elements of the array. — prasad_
– prasad_, Commented Apr 2, 2020 at 2:39
@prasad_ do you have sample code to try it out, as i am quite new to MongoDB. — JAVA Programmer
– JAVA Programmer, Commented Apr 2, 2020 at 3:24

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-04-02 04:50:19Z

2

Your issue should be the last document which has ts as type NumberLong() instead of string, which is what throwing an error, Try below query :

db.collection.aggregate([
  /** Re-create 'bioupdate' with updated data */
  {
    $addFields: {
      bioupdate: {
        $map: {
          input: "$bioupdate", // Iterate on 'bioupdate' array
          in: {
            $cond: [
              { $eq: [{ $type: "$$this.ts" }, "string"] }, // Check if 'ts' field in current object is of type string
              /** If YES, Split it & get first element from array, it will be string if you want to keep is as number convert using '$long' */
              {
                $mergeObjects: [
                  "$$this",
                  {
                    ts: {
                      $toLong: {
                        $arrayElemAt: [{ $split: ["$$this.ts", "-"] }, 0]
                      }
                    }
                  }
                ]
              },
              /** If NO, Just to make sure convert existing to long & merge with actual object & push entire object back to array */
              { $mergeObjects: ["$$this", { ts: { $toLong: "$$this.ts" } }] }
            ]
          }
        }
      }
    }
  }
]);

Test : MongoDB-Playground

answered Apr 2, 2020 at 4:50

whoami - fakeFaceTrueSoul

18.1k6 gold badges36 silver badges51 bronze badges

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

6 Comments

whoami - fakeFaceTrueSoul Over a year ago

Note : I've mentioned it's existing as NumberLong() but I think it's existing as double in your actual doc, So then end you can convert to double instead of long using $toDouble..

JAVA Programmer Over a year ago

thanks, it worked like a charm, one last thing when i try to add this "{$out:"novelmodel"}" at the bottom of your code to save the new merged records, it emptied out all the other pins.

whoami - fakeFaceTrueSoul Over a year ago

@JAVAProgrammer : what do you mean by emptied we’re going ‘addfields’ so entire doc should exist did you try with project instead?

whoami - fakeFaceTrueSoul Over a year ago

@JAVAProgrammer : If you aggregation is successful $out as an end stage will completely drop the collection novelmodel if it already exists & re-create a new collection with same name with results of aggregation pipeline, Just in case if there is not collection with name novelmodel then $out will create a new collection. Did you look into results of aggregation before $out ?

JAVA Programmer Over a year ago

hey thanks!!!!! thanks alot, it works, my bad, i did with one particular id using$match which causes only that particular id saved and the rest of it gone, later when i remove $match, it succesfully updated to all the ids. thanks alot, it works.

|

Collectives™ on Stack Overflow

MongoDB Aggregation pipeline, update an existing elements in an array after string split

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related