What is the Equvalent N1ql query for inserting the document in the couchbase

Java SDK
,
1

The following query is for fetching the documents from the couchbase while working with Java_client

 N1qlQueryResult result = bucket.query(N1qlQuery.simple(
	        		"SELECT * FROM `default;"));

How to write the statement to insert the docs into Couchbase using N1qlQuery like above?

2

Hi,

The following query would work:

INSERT INTO product (KEY, VALUE) VALUES ("odwalla-juice1", { "productId": "odwalla-juice1", 
  "unitPrice": 5.40, "type": "product", "color":"red"});

See the full documentation here: http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/upsert.html

3

Thank you @ldoguin for the reply. I know this N1qlQuery but I wan to write it using
bucket.query(N1qlQuery.simple(
** “INSERT INTO default(KEY, VALUE) values(“jsonStrnig”);**”));

So that I can write in my Java program while communicating to the Couchbase

4

@pioneer.suri the question is: why do you want to use N1QL to insert records and not use the regular KV mutation APIs? if its just a plain insert you can do bucket.insert, upsert or replace.

Any specific example so we can help you figure it out?

5

Hello @daschl, Thank you for the immediate response. Initially I have inserted using Mutation API like:

JsonObject infoObj = JsonObject.create()
.put(“info”, jsondoc);

couchbase.png1280×1024 135 KB

bucket.upsert(JsonDocument.create(“device”, infoObj));

but the document is stored as shown above the attached image:

So I am not able to retrieve required json nodes from the documents.
That’s the reason I wanted to insert in different way(N1ql) instead of using Mutation API.
Please help me to solve this Issue. Let me know If you need any more details.

6

Yeah your device is not a json object but actually a Json string! can you copy the full code you are using to insert the document? I think I can spot the issue then probably pretty quickly.

7

Please find the below code which is used for inserting the json string into the default bucket.

Cluster cluster = CouchbaseCluster.create(“localhost”);

Bucket bucket = cluster.openBucket(“default”);

String finalData = “{‘device’:{‘y’:{‘devicesmsPropertyList’:{‘dskFlag’:‘false’,‘serialId’:1000,‘inputTray’:{‘LIST’:{‘e’:[{‘inTray’:{‘id’:‘1’,‘name’:‘BypassTray’,‘amount’:1000 etc…}}}}”;

JsonObject infoObj = JsonObject.create().put(“info”, finalData);

bucket.upsert(JsonDocument.create(“device”, infoObj));

8

Okay that’s easy to fix :slight_smile: If you pass in a raw json document you should use the RawJsonDocument instead. so Try:

String finalData = "{'device':{'y':{'devicesmsPropertyList':{'dskFlag':'false','serialId':1000,'inputTray':{'LIST':{'e':[{'inTray':{'id':'1','name':'BypassTray','amount':1000 etc......}}}}";
bucket.upsert(RawJsonDocument.create("device",finalData));

Note that this will not put it into “info” since you are nesting. Alternatively if you want to make it work like this, even if its a little more wasteful on the GC side you could do

 JsonObject infoObj = JsonObject.create().put("info", JsonObject.fromJson(finalData));

Because in your case you are mixing raw json data versus structured data in java if you know what I mean :slight_smile:

1 Like
9

Thank you very much @daschl, It is very useful.

When I use (RawJsonDocument.create(), It is storing the data in the binary format.

String finalData = “{‘device’:{‘y’:{‘devicesmsPropertyList’:{‘dskFlag’:‘false’,‘serialId’:1000,‘inputTray’:{‘LIST’:{‘e’:[{‘inTray’:{‘id’:‘1’,‘name’:‘BypassTray’,‘amount’:1000 etc…}}}}”;

bucket.upsert(RawJsonDocument.create(“device”,finalData));

Fetching the data from the bucket, It is giving the following result.

select * from default USE KEYS[“device1”];

Result:

[
{
“default”: “<binary (24543 b)>”
}
]

May I know In which Scenario it is preferable, because We are not getting the data in the required format.

So I have used the following JSONObject to create JSONDocument.

JsonObject infoObj = JsonObject.create().put(“info”, JsonObject.fromJson(finalData));
Now it is working as per my expectation. but You are saying It will waste on GC side. Is it meant, Performance will differ comapre to binary format.

10

In your example above you store the document as “device” key but query “device1”, that’s why its not returned I guess?

if you use the RawJsonDocument and pass in a valid json string as the document value it will be stored as proper JSON on the server side, many users are doing that. In your case it depends on what you’d like to do, if you get the raw json already it makes sense to store it directly. In the other case if you want to make some modifications to it converting it into an actual object gives you more flexibility.