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

Hi,

I am new to Couchbase. We have a requirement to update documents with a new field. The value of that new field needs to be populated from a reference CSV file. Please help me with the right query / solution to achieve the same. Given the example below

Existing individual documents in same bucket

{
“A_list”: [
{
“doc_type”: “Books”,
“stud_id”: “1001”,
“spm_id”: “profile_1001”,
“sms”: “I”,
“email”: “O”,
},
{
“doc_type”: “Books”,
“stud_id”: “1002”,
“spm_id”: “profile_1002”,
“sms”: “O”,
“email”: “O”,
},
{
“doc_type”: “CDs”,
“stud_id”: “1003”,
“spm_id”: “obj_1003”,
“sms”: “O”,
“email”: “I”,
}
]
}

Input CSV

stud_id,xyz_id
1001,9001
1002,9010
1003,9081

Desired Output

{
“A_list”: [
{
“doc_type”: “Books”,
“stud_id”: “1001”,
“xyz_id”:“9001”,
“spm_id”: “profile_1001”,
“sms”: “I”,
“email”: “O”,
},
{
“doc_type”: “Books”,
“stud_id”: “1002”,
“xyz_id”:“9010”,
“spm_id”: “profile_1002”,
“sms”: “O”,
“email”: “O”,
},
{
“doc_type”: “CDs”,
“stud_id”: “1003”,
“xyz_id”:“9081”,
“spm_id”: “obj_1003”,
“sms”: “O”,
“email”: “I”,
}
]
}

Thank you,
Regards,
jayant

In your application read your CVS file and construct as Object like below and pass it as named parameter $inputcvs

{"1001":"9001",
"1002":"9010",
"1003":"9081"}

UPDATE default AS d
SET al.xyz_id = $inputcvs.[al.stud_id] FOR al IN d.A_list END
WHERE ......

Thanks for your response. As said, I am new to CouchDB, please help me with little more of elaboration. Yes, I can pass the CSV in the suggested format from my application In the query

UPDATE default AS d
SET al.xyz_id = $inputcvs.[al.stud_id] FOR al IN d.A_list END
WHERE …
I assume -
default is the bucket-name. Appreciate if someone can help me understand the query.

Thank you,
Regards,
Jayant

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