I want to push the previous state (if the values changed) during an upsert into an array history (atomar) but no matter what I try, I can't find a clean solution.

Below you find simplified attempts:

Attempt 1: Use $set and $concatArrays

final var timestamp = Document.parse(String.format("{$set: {timestamp: %d}}", time));
final var pointCount = Document.parse("{$set: {"  
            "data_point_count: {$ifNull: [{$add: [\"$data_point_count\", 1]}, 1]}   }}");

final var history = Document.parse(String.format("{$set: {"  
   "history: {$cond: {"  
      "if: {$eq: [\"$timestamp\", %d]},"  
      "then: \"$history\","                 // no changes, keep untouched
      "else: {$concatArrays: ["  
         "{$ifNull: [\"$history\", []]},"   // without, history stays null

         // Bug: this adds an empty object on 1st upsert (insert)
         "[{timestamp: \"$timestamp\", data_point_count: \"$data_point_count\"}]"  
      "]}"  
   "}}"  
"}}", time));

final var update = Arrays.asList(setHistory, timestampUpdate, meanNinetyPercentileUpdateCommand, setDataPointCount);
// If I don't use Arrays.asList() commands like `$ifNull` are written into the database
//final var update = Updates.combine(setDataPointCount, setHistory); does not interpret the commands

final var options = new UpdateOptions().upsert(true);
targetCollection.updateOne(filter, update, options);

Problem:

• when the upsert is executed the first time (i.e. insert), an empty object is added to history: [{}]
• on subsequent updates, everything works: [{}, {12345, 2}, {23456, 3}]

Attempt 2: Use $set and $push

final var update = new Document();

final var set = new Document();
set.put("timestamp", time);
set.put("data_point_count", Document.parse("{$ifNull: [{$add: [\"$data_point_count\", 1]}, 1]}"));
update.put("$set", set);

update.put("$push", Document.parse("{history: {timestamp: \"$timestamp\", data_point_count: \"$data_point_count\"}}}"));

final var options = new UpdateOptions().upsert(true);
targetCollection.updateOne(filter, update, options);

Problem:

• Does not interpret commands like \"$timestamp\" and $ifNull and just writes e.g. $ifNull into the database.
• I cannot use Arrays.asList() or else I get the error: Unrecognized pipeline stage name: '$push', but without the Arrays.asList the commands are not executed but just written to the database, e.g. data_point_count: {$ifNull: ...}

Question: How can I use one upsert command which:

• creates history: [] or history: null or just no history field during insert
• when the timestamp changes in the update add the state before the update object to the history array without any empty objects (see attempt 1).

Update (suggestion from @Gibbs):

If I understood your suggestion correctly, it also adds an empty object when the upsert is executed the first time: [{}, {12345, 2}, {23456, 3}]

final var setHistory = Document.parse(String.format("{" 
    "$set: {" 
        "history: {" 
            "$cond: [" 
                // <Array condition goes here>
                "{" 
                    "$eq: [\"$timestamp\", %d]"  // with eq => insert also adds an empty object
                    //"$ne: [\"$timestamp\", %d]"  // Error: "input to $map must be an array not string"
                "}," 
                //True part (timestamp not changed: keep history)
                "{" 
                    "$map: {" 
                        "input: \"history\"," 
                        "in: {" 
                            "$mergeObjects: [" 
                                "\"$$this\"," 
                                "{" 
                                    "timestamp: \"$timestamp\","  
                                    "mean_ninety_percentile: \"$mean_ninety_percentile\"" 
                                "}" 
                            "]" 
                        "}" 
                    "}" 
                "}," 
                //False part (timestamp changed: add state to history)
                "{" 
                    "$concatArrays: ["  
                        "{ $ifNull: [\"$history\", []] },"  
                        "["  
                            "{" 
                                "timestamp: \"$timestamp\","  
                                "mean_ninety_percentile: \"$mean_ninety_percentile\"" 
                            "}" 
                        "]"  
                    "]" 
                "}" 
            "]" 
        "}" 
    "}" 
"}", time));

For clarification, the goal is the following state after the 2nd upsert (i.e. 1 insert and 1 update):

collection = [
  {
     timestamp: 23456,
     data_point_count: 2,
     history: [
        {timestamp: 12345, data_point_count: 1}
     ]
  },...
]

CodePudding user response：

You can achieve the same using aggregate update. But it is supported from mongo 4.2 .

db.collection.update({},
[{
$set: {
  timestamp: 12345,
  mean_ninety_percentile: 0.111,
  history: {
    $cond: [
      // <Array condition goes here>
      {
        $eq: [
          "$timestamp",
          12345
        ]
      },
      // True part (timestamp not changed: keep history)
      "$history",
      // False part (timestamp changed: add state to history)
      {
        $concatArrays: [
          {
            $ifNull: [
              "$history",
              []
            ]
          },
          [
            // add current state to history
            {
              timestamp: "$timestamp",
              mean_ninety_percentile: "$mean_ninety_percentile"
            }
          ]
      }
    ]
  }
}
}],
{
  upsert: true
})