Need help on update child level elements

SQL++
,
1

Hi,
I am trying to individual update on fields ‘to’ and ‘from’

I am trying with this query

UPDATE doc
SET userss.to= null
FOR userss in UserGroup.user
FOR UserGroup in UserGroups
WHEN UserGroup.name == “name1” and userss.id="user1"
END,
userss.from= null
FOR userss in UserGroup.users
FOR UserGroup in UserGroups
WHEN UserGroup.name == “name1” and userss.id="devxyz1"
END

but it is taking more time and sometime facing issue on update too

Is there any other way to update ?
Can anyone help me on this??

Thank You
Amit

{
"name":"app1",
"pGroups": [
{
	"name": pname1,
	"cont": {
	"facility": "facility1"
	},
	"UserGroups": [
	{
		"description": "",
		"name": "name1",
		"user": [
		{
		"from": "2016-10-10",
		"id": "user1",
		"to": null
		},
		{
		"from": "2016-10-10",
		"id": "user2",
		"to": null
		}
		]
	}
	]
}
]
}
2

What type of problems you are facing. The sample document is not a proper json.
The following works fine.

insert into default values("k01", { "name": "pname1", "cont": { "facility": "facility1" }, "UserGroups": [ { "description": "aa", "name": "name1", "user": [ { "from": "2016-10-10", "id": "user1", "to": 34 }, { "from": "2016-10-10", "id": "user2", "to": 35 } ] }] });

UPDATE default SET userss.`to`    = null FOR userss IN UserGroup.`user` FOR UserGroup IN UserGroups WHEN UserGroup.name = "name1" AND  userss.id="user1" END,
                   userss.`from`  = null FOR userss IN UserGroup.`user` FOR UserGroup IN UserGroups WHEN UserGroup.name = "name1" AND  userss.id="user2" END;
3

Thanks for the reply.
Actually I donot want to use FOR…IN… WHEN as it is like a loop to update all sub-child groups at a time.

Is it possible without using FOR…IN…??

4

If you need to update array element with condition you need to use FOR …IN …WHEN. If You know exact position of array or whole array you can update without FOR.

5

Actually the concern is can we write the query like:

UPDATE default SET userss.to = null FOR userss , userss.from = null
FOR userss IN UserGroup.user FOR UserGroup IN UserGroups WHEN UserGroup.name = “name1” AND userss.id=“user2” END;

So that one we have smaller query, and it seems like we are going in the document multiple times if we are updating multiple properties in a subdcument.

6

It is not possible.

If UserGroup.user objects are unique you can reconstruct user object and replace it. like below.

UPDATE default 
SET UserGroup.`user`[ARRAY_POS(UserGroup.`user`,userss)] = {"to" : null, "from":null, "id":userss.id} 
FOR userss IN UserGroup.`user` FOR UserGroup IN UserGroups WHEN UserGroup.name = "name1" AND  userss.id="user1" END;

For not unique

UPDATE default 
SET UserGroup.`user` = ARRAY CASE WHEN userss.id="user1" THEN {"to" : null, "from":null, "id":userss.id} ELSE userss END FOR userss IN UserGroup.`user` END 
FOR UserGroup IN UserGroups WHEN UserGroup.name = "name1" END;
7

Thanks for the update, i think we are looking for reconstruct the object since there could be more properties to what we are updating, if we reconstruct we would loose that data.
I think in that case we are left with the multi update as we were doing earlier.