RETURNING clause after insert/update query

SQL++
1

Not able to write proper query for returning clause for child level elements for this document

I am using this query for a inserting user
UPDATE dov
SET UserGroup.user = ARRAY_PUT(UserGroup.users, {‘id’ : ‘user3’,‘to’:‘2001-12-12’,‘from’:‘2002-12-12’})
FOR UserGroup in pGroup.UserGroups
FOR pGroup in pGroups
WHEN pGroup.name == ‘pname1’ AND UserGroup.name == "name1"
END
WHERE name == ‘app1’

trying to use returning clause after this query but not able to use it.

Can anyone please help on this…?? I will be grateful for the help…

{
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 
INSERT INTO default VALUES("k01", { "name":'app1', "pGroups":[{ "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 UserGroup.`user` = ARRAY_APPEND(UserGroup.`user`, {'id' : 'user3','to':'2001-12-12','from':'2002-12-12'})
      FOR UserGroup IN pGroup.UserGroups FOR pGroup IN pGroups WHEN pGroup.name == 'pname1' AND UserGroup.name == "name1" END
WHERE name == 'app1' RETURNING pGroups;

If you need to return child level element you need to use array-expression or first -expression or slice expression as described https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/collectionops.html
example : RETURNING pGroups[0].UserGroups[0].`user`

1 Like
3

I have 100 of record (JSON Chunk), can we get Index of the json while Updating or Inserting?, because If I use 0th element it will return the 0 element of the user, may be I am updating the 10th element.

Thanks
Amit

4

The expression after RETURNING is uses same principal as projection of SELECT statement. The expression is evaluated based on UPDATED/INSERTED document.
We have function ARRAY_POS() which gives array position.

5

Thanks for you answer.
We are using Node.js and using queries to update documents in the couchbase.
We are using Returning statement because we found out that when we were inserting/updating a document, in callback of the bucket query we wanted to get the updates we did, we were using another query but incase of new insert of document atleast we saw nothing getting returned from the second select, thats why we are trying to use return, which as per your answer would be more difficult to use.

So, if you can explain, why we were not getting data on select when we were inserting the document or should we not do it at all that would help us.

6

If you update index key or INSERT there will be lag to index to catchup to the updates. You can use request_plus to force index catchup.

INSERT/UPDATE RETURNING *; gives whole document. If there is no error the document should have been updated/inserted.

It is not difficult but you need to construct expression. What ever you did second select projection do same thing in returning clause.

Also, You can do RETURNING META().id , Use that id in callback SELECT as USE KEYS[…]. This eliminate IndexScan (no index catchup required).

Examples of INSERT with RETURNING https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/insert.html

7

So, if that’s the case in case of subdocument updates and considering our secondary indexes are not based on the subdocuments we are trying to update, we can safely use the select in callback without having to worry about the catchups or catchup would be applicable for the subdocument updates too?

8

If callback is for each update instead of complete select, you can consider select with USE KEYS.
For INSERT indexes depends on INSERT values.

9

Thanks for quick response first of all.

Yes we can Use Keys but in that case since we are into nested document we would get an object we would have to translate and interpret, it would be easier to just get the data by another query. But as i understood, if key is not updated i would get the updated document and the issue would be only with the key catchup not related to the document updates.

10

If you do separate query in call back.

  1. As part of update If the index keys updated index may not have caught up to date. In that case you need to use request_plus. This will increase response time of second select. Also you might see other updates (this may not be an issue).
  2. In case of INSERT you may not find the document because index may not have caught up to date. In that case you need to use request_plus. This will increase response time of second select.
  3. second select will be doing indexScan and may be fetch if you are interested only updated/inserted documents lot of unnecessary work.

Solution: INSERT/UPDATE RETURNING clause will be projection of call back SELECT and no callback
OR
INSERT/UPDATE RETURNING clause will return META().ID . USE those META().ID’s in SECOND SELECT. This will not do any IndexScan.