Cannot perform append and update on an array within the same n1ql request

I need to update some array elements each time a new one is pushed. I use the following query, which doesn’t work

UPDATE `users`
SET logs.sessions = ARRAY_APPEND(IFMISSINGORNULL(logs.sessions, []), {"session_id": "randomid", "fingerprint": "xxxx"}),
    s.closed = true FOR s IN logs.sessions WHEN s.fingerprint = "xxxx" AND s.closed = false END
WHERE credentials.email = "user@email.com"

This request doesn’t work, unless I split it in 2 independant requests:

UPDATE `users`
SET logs.sessions = ARRAY_APPEND(IFMISSINGORNULL(logs.sessions, []), {"session_id": "randomid", "fingerprint": "xxxx"})
WHERE credentials.email = "user@email.com"

UPDATE `users`
SET  s.closed = true FOR s IN logs.sessions WHEN s.fingerprint = "xxxx" AND s.closed = false END
WHERE credentials.email = "user@email.com"

The obvious problem of this fix is that it requires 2 separate n1ql calls, which are slowing down the API.

Can this be performed all in a single request ?

UPDATE `users`
SET logs.sessions = ARRAY CASE WHEN s.fingerprint = "xxxx" AND s.closed = false THEN OBJECT_PUT(s,"closed",true) ELSE s END
    s.closed = true FOR s IN logs.sessions WHEN s.fingerprint = "xxxx" AND s.closed = false END
WHERE credentials.email = "user@email.com";
1 Like

Thanks!

Got it to work with this:

UPDATE `users` 
SET logs.sessions = ARRAY_CONCAT(
    ARRAY CASE WHEN s.fingerprint = "xxxx" AND s.closed = false THEN OBJECT_PUT(s,"closed",true) ELSE END FOR s IN logs.sessions END,
    [{"session_id": "randomid", "fingerprint": "xxxx"}]
) WHERE personal_data.credentials.email = "user@domain.com"