How to add new data row in an array?


#1

Here is a simple document

Bucket : BOOK    
doc id: book01
    {
    "title": "adventure",
    "custom": [
    	{"modified": "a01","time": 200},
    	{"modified": "a02","time": 201},
    	{"modified": "a03","time": 202},
    	]
    }

I want to add new data {“modified”: “a04”,“time”: 204} in custom array and how to do that efficiently?

My method is to retrieve the document and modified the document(coyp the old data and add new row),
then replace the document.

JsonDocument getUserDoc = cs.getDocument(book01, BOOK);
//Modify the doc by reading the all data and add new row.
//It's a little waste time and cost lines to do that.

Does there have any better solution(or N1QL)?


#2

N1QL mutative dml is still in beta, but it allows you to do that.

Here is an example UPDATE statement:

UPDATE default USE KEYS "docID" SET hobby = ARRAY_PUT(IFNULL(hobby, []), "biking");

With a few comments:

UPDATE default USE KEYS "docID" --update the document of id "docID"
SET hobby = ARRAY_PUT( --ARRAY_PUT will only append if value not present
  IFNULL(hobby, []) --(if the field is null, work from an empty array)
  , "biking"); --the value to append if not present

Note that if you test that in cbq, you can end the statement with a “RETURNING *” in order to have feedback on what was modified (it will return the content of the updated document as a result).

However, under the covers it just fetch the document, put the correct fields in and persist the document back (as far as I know).


#3

thanks, @simonbasle

I have another question.
What happened if I have multiple apps to append this document?
There are 2 apps to add {“modified”: “A1”,“time”: 300}, {“modified”: “B1”,“time”: 301} respectively and may be access it concurrently.

Would the result be
{“modified”: “a01”,“time”: 200},
{“modified”: “a02”,“time”: 201},
{“modified”: “a03”,“time”: 202},
{“modified”: “A1”,“time”: 300},
{“modified”: “B1”,“time”: 301}

Or it may be lost some data?(lost A1 or B1)


#4

@geraldss or @colm can you develop on that?


#5

Yes, you can use ARRAY_PUT() or ARRAY_APPEND() together with UPDATE…SET.

If there is a concurrent UPDATE, one will succeed and the others will error out, which allows the application to retry the ones that error out. No data will be lost.


#6

Hi @geraldss - does the error that’s returned give a specific CAS failure code/message that the SDKs then use to raise an exception? Vs other error messages that might be returned.


#7

@daves, yes, N1QL returns a specific error and message. @simonbasle, hopefully the SDK exposes this error to the caller.


#8

Yes, the Java SDK will expose all errors under the errors part of the JSON response as individual JsonObject items.
Note: the way SDKs are dealing with these query service errors is to expose them to the user as JSON/a list of error objects, not by throwing Exceptions.