Updating array multiple times in one update

SQL++
1

I am trying to update an array multiple times in one update query, because there is a need to create an array element on the fly, depending on if it already exists.

The issue is, if I chain CASE statements in one query, only the last statement is applied. It is because the array entity I am trying to update is static. It is calculated once during the execution of the update query, and it does not update as SET statements get applied to it.

Here is an example:

UPDATE default SET 
my_array = CASE WHEN (EVERY item IN my_array SATISFIES item.id <> 123 END) THEN  ARRAY_PUT(my_Array,
 {"id": 123, "col2":"blah"}]]}) ELSE my_array END,
my_array = CASE WHEN (EVERY item IN my_array SATISFIES item.id <> 456  END) THEN  ARRAY_PUT(my_Array,{"id": 456, "col2":"blah2"}]]}) ELSE my_array END,
my_array = CASE WHEN (EVERY item IN my_array SATISFIES item.id <> 789 END) THEN  ARRAY_PUT(my_Array,{"id": 789, "col33":"blah33"}]]}) ELSE my_array END
WHERE type = 1 AND doc_id = 1

Assuming this type of behavior won’t change, do I have an alternative that does not require multiple update queries?

2

I am not sure if understood question correctly. Also note the fields comes from document not from SET (i,e. my_array in all the reference in SET comes from document not through previous SET).

Are you looking to add to the array if not exist multiple times with single update?

UPDATE default SET
my_Array = ARRAY_CONCAT(my_Array,
            CASE WHEN (EVERY item IN my_Array SATISFIES item.id <> 123 END) THEN [{"id": 123, "col2":"blah"}] ELSE [] END,
            CASE WHEN (EVERY item IN my_Array SATISFIES item.id <> 456 END) THEN [{"id": 456, "col2":"blah2"}] ELSE [] END,
            CASE WHEN (EVERY item IN my_Array SATISFIES item.id <> 789 END) THEN [{"id": 789, "col2":"blah33"}] ELSE [] END);

UPDATE default SET
my_Array = ARRAY_CONCAT(my_Array,
            CASE WHEN (123 NOT IN my_Array[*].id) THEN [{"id": 123, "col2":"blah"}] ELSE [] END,
            CASE WHEN (456 NOT IN my_Array[*].id) THEN [{"id": 456, "col2":"blah2"}] ELSE [] END,
            CASE WHEN (789 NOT IN my_Array[*].id) THEN [{"id": 789, "col2":"blah33"}] ELSE [] END);

    UPDATE default d SET my_Array =
           ARRAY_CONCAT(my_Array, ARRAY CASE WHEN v.id NOT IN my_Array[*].id THEN v ELSE MISSING END
                                   FOR v IN [{"id": 123, "col2":"blah"}, {"id": 456, "col2":"blah2"}, {"id": 789, "col33":"blah33"}] END)
    WHERE ANY v IN [{"id": 123, "col2":"blah"}, {"id": 456, "col2":"blah2"}, {"id": 789, "col33":"blah33"}]
          SATISFIES v.id NOT IN my_Array[*].id END;
1 Like
3

What I meant was that any changes done to “my_array” do not reflect after each expression is executed. Only at the end.
Which is why I cannot create an item inside of an array in one expression, and then reference that new item in another expression, in the same update query.

That being said, ARRAY_CONCAT was the proper solution. Though “MISSING” in the ELSE statement erases my array completely. The proper value for the ELSE in this case is [].

Thanks!