Conditional Set based on value present

SQL++
1

I am wondering if i can in N1QL only update a value of an Doc in Array if it is Not Defined or Null in a single stament.
In my case i keep track of email Activity and i want to log when the first time the email was opened. Currently i am using this query

UPDATE Contacts AS d USE KEYS $1
               SET e.opened = true, e.activityDate = $3 FOR e IN d.emails  WHEN e.tracking_nbr =  $2 END,
               d.metrics.nbr_of_email_opened  = d.metrics.nbr_of_email_opened +1 ,
               d.metrics.nbr_of_unique_email_opened =  d.metrics.nbr_of_unique_email_opened + 
 CASE WHEN  (ANY e IN d.emails SATISFIES e.tracking_nbr =  $2 AND (e.opened  IS NOT VALUED OR e.opened = false) END) THEN 1 ELSE 0 END`
      WHERE  d._type = "email_campaign" AND ANY e IN d.emails SATISFIES e.tracking_nbr = $2 END

That code will overwrite the activityDate everytime it is called which i want to avoid.

2

SET clause always updates once statement WHERE clause is true. Only way to preserve is through CASE on right side of SET by setting same or new value. (ex SET d.f1 = CASE WHEN d.flag == true THEN “10” ELSE d.f1 END)

If SET clause updating array element (though FOR syntax) there is flexibility. You can add any condition to WHEN clause. Only that condition true then only that part of element is modified other wise it ignored.

Modify like this

e.activityDate = $3 
 FOR e IN d.emails  WHEN e.tracking_nbr =  $2  AND (e.opened  IS NOT VALUED OR e.opened = false) END

NOTE: Once document is qualified through WHERE clause mutations happens irrespective of change happens SET?UNSET clauses.

3

Ok when i try this below, it no longer sets the opened to thru which in turn keeps updating activityDate

UPDATE Contacts AS d USE KEYS $1

               SET e.opened = true, e.activityDate = $3 FOR e IN d.emails  WHEN e.tracking_nbr =  $2 AND (e.opened  IS NOT VALUED OR e.opened = false) END,

               d.metrics.nbr_of_email_opened  = d.metrics.nbr_of_email_opened +1 ,

               d.metrics.nbr_of_unique_email_opened =  d.metrics.nbr_of_unique_email_opened + 

              CASE WHEN  (ANY e IN d.emails SATISFIES e.tracking_nbr =  $2 AND (e.opened  IS NOT VALUED OR e.opened = false) END) THEN 1 ELSE 0 END

              WHERE  d._type = "email_campaign" AND ANY e IN d.emails SATISFIES e.tracking_nbr = $2 END
4

SET/UNSET each modification needs its own FOR clause
SET e.opened = true, … This mean document (d.e.opened) has object e and opened filed. If there is no object e it ignores.

What you need is:

UPDATE Contacts AS d USE KEYS $1
SET e.opened = true FOR e IN d.emails  WHEN e.tracking_nbr =  $2  AND (e.opened  IS NOT VALUED OR e.opened = false) END,
    e.activityDate = $3 FOR e IN d.emails  WHEN e.tracking_nbr =  $2 AND (e.opened  IS NOT VALUED OR e.opened = false) END,
    d.metrics.nbr_of_email_opened  = d.metrics.nbr_of_email_opened +1 ,
    d.metrics.nbr_of_unique_email_opened =  d.metrics.nbr_of_unique_email_opened +
        CASE WHEN (ANY e IN d.emails SATISFIES e.tracking_nbr = $2 AND (e.opened  IS NOT VALUED OR e.opened = false) END)
            THEN 1 ELSE 0
        END
WHERE  d._type = "email_campaign" AND ANY e IN d.emails SATISFIES e.tracking_nbr = $2 END;

OR

UPDATE Contacts AS d USE KEYS $1
SET d.emails[pos] = OBJECT_CONCAT(e, {"opened":true, "activityDate":$3})
              FOR pos:e IN d.emails WHEN e.tracking_nbr =  $2 AND (e.opened  IS NOT VALUED OR e.opened = false) END,
    d.metrics.nbr_of_email_opened  = d.metrics.nbr_of_email_opened +1 ,
    d.metrics.nbr_of_unique_email_opened =  d.metrics.nbr_of_unique_email_opened +
        CASE WHEN (ANY e IN d.emails SATISFIES e.tracking_nbr = $2 AND (e.opened  IS NOT VALUED OR e.opened = false) END)
            THEN 1 ELSE 0
        END
WHERE  d._type = "email_campaign" AND ANY e IN d.emails SATISFIES e.tracking_nbr = $2 END;