Update query in N1QL for array

SQL++
,
1

“id”: “123”
“recipients”: {
“emailRecipients”: [
{
“contactStub”: “59b80454-8d84-44e9-b520-808340630b99”,
“firstName”: “b”,
“lastName”: “1”,
“additionalRecipients”: [],
“emailAddress”: "abc@gmail.com",
}
]
}

I need to update firstName, lastName, emailAddress-

I have the following query which works.

UPDATE bucket-name set er.firstName="changedValue1" FOR er IN recipients.emailRecipients WHEN er.emailAddress = "abc@gmail.com"END, er.lastName= "changedValue2" FOR er IN recipients.emailRecipients WHEN er.emailAddress = "abc@gmail.com"END, er.emailAddress = "changedValue3" FOR er IN recipients.emailRecipients WHEN er.emailAddress = "abc@gmail.com"END WHERE id = 123

Isn’t there any other better way to do this?

2 
UPDATE default SET recipients. emailRecipients = ARRAY   CASE WHEN er.emailAddress = "abc@gmail.com" TEHN  {lastName:"changedValue2", firstName:"changedValue"} ELSE er END FOR er IN recipients. emailRecipients END
WHERE .....

In Array construct when matched in CASE you need to provide new object and it replaces old one

OR

Use OBJECT_CONCAT() If the field is already present it replaces or adds

UPDATE default SET recipients. emailRecipients = ARRAY   CASE WHEN er.emailAddress = "abc@gmail.com" TEHN   OBJECT_CONCAT(er,{ {lastName:"changedValue2", firstName:"changedValue"})  ELSE er END FOR er IN recipients. emailRecipients END
    WHERE .....
3

The query (one without OBJECT_CONCAT) gives error - "syntax error - at end of input"

4

Post the exact query.

5

UPDATE bucket-name SET recipients. emailRecipients = ARRAY
CASE WHEN (er.emailAddress = “abc@g.mail” and er.contactStub = “af3c01f-008c-4a7c-a091-4c3fb9a96555”)
THEN
{er.lastName:“1c”, er.firstName:“Sc”, er.emailAddress:“cdf@j.mail”}
ELSE
er END
FOR er IN recipients.emailRecipients END
WHERE id = 4634

6

@vsr1 It worked, but it’s replacing the existing object which should not be the case.

7

The query you posted you are replacing.

UPDATE `bucket-name`
SET recipients.emailRecipients = ARRAY CASE WHEN (er.emailAddress = "abc@g.mail" AND er.contactStub = "af3c01f-008c-4a7c-a091-4c3fb9a96555")
                                            THEN OBJECT_CONCAT(er, {"lastName":"1c", "firstName":"Sc", "emailAddress":"cdf@j.mail"})
                                            ELSE er
                                       END
                                 FOR er IN recipients.emailRecipients END
WHERE id = 4634 AND
      ANY v IN recipients.emailRecipients SATISFIES v.emailAddress = "abc@g.mail" AND v.contactStub = "af3c01f-008c-4a7c-a091-4c3fb9a96555" END;

NOTE :Consider adding additional predicate. Assume id = 4634 document array does not have entry that you want to update still it goes and update same document (no change). To avoid such updates you need additional predicate

8

2 questions here-

  1. What’s the purpose of adding - ANY v IN recipients.emailRecipients SATISFIES v.emailAddress = "abc@g.mail" AND v.contactStub = "af3c01f-008c-4a7c-a091-4c3fb9a96555" END; in end of query
  2. What if my document doesn’t have firstName property it still goes and add that property with the new value.
9
  1. I have already explained in my previous post. If the element is not there in array it still updated document with same document you don’t want have unnecessary mutation.
  2. Yes. If you don’t want that you need to supply whole object to replace
10

okay… Thanks for the help. Let me run my query and see how it works.
thanks again.

11

To better understand item 1 Try this

INSERT INTO default VALUES ("123",{ "id": "123", "recipients": { "emailRecipients": [ { "contactStub": "59b80454-8d84-44e9-b520-808340630b99", "firstName": "b", "lastName": "1", "additionalRecipients": [], "emailAddress": "abc@gmail.com" }, { "contactStub": "59b80454-8d84-44e9-b520-808340630b99", "firstName": "b", "lastName": "1", "additionalRecipients": [], "emailAddress": "xyz@gmail.com" } ]
}});

UPDATE default
SET recipients.emailRecipients = ARRAY CASE WHEN (er.emailAddress = "abc@gmail.com" AND er.contactStub = "59b80454-8d84-44e9-b520-808340630b99" )
                                            THEN OBJECT_CONCAT(er, {"lastName":"1c", "firstName":"Sc", "emailAddress":"cdf@j.mail"})
                                            ELSE er
                                       END
                                 FOR er IN recipients.emailRecipients END
WHERE id = "123" AND
      ANY v IN recipients.emailRecipients SATISFIES v.emailAddress = "abc@gmail.com" AND v.contactStub = "59b80454-8d84-44e9-b520-808340630b99" END;

For First Update you see mutationCount: 1 and documented updated.
repeat Update mutationCount:0 i.e. query never done any update because nothing qaulified

Now remove ANY clause and run update. Every time you see mutationCount:1 i.e query updated document every time with same value.

Simple example:
UPDATE default SET c1 = 5 WHERE id = 15;
Assume you have 1000 rows for id = 15, out of that c1 = 5 you have 100 rows above query updates 1000 rwos

The following is much optimized. It only updates 900

UPDATE default SET c1 = 5 WHERE id = 15 AND c1 != 5;

12

If the document doesn’t have middleName property and don’t want to add that you can use the following query

UPDATE default
SET recipients.emailRecipients = ARRAY CASE WHEN (er.emailAddress = "abc@gmail.com" AND er.contactStub = "59b80454-8d84-44e9-b520-808340630b99" )
                                            THEN (OBJECT v.name: IFMISSING( (FIRST nv.val FOR nv IN OBJECT_PAIRS({"lastName":"1c", "firstName":"Sc", "emailAddress":"cdf@j.mail", "middleName":"N"}) WHEN nv.name = v.name END), v.val) FOR v IN OBJECT_PAIRS(er) END)
                                            ELSE er
                                       END
                                 FOR er IN recipients.emailRecipients END
WHERE id = "123" AND
      ANY v IN recipients.emailRecipients SATISFIES v.emailAddress = "abc@gmail.com" AND v.contactStub = "59b80454-8d84-44e9-b520-808340630b99" END;