How can i update a value in all docs in an array?

SQL++
1

I have an array called emails which i would like to update if possible without having to retrive the subdoc and then loop thru array. below is what my emails array looks like

"emails": [
      {
        "address": "work@gmail.com",
        "default": false,
        "id": "E78931A8-7FFF-4CC2-9137-958977D4E914",
        "type": "Work"
      },
      {
        "address": "personal@gmail.com",
        "default": true,
        "id": "F57B7F56-9421-4B29-BA88-44B2641CACE7",
        "type": "Personal"
      }
    ]

So what i am looking for is to set the default to false for all docs in emails array for a specific doc and using the USE KEYS “user::8D6D24A5-D669-45DC-99AC-F257BDA133A4”
This is not ideal but will ensure i have only one default, so i will go and check if the user selected the email to be default, if so i will set all to false and then on insert or update set the new item to true.

2

If you want update without retrieving, you must use SUBDOC API in the SDKs.
To update inside the array using SUBDOC API you must know full path include array pos. example: email[1].default
You can also use SUBDOC retrieve array portion of the document and modify and write using subdoc array portion.

You can also use N1QL (This full doc retrieve and full doc write) using UPDATE. Check out last example https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html

UPDATE default AS d  USE KEYS ["user::8D6D24A5-D669-45DC-99AC-F257BDA133A4"]
SET   e.`default` = false FOR e IN d.email END
WHERE ...;
3

how would the query llook if i also wanted to update the value of a single doc in the array . I tried 2 ways but neither did the job

 UPDATE Contacts AS d  USE KEYS ["user::8D6D24A5-D669-45DC-99AC-F257BDA133A4"]
 SET   e.dflt = false FOR e IN d.emails and e.id = 'F57B7F56-9421-4B29-BA88-44B2641CACE7' END

as well as this one

UPDATE Contacts AS d  USE KEYS ["user::8D6D24A5-D669-45DC-99AC-F257BDA133A4"]
SET   e.dflt = false FOR e IN d.emails END
where e.id = 'F57B7F56-9421-4B29-BA88-44B2641CACE7'

So what am i missing here to specify the doc in array to update ?

4 
UPDATE Contacts AS d  USE KEYS ["user::8D6D24A5-D669-45DC-99AC-F257BDA133A4"]
 SET   e.dflt = false FOR e IN d.emails  WHEN e.id = 'F57B7F56-9421-4B29-BA88-44B2641CACE7' END
WHERE ......;

update:

set-clause:

update-for:

unset-clause:

5

Thanks that works as always. To stay on that topic can N1QL handle following Tasks in this case

a) Delete a Doc in the Array based on the known id ?
b) Add a new Doc to the Array
c) get the Index of a Doc in Array based on the ID provided

Most of these i am curently doing via SubDoc and SDK but i like N1QL better as there is a better way to check success since on the SubDoc its all or nothing
Thanks

6

Remove element from ARRAY

UPDATE Contacts AS d  USE KEYS ["user::8D6D24A5-D669-45DC-99AC-F257BDA133A4"]
SET   d.emails  =   ARRAY e FOR e IN d.emails  WHEN e.id != 'F57B7F56-9421-4B29-BA88-44B2641CACE7' END;

Add new element to the ARRAY unconditionally

   UPDATE Contacts AS d  USE KEYS ["user::8D6D24A5-D669-45DC-99AC-F257BDA133A4"]
    SET   d.emails  =    ARRAY_APPEND(d.emails, {....});

Get First matching position of ARRAY

  SELECT FIRST pos FOR pos:e IN d.emails WHEN  e.id = 'F57B7F56-9421-4B29-BA88-44B2641CACE7' END AS pos 
  FROM default;
7

Ok the first 2 make sense so there is no delete, we go and replace array - the doc which ID we specified. Where i am a bit confused is the matching array position,

Wouldn’t the below one be more correct as we dont want to search the whole bucket ?

SELECT FIRST pos FOR pos:e IN d.emails WHEN  e.id = 'F57B7F56-9421-4B29-BA88-44B2641CACE7' END AS pos 
  FROM Contacts AS d  USE KEYS ["user::8D6D24A5-D669-45DC-99AC-F257BDA133A4"]

When i run this i get this back
success | elapsed: 6.91ms | execution: 6.70ms | count: 1 | size: 2
[
{}
]

8

This gives me results. If no match it returns MISSING

  INSERT INTO default VALUES ("k01", {"emails": [ { "address": "work@gmail.com", "default": false, "id": "E78931A8-7FFF-4CC2-9137-958977D4E914", "type": "Work" }, { "address": "personal@gmail.com", "default": true, "id": "F57B7F56-9421-4B29-BA88-44B2641CACE7", "type": "Personal" } ]});
    SELECT FIRST pos FOR pos:e IN d.emails WHEN  e.id = 'F57B7F56-9421-4B29-BA88-44B2641CACE7' END AS pos
      FROM default AS d USE KEYS "k01";