Remove all objects matching condition from an array

SQL++
1

I want to remove all objects with the same country from the array but my query removes only one each execution.

  "notifications": [{
  "text": "werwe",
  "seller": "2323",
  "country": "GB"
  }, {
  "text": "we232323e",
  "seller": "132333",
  "country": "GB"
  }, {
  "text": "xcvx cxx",
  "seller": "6344323",
  "country": "FR"
  }]

query:

UPDATE bucket USE KEYS "test_key"
SET notifications = ARRAY_REMOVE(notifications, i) FOR i IN notifications WHEN i.country = "GB" END;
2

SET notifications = ARRAY_REMOVE(notifications, i) FOR i IN notifications WHEN i.country = “GB” END;
ARRAY_REMOVE(notifications, i) – notifications is original element not intermediate results.

You should use array construct to build new array and assign it.

You want mutate documents when notifications has at least one country of “GB” (controlled by WHERE caluse) and remove them (controlled by SET clause).

UPDATE bucket USE KEYS "test_key" 
SET notifications =   ARRAY v  FOR v IN notifications WHEN v.country  != "GB" END
WHERE ANY v IN notifications SATISFIES v.country = "GB" END;
3

What if I want to have more conditions and I just want to delete those that match condition and leave the rest? if I add more conditions then it deletes everything

4

Add them to SATISFIES and WHEN clause.

5

Yes I added to SATISFIES AND WHEN ( conditionA AND conditionB AND conditionC ) but even if one of them is true then it deletes from array

6

Please note WHEN condition must be negative of WHERE . If still need help please post original query.

UPDATE bucket USE KEYS "test_key" 
SET notifications =   ARRAY v  FOR v IN notifications WHEN  NOT (v.country  = "GB"  AND v.seller = "132333")  END
WHERE ANY v IN notifications SATISFIES v.country = "GB" AND v.seller = "132333" END;

Try following select and adjust your conditions and play around before you do actual update.

SELECT  ARRAY v  FOR v IN notifications WHEN  NOT (v.country  = "GB"  AND v.seller = "132333")  END
FROM   bucket USE KEYS "test_key" 
 WHERE ANY v IN notifications SATISFIES v.country = "GB" AND v.seller = "132333" END;
7

can we objects from two array using the same condition like my query will be:
UPDATE bucket
SET notifications = ARRAY v FOR v IN notifications WHEN NOT (v.country = “GB” AND v.seller = “132333”) END,
state=Array s for s IN state when not(s.county=“GB” and s.seller=“132333”)END
WHERE ANY v IN notifications SATISFIES v.country = “GB” AND v.seller = “132333” END
and ANY s in state Satisifies s.county=“GB” and s.seller=“132333”
and type=" client_notification" and state_notification=“123”

I am not able to achieve desired output, remove objects from two array in document in same query

8

not sure what exactly your use case.
Do you want update either notifications or state (WHERE (anycond1 OR anycond2) AND …

If need help post input document, expected result as separate post

9

Want to update both notification and state in single query with same where and when conditions

10

typo country vs county

INSERT INTO default VALUES ("test_key", { "notifications": [{ "text": "werwe", "seller": "2323", "country": "GB" }, { "text": "we232323e", "seller": "132333", "country": "GB" }, { "text": "xcvx cxx", "seller": "6344323", "country": "FR" }], "state": [{ "text": "werwe", "seller": "2323", "country": "GB" }, { "text": "we232323e", "seller": "132333", "country": "GB" }, { "text": "xcvx cxx", "seller": "6344323", "country": "FR" }], "type":"client_notification", "state_notification":"123" });

UPDATE default AS d USE KEYS "test_key"
SET d.notifications = ARRAY v FOR v IN d.notifications WHEN NOT (v.country = "GB" AND v.seller = "132333") END,
    d.state = ARRAY s FOR s IN d.state WHEN NOT (s.country="GB" AND s.seller="132333")END
WHERE ANY v IN d.notifications SATISFIES v.country = "GB" AND v.seller = "132333" END
      AND ANY s in d.state SATISFIES s.country="GB" AND s.seller="132333" END
      AND d.type="client_notification" AND d.state_notification="123";

SELECT d.* FROM default AS d USE KEYS "test_key";
11

it is working for single documents but if more than one documents it is not working.
if i run to update only notification it update and remove all the records from notification