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

[jayantbit]

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

[vsr1]

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 ......

[jayantbit]

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

[vsr1]

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`

[jayantbit]

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?

[vsr1]

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

Named Parameters in the Web Console

Hello - We have a N1QL query that a handful of our data folks use for adhoc analysis. The queries have a few large arrays of values we use in an IN statement (example below). They need to be able to switch between 5 or so code sets when running their query. We have an index on v.drgCode, such that performance is GREAT when the array of codes is included in the query. However if we use a sub-select inside the WHERE clause to source the codes out of a document, performance is terrible. Upon …

cbq shell Mastering the Couchbase N1QL Shell: Scripting - DZone

[jayantbit]

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.

[vsr1]

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.

[jayantbit]

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.

[vsr1]

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"}

[jayantbit]

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

[vsr1]

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];

[jayantbit]

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>

[vsr1]

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];

[jayantbit]

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>

[jayantbit]

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

[jayantbit]

@jayantbit

Error evaluating SET clause. - cause: No value for named parameter

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.

[vsr1]

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);

[jayantbit]

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

[jayantbit]

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