ARRAY_REPLACE if value exists, otherwise ARRAY_PUT

SQL++
1

Hello.
I am writing a query for updating a complex value within an array. What I need is this:

  1. If a value does not exist, I put it into the array;
  2. If it does exist, I update the value within the array;

My query so far:

UPDATE test AS t
SET t.array = CASE
	WHEN (ANY node IN t.array SATISFIES node.id = 9999 END)
	  THEN ARRAY_REPLACE(t.array, node, { 'id': 1000, 'value': 9999 })
	ELSE 
	  ARRAY_PUT(t.array, { 'id': 1000, 'value': 1111})
	END
WHERE t.type = 'test'

It doesn’t work when the WHEN clause is true, because node doesn’t get populated into THEN section. That results into flushing my array entirely. Is there a way to do it correctly?

Couchbase: Enterprise Edition 6.6.0

2 
UPDATE test AS t
SET t.array =  CASE WHEN (FIRST v IN t.array WHEN v.id = 9999 END IS MISSING)
	  THEN  ARRAY_PUT(t.array, { 'id': 1000, 'value': 1111}) 
          ELSE  ARRAY CASE WHEN v.id == 9999 TEHN  { 'id': 1000, 'value': 9999 } ELSE v END FOR v IN t.array END
	END
WHERE t.type = 'test'
3

Thank you!
Tried it and got:
syntax error - at WHEN.
Here’s what I have that is working so far:

UPDATE test AS t
SET
  t.array = CASE
  WHEN (EVERY tt IN t.array SATISFIES tt .id !=  9999 END)
    THEN ARRAY_PUT( t.array, { 'id': 9999, 'value': 1111 })
    ELSE t.array
  END,
  t.value= 2222 FOR t IN t.array WHEN t.id = 9999 END
WHERE org.type = 'test'

Also, I am having another problem with the same query. I did not specify it at the beginning, but my doc structure is this:

{
  "id": 1,
  "sub": [
	  {
		"id": 2,
		"subSub": [
		  {
			"id": 3,
			"array": []
		  },
		],
		"array": []
	  },
  ],
  "array": []
}

I would like to apply the same logic for all the two subdocuments. With the same inserted/updated values. I thought it should be straightforward after figuring out the top one, but it appears that for all the subdocs the query will be different.
This doesn’t work:

UPDATE test AS t
SET
  t.array = CASE
  WHEN (EVERY tt IN t.array SATISFIES tt .id !=  9999 END)
    THEN ARRAY_PUT( t.array, { 'id': 9999, 'value': 1111 })
    ELSE t.array
  END,
  t.value= 2222 FOR t IN t.array WHEN t.id = 9999 END
  t.sub.array = CASE
  WHEN (EVERY tsub IN t.sub.array SATISFIES tsub.id !=  9999 END)
    THEN ARRAY_PUT( tsub.array, { 'id': 9999, 'value': 1111 })
    ELSE tsub.array
  END,
  tsub.value= 2222 FOR tsub IN t.sub.array WHEN tsub.id = 9999 END
WHERE t.type = 'test'
4

Here’s what I got:

UPDATE test AS t
SET
  -- top
  t.array =  CASE
  WHEN (EVERY ta IN t.array SATISFIES ta.id!= 9999 END)
    THEN ARRAY_PUT( t.array, { 'id': 1111, 'value': 9999 })
    ELSE t.array
  END,
  ta.value= 77777 FOR ta IN  t.array WHEN ta.id = 9999 END
  -- child
  child.array =  CASE
  WHEN (EVERY ca IN child.array SATISFIES ca.id != 9999 END)
    THEN ARRAY_PUT(child.array,{ 'id': 1111, 'value': 9999 })
    ELSE child.array
  END
  FOR child IN t.children END,
  aa.value = 77777 FOR ca IN ARRAY_FLATTEN(ARRAY child.array FOR child IN t.children END, 1) WHEN aa.id = 9999 END
  -- grand child
  grandchild.array = CASE
  WHEN (EVERY wa IN  grandchild.array SATISFIES wa.userId != 9999 END)
    THEN ARRAY_PUT( grandchild.array, { 'id': 1111, 'value': 9999 })
    ELSE  grandchild.array
  END
  FOR grandchild IN ARRAY_FLATTEN(ARRAY child.grandchildren FOR child IN t.children END, 1) END,
  SET ga.value = 666 FOR ga IN ARRAY_FLATTEN(ARRAY grandchild.array FOR grandchild IN ARRAY_FLATTEN(ARRAY child.grandchildren FOR child IN t.children END, 1) END, 1) WHEN ga.id = 9999 END 
WHERE t.type = 'test'

But I feel like it’s a bit sloppy. Is there a better way to achieve what I want?

5 
UPSERT INTO default VALUES("f01", { "id": 1, "sub": [ { "id": 2, "subSub": [ { "id": 3, "array": [{ 'id': 9998, 'value': 1111 }] }], "array": [{ 'id': 9999, 'value': 1111 }] } ], "array": [{ 'id': 9996, 'value': 1111 }, { 'id': 9997, 'value': 1111 }] } );

UPDATE default AS t
SET
    t.`array` = CASE WHEN 9999 NOT IN t.`array`[*].id
                   THEN ARRAY_PUT( t.`array`, { 'id': 9999, 'value': 1111 })
                   ELSE t.`array`
               END,
    tae.`value` = 222 FOR tae IN t.`array` WHEN tae.id == 9999 END,
    ta.`array` = ARRAY_PUT( ta.`array`, { 'id': 9999, 'value': 1111 })
               FOR ta IN t.sub WHEN 9999 NOT IN ta.`array`[*].id END,
    tae.`value` = 222 FOR tae IN ta.`array` FOR ta IN t.sub WHEN tae.id == 9999 END,
    ta.`array` = ARRAY_PUT( ta.`array`, { 'id': 9999, 'value': 1111 })
               FOR ta IN ts.subSub FOR ts IN t.sub WHEN 9999 NOT IN ta.`array`[*].id END,
    tae.`value` = 222 FOR tae IN ta.`array` FOR ta IN ts.subSub FOR ts IN t.sub WHEN tae.id == 9999 END;
6

Perfect! This works like a charm. Thank you.

P.S.
You forgot to add
WHERE t.type = 'test' to the end of the query.