Update query to add new attribute and its value in existing document

Yes default is bucket, $inputcvs is query parameter.

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/update.html will explain various parts of the UPDATE statement with examples.
$inputcvs.[al.stud_id] ===> evaluates al.stud_id and takes value and convert into field name and reference that field from $inputcvs get value.

al.stud_id is "1002"
  i.e  $inputcvs.`1002`

I think I took a wrong example. Please consider the below example (taking only 2 records, but on actual, will have 3 million records to update). Considering the experience field as unique field. As suggested, I prepared the input file as:

{“14746”:“1010”,
“14248”:“1011”}

Document:

[
{
“gamesim-sample”: {
“experience”: 14746,
“expId”:“exp_14746”
“hitpoints”: 20210,
“jsonType”: “player”,
“level”: 146,
“loggedIn”: true,
“name”: “Aaron0”,
“uuid”: “3b49dd18-1d56-478e-8ab1-fb38e31ce7e2”
}
},
{
“gamesim-sample”: {
“experience”: 14248,
“expId”:“exp_14248”
“hitpoints”: 23832,
“jsonType”: “player”,
“level”: 141,
“loggedIn”: true,
“name”: “Aaron1”,
“uuid”: “78edf902-7dd2-49a4-99b4-1c94ee286a33”
}
}
]

Desired Output:

[
{
“gamesim-sample”: {
“experience”: 14746,
“gal_id”:1010
“expId”:“exp_1010”
“hitpoints”: 20210,
“jsonType”: “player”,
“level”: 146,
“loggedIn”: true,
“name”: “Aaron0”,
“uuid”: “3b49dd18-1d56-478e-8ab1-fb38e31ce7e2”
}
},
{
“gamesim-sample”: {
“experience”: 14248,
“gal_id”:1011
“expId”:“exp_1011”
“hitpoints”: 23832,
“jsonType”: “player”,
“level”: 141,
“loggedIn”: true,
“name”: “Aaron1”,
“uuid”: “78edf902-7dd2-49a4-99b4-1c94ee286a33”
}
}
]

Trial

update gamesim-sample as g
set a1.gal_Id=$inputcsv.[a1.experience] for a1 in ?Got Stuck? END

How do I pass this $inputcsv? Shall I location of the file?

There is no array in your document.

UPDATE `gamesim-sample` AS g
SET g.gal_id = $inputcsv.[g.experience];

It is called named parameters. Check based on SDK how to set named parameters

cbq shell https://dzone.com/articles/mastering-the-couchbase-n1ql-shell-scripting

Thanks for your response. This means that we would need to have some small external utility (Java or any other) which will pass this $inputcsv as a parameter.

You can pass it through cbq shell, web ui, REST API too . You need to pass in JSON not a file.
Tell us which one you are using and will give you example.

I am using web ui.

Also, the input json would have around 3 million rows (in production). But that we can always have a logic to put this in chunks.

In Query Work Bench Click on preferences (i.e. top right corener)
There named parameters fill the following and save. execute your query

$inputcsv  as name, value as  {"1001":"9001",
"1002":"9010",
"1003":"9081"}

I see the save button gets disabled as soon as enter the string type value

Not sure why. Try with cbq shell

cbq -e=http://172.23.107.18:8091 -u=<user> -p=<password>
\set -$inputcsv {"1001":"9001", "1002":"9010", "1003":"9081"} ;

UPDATE `gamesim-sample` AS g
SET g.gal_id = $inputcsv.[g.experience];

Thanks for your prompt response.

When I run the command in cbq shell, I don’t see any output displayed. Unfortunately, documents are not updated when queries. Do we need to execute something similar to commit or something?

cbq> \set -$inputcvs{“14746”:“1010”,“14248”:“1011”,“55”:“1012”};
e[31m ERROR 139 : Too few input arguments to command. e[0m

cbq> \set -$inputcvs{“14746”:“1010”,“14248”:“1011”,“55”:“1012”}; UPDATE gamesim-sample AS g SET g.gal_id = $inputcsv.[g.experience];
cbq>

You need space

cbq -e=http://172.23.107.18:8091 -u=<user> -p=<password>

At prompt execute  one command at time

 \set -$inputcsv   {"1001":"9001", "1002":"9010", "1003":"9081"} ;

UPDATE   `gamesim-sample` AS g SET g.gal_id = $inputcsv.[g.experience];

Ok. With this, now getting the error message as “msg”: “Error evaluating SET clause. - cause: No value for named parameter $inputcsv.” Console output below: -

cbq> \set -$inputcvs {“14746”:“1010”, “14248”:“1011”, “55”:“1012”} ;
cbq> UPDATE gamesim-sample AS g SET g.gal_id = $inputcsv.[g.experience];
{
“requestID”: “80ed94d7-a9b4-4e26-bef8-ea48a19fd058”,
“signature”: null,
“results”: [
],
“errors”: [
{
“code”: 5010,
“msg”: “Error evaluating SET clause. - cause: No value for named parameter $inputcsv.”
}
],
“status”: “errors”,
“metrics”: {
“elapsedTime”: “4.9884ms”,
“executionTime”: “4.9884ms”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}
cbq>

The error is same for the cbq> UPDATE gamesim-sample AS g SET g.gal_id = $inputcsv.[g.experience];

Not sure why the slant quote is not appearing in the reply

Please ignore, There was a small difference in SET and UPDATE named parameter ( $inputcvs and $inputcsv). Sorry my bad.

Well, now it returns success, but on querying, the value of gal_id comes as null.

g.experience is number and object fields named parameters are string so change to TOSTRING(g.experience)

UPDATE  `gamesim-sample`  AS g
 SET g.gal_id = $inputcvs.[TOSTRING(g.experience)];

If there is no input value and don’t want to set

UPDATE  `gamesim-sample`  AS g
 SET g.gal_id = IFNULL($inputcvs.[TOSTRING(g.experience)], MISSING);

Great!!!. Hats Off to your immense knowledge on Couchbase. Thanks a lot

Hi,

Alternatively, can we pass through REST API, using postman? If we can do using postman, we can do using Tibco BW too. If yes, please share an example.

Please let me know your thoughts.

Thank you,
Regards,
Jayant

Not sure what do u mean pass. I am not expert on those tools. cc @binh.le

There is many ways to do this. Even you can insert your input into bucket as temporary key.
USE MERGE option from temp key and actual documents

Best approach is described here Query to insert new document from an existing document through named parameter

Got you.

By pass through, I meant calling Couchbase REST API and pass the named parameter as API Input parameter.

Thanks

REST API you can do. My only concern is your use case of 3M+ documents, the parameter size can be 20M+ bytes. Mutating may take long time, may get errors, timeouts. Don’t want to try already mutated documents.
Keep in all these approach suggested in other post will be reasonable.