Update nested json array fileds

Couchbase Server
1

Hi,

Please share your thought on how to update on below field

“UserList”: [
{
“userAge”: 33,
“empID”: 32,
“empSal”: 120000,
“userDept”: {
“deptId”: 1,
“deptEmpCount”: 10,
“deptNo”: 2
}
},{
“userAge”: 33,
“empID”: 31,
“empSal”: 120000,
“userDept”: {
“deptId”: 1,
“deptEmpCount”: 10,
“deptNo”: 2
}
}
]

want to update deptEmpCount and deptId based on empID.,
Same want to remove based on empID

2 
UPDATE default AS d
SET u.deptEmpCount = 12 FOR u IN d.UserList WHEN u.empID = 31 END,      
          u.deptId  = 2 FOR u IN d.UserList WHEN u.empID = 31 END
WHERE ...;

UPDATE default AS d
SET d.UserList = ARRAY  u I FOR u IN d.UserList WHEN u.empID = 31 END
WHERE ...;

See update-for syntax and last example https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html

3

“userDept”: {
“deptId”: 1,
“deptEmpCount”: 10,
“deptNo”: 2
}

deptEmpCount & deptId are inside userDept object?how to update deptEmpCount & deptId based on empID

want to add new User object to UserList in the existing doc:
{
“userAge”: 34,
“empID”: 33,
“empSal”: 120000,
“userDept”: {
“deptId”: 1,
“deptEmpCount”: 10,
“deptNo”: 3
}
}

4

Add object to existing user

UPDATE default AS d
SET u.userDept =  {"deptId":1,"deptEmpCount: 10, deptNo:3} FOR u IN d.UserList WHEN u.empID = 33 END
WHERE ...;

To Add new object to UserList

UPDATE default AS d
SET d.UserList = ARRAY_APPEND(d.UserList, { "userAge": 34, "empID": 33, "empSal": 120000, "userDept": { "deptId": 1, "deptEmpCount": 10, "deptNo": 3 } })
WHERE ..

To Add new object to UserList if not there, if there update it.

UPDATE default d
SET d.UserList = (CASE WHEN (FIRST u FOR u IN d.UserList WHEN u.empID = 33 END) IS MISSING
                       THEN ARRAY_APPEND(d.UserList,
                           { "userAge": 34, "empID": 33, "empSal": 120000, "userDept": { "deptId": 1, "deptEmpCount": 10, "deptNo": 3 } })
                       ELSE d.UserList END
                  END),
     u.userDept.deptId = 2 FOR u IN d.UserList WHEN u.empID = 33 END
WHERE ....
5

Thanks, Is it possible to have batch update using couch sdk like in jdbc?

6

Update on WHERE condition updates all qualified documents.

7

suppose need to update empID,empSal like
batch update query
UPDATE default SET g.userAge = 332,g.empSal=12 FOR g IN UserList WHEN g.empID = 32 END
UPDATE default SET g.userAge = 332,g.empSal=12 FOR g IN UserList WHEN g.empID = 31 END
batch delete query
UPDATE default SET groupList = ARRAY g FOR g IN groupList WHEN v.groupID != 32 END
UPDATE default SET groupList = ARRAY g FOR g IN groupList WHEN v.groupID != 31 END

how to execute these queries in batch in one request.

8 
 UPDATE pocdata SET UserList[pos] = OBJECT_CONCAT(u, {"userAge":332, "empSal":12})  FOR pos:u IN UserList WHEN u.empID IN [31,32] END,
               groupList = ARRAY g FOR g IN groupList WHEN g.groupID NOT IN [31, 32] END;

OR

 UPDATE pocdata SET UserList[pos] = OBJECT_PUT(OBJECT_PUT(u, "userAge", 332), "empSal", 12)  FOR pos:u IN UserList WHEN u.empID IN [31,32] END,
               groupList = ARRAY g FOR g IN groupList WHEN g.groupID NOT IN [31, 32] END;

OR

UPDATE pocdata SET  u.userAge = 332  FOR u IN UserList WHEN u.empID IN [31,32] END,
             u.empSal = 12  FOR u IN UserList WHEN u.empID IN [31,32] END,
             groupList = ARRAY g FOR g IN groupList WHEN g.groupID NOT IN [31, 32] END;
9

UPDATE default SET g.userAge = 331,g.empSal=11 FOR g IN UserList WHEN g.empID = 32 END
UPDATE default SET g.userAge = 332,g.empSal=12 FOR g IN UserList WHEN g.empID = 31 END

batch update where userAge and empSal value will be different for different empID .

10

Then use two set commands in same update

 UPDATE pocdata SET UserList[pos] = OBJECT_PUT(OBJECT_PUT(u, "userAge", 331), "empSal", 11)  FOR pos:u IN UserList WHEN u.empID = 32 END,
                UserList[pos] = OBJECT_PUT(OBJECT_PUT(u, "userAge", 332), "empSal", 12)  FOR pos:u IN UserList WHEN u.empID = 31 END,
               groupList = ARRAY g FOR g IN groupList WHEN g.groupID NOT IN [31, 32] END;

If so complex, you can get document in application and modify and write back using SDKs

Each SQL statement is independent there are no batches. Only SELECT can have subqueries.

11

Thanks But with ref to below structure

“UserList”: [
{
“userAge”: 33,
“empID”: 32,
“empSal”: 120000,
“userDept”: {
“deptId”: 1,
“deptEmpCount”: 10,
“deptNo”: 2
}
},{
“userAge”: 33,
“empID”: 31,
“empSal”: 120000,
“userDept”: {
“deptId”: 1,
“deptEmpCount”: 10,
“deptNo”: 2
}
}
]

suppose we want to update userAge,empSal and deptId,deptNo in userDept object and all in UserList array
for 31 and 32

12

You can cascade more of the object_put

UPDATE pocdata 
SET UserList[pos] = OBJECT_PUT(OBJECT_PUT(OBJECT_PUT(u, "userAge", 331), "empSal", 11),"userDept", OBJECT_PUT(OBJECT_PUT(u.userDept, "deptNo", 3), "deptId",5))  FOR pos:u IN UserList WHEN u.empID = 32 END;

OR

You can reconstruct object and assign like below

UPDATE pocdata 
SET UserList[pos] =  { "userAge": u.userAge+1, "empSal": u.empSal+1000, u.empID, "userDept": {u.userDept.deptEmpCount, "deptId": 5, "deptNo" :3 }  FOR pos:u IN UserList WHEN u.empID = 32 END;