Update Array of object's when object.name is present, if not present add object to Array

SQL++
1

Hi,
Below is my Document with “allDocs” as Document key inside “info” bucket
{
“userList”: [
{
“icon”: “NA”,
“description”: “NA”,
“name”: “ABC”,
“status”: “0”
},
{
“icon”: “NA”,
“description”: “NA”,
“name”: “XYZ”,
“status”: “0”
}
]
}

I want to compare the name inside the Array Object
if not present then Add the Object in to Array
If Presnt then Update the Object.

I am able to just Update the array with object using below query

UPDATE info USE KEYS ‘allDocs’ SET userList= ARRAY_APPEND( userList, {
“icon”: “NA”,
“description”: “NA”,
“name”: “123”,
“status”: “0”
} ) ;

But, first i want to check if the any of the array object contains “name” : 123, if present then update the Object else add it as New Object in to Array.

I tried multiple queries one of which is
UPDATE info USE KEYS ‘allDocs’ SET userList= ARRAY_DISTINCT( ARRAY_APPEND( ARRAY CASE WHEN x.name= ‘123’ THEN {
“icon”: “NA”,
“description”: “NA”,
“name”: “123”,
“status”: “0”
} ELSE x END, {
“icon”: “NA”,
“description”: “NA”,
“name”: “123”,
“status”: “0”
} ) END);
it gave syntax Errors.
can you please help me in this.

2

Hello, Rakesh.

The ANY operator will let you check the array for objects matching the criteria you need:

https://developer.couchbase.com/documentation/server/5.0/n1ql/n1ql-language-reference/collectionops.html

3 
UPDATE info USE KEYS ["allDocs"]
 SET userList = ARRAY CASE WHEN v.name = "123" TEHN  {.....} WHEN  "123" NOT IN userList[*].name THEN {.....} ELSE v END FOR v IN userList END;
4

Thank you @johan_larson @vsr1

5

@vsr1
i have tried the below query as you mentioned.

UPDATE info USE KEYS ‘allDocs’
SET userList = ARRAY CASE WHEN v.name = “ABC” THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”}
WHEN v.name NOT IN userList [*].name THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”} ELSE v END FOR v IN userList END FROM default;

It was giving me syntax error at FROM
"msg": "syntax error - at FROM"

so i tried,

UPDATE info USE KEYS ‘allDocs’
SET userList = ARRAY CASE WHEN v.name = “ABC” THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”}
WHEN v.name NOT IN userList [*].name THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”} ELSE v END FOR vIN userList END;

This query works for only updating when the Object is already present.
Its not creating a new Object in Array if not present.

6

now it works, i have changed the query a bit to

UPDATE info USE KEYS 'allDocs’
SET userList = ARRAY CASE WHEN v.name = “ABC” THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”}
WHEN “ABC” NOT IN userList [*].name THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”} ELSE v END FOR vIN userList END;

is that correct way?

7

Yes. The new statement is right

8

Below new query which i have tried is also not working properly.

UPDATE info USE KEYS 'allDocs’
SET userList = ARRAY CASE WHEN v.name = “ABC” THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”}
WHEN “ABC” NOT IN userList [*].name THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”} ELSE v END FOR v IN userList END;

This actually, Even though the name is not present in the Array, it just replacing the old ArrayObject with the new one

Original Array

{
  "userList ": [
  {
    “description”: “NA”,
    “name”: “123”,
    “icon”: “NA”,
    “status”: “0”
   }]
}

After running the above query

   {
      "userList ": [
      {
        “description”: “NA”,
        “name”: “pay”,
        “icon”: “NA”,
        “status”: “0”
       }]
    }

so i have tried different one

UPDATE info USE KEYS 'allDocs’
SET userList = ARRAY CASE WHEN v.name = “ABC” THEN {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”}
WHEN “ABC” NOT IN userList [*].name THEN ARRAY_APPEND(userList, {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”}) ELSE v END FOR v IN userList END;

This is creating another Array inside the Array

My original Array
{
"userList ": [{
“description”: “NA”,
“name”: “123”,
“icon”: “NA”,
“status”: “0”
}]
}

When i run the above query ARRAY_APPEND query
it updated the array to

{
  "userList ": [
    [
     {
       "description": "NA",
       "name": "123",
       "icon": "NA",
       "status": "0"
     },
     {
       "description": "NA",
       "name": "pay",
       "icon": "NA",
       "status": "0"
     }
    ]
  ]
}

I think its because of “FOR v IN userList”, if i already have two Objects in the Array, it is creating three Array’s and adding new Object in to all three Arrays.

The update of Array Object is working Ok, but adding a new Object in to Array is giving me problems

9 
UPDATE info USE KEYS ["allDocs"]
 SET userList = CASE
                   WHEN "ABC" IN userList [*].name
                   THEN (ARRAY CASE WHEN v.name = "ABC"
                               TEHN {"icon": "NA", "description": "NA", "name": "Pay", "status": "0"}
                               ELSE v
                               END
                        FOR v IN userList END)
                   ELSE ARRAY_APPEND(userList,{"icon": "NA", "description": "NA2", "name": "Pay", "status": "0"})
                   END;
10

Hello, I ‘m facing issue in update query where you have mentioned {“icon”: “NA”, “description”: “NA”, “name”: “Pay”, “status”: “0”} - I want to know what this format is?? i tried few of i knew it gives me something like -/{/“icon/”:confused: "NA’", /“description/”: /“NA/”,/ “name/”:confused: “Pay/”,/ “status/”:confused: “0/”} -as you can see i’m getting / this which i don’t want.