Can't get $lt and $gt to work properly within $set

Working with Data
, ,
1

Hi,
I’m working on a project where I need to be able to conditionally add data if the start date is greater than the end dates saved in the database or if the end date is less than the start dates saved in the database.

example of data:

_id: ''
appointments: [
{
  _id: '',
  location: '',
  dateTimeStamp: [ 1689952800000, 1689954600000],
},
]

I’ve tried achieving this by:

 const appointmentsExist = await Appointments.findOneAndUpdate({_id:user._id}, 
  [
   {
        $set: {
          appointments:{
              $cond: {
                if: {
                  $or:[
                     { $lt: [
                         { $ifNull: [ "$appointments.dateTimeStamp.1", 0 ] }, args.input.appointments[0].dateTimeStamp[0]
                        ]
                     }, 
                      { $gt: [
                          { $ifNull: [ "$appointments.dateTimeStamp.0", 0  ] }, args.input.appointments[0].dateTimeStamp[1]
                       ]
                      },
                  ],
               },
              then: {$concatArrays:[{$ifNull: ["$appointments", []]}, args.input.appointments]},
              else: {}
              }
          }
      }
  }
  ],{upsert: true})

where args.input.appointments (example) is:

[{
   location: '',
  dateTimeStamp: [ 1689952900000, 1689954900000],
}]

The problem that I’m having is that the array of args.input.appointments is being pushed to the database whether $appointments.dateTimeStamp.1< args.input.appointments[0].dateTimeStamp[0] or $appointments.dateTimeStamp.1> args.input.appointments[0].dateTimeStamp[0], and the same is true for $appointments.dateTimeStamp.0" > args.input.appointments[0].dateTimeStamp[1] or $appointments.dateTimeStamp.0" < args.input.appointments[0].dateTimeStamp[1].

Does anyone know why this is happening or what I’m doing wrong? I’ve even tried testing a simpler version in mongoplayground where dateTimeStamp is only a number value instead of an array, and I still am getting similar issues. I would really appreciate any help. Thank you!

2

I had a play, getting the matching to work BUT with the below it’ll add it twice (if you run it twice), as the condition finds a document that has an array element that is not overlapping with the new entry. After we add it, we still get a match as there exists a non-overlapping appt (in addition to the overlapping).

You need to check that NONE of the events in the array overlap the new item as far I can tell from your post.

You could use an aggregate merge back, where you unwind the appointments, check each one, re-group and then check for any grouped items that have no matches (or have an empty appointment schedule).

Perhaps someone can respond with a more elegant solution?

let newAppt = [
    {
        _id:'Zoo',
        dateTimeStamp:[1,5]
    }
]
db.getCollection("Appointments").update(
{
    $or:[
        {'appointments.dateTimeStamp.0':{$gt:newAppt[0].dateTimeStamp[1]}},
        {'appointments.dateTimeStamp.1':{$lt:newAppt[0].dateTimeStamp[0]}},
        {'appointments.0':{$exists:false}},
    ]
},
[
    {
        $set:{
            appointments:{
                $concatArrays:[
                    {
                        $ifNull:[
                            '$appointments',
                            []
                        ]
                    },
                    newAppt
                ]
            }
        }
    }
]
)

This was my second try to work out which items could be updated:

let newStart = 11
let newEnd = 16

db.getCollection("Appointments").aggregate([
{
  $unwind:
    {
      path: '$appointments',
      preserveNullAndEmptyArrays: true
    }
},
{
    $addFields:{
        itemStart:{$arrayElemAt:['$appointments.dateTimeStamp', 0]},
        itemEnd:{$arrayElemAt:['$appointments.dateTimeStamp', 1]},
    }
},
{
    $addFields:{
        comboField:{
            $cond:{
                if:{
                    $or:[
                        {$eq:['$itemStart', null]},
                        {$gt:[newStart, {$ifNull:['$itemEnd', 9999999]}]},
                        {$lt:[newEnd, {$ifNull:['$itemStart', -9999999]}]},
                    ]
                },
                then:true,
                else:false
            }            
        }
    }
},
{
    $group:{
        _id:'$_id',
        checks:{$push:'$comboField'},
        appointments:{$push:'$appointments'}
    }
},
{
    $match:{
        'checks':{$ne:false}
    }
},
{
    $project:{
        'checks':0
    }
},
{
    $project:{
        'appointments':{
            $concatArrays:[
                '$appointments',
                [
                    {
                        location:'London',
                        dateTimeStamp: [ 11, 16]    
                    }            
                ]
            ]
        }
    }
},
{
    $merge:{
        into:'Appointments',
        on:'_id',
        whenMatched:'merge'
    }
}
])

I added in the $ifNulls to cope with the condition where the values were not set, I also pulled the array elements into new fields to make my life easier when debugging…

So the stages are:

Unwind
Check each current appointment to see if it’s not overlapping the new item to insert
Group the results back up to the ID
Filter out anything that has an overlap and so cannot insert the new item
Remove calculated fields
Create a new appointments array from the old one concat with the new data
Merge back into collection, specifying a merge of the objects

I did this with a collection of multiple documents but if you know the single document you could just filter that one.

I imagine you could also do this with a $reduce but this was the rabbit hole that I went down…

I’ll bet someone comes along with a one-line update now…