Create OR Update existing document

SQL++
1

Hi all,

I am currently trying to:

  1. Check if a document exists,
  2. If document exists, update the document,
  3. If document doesn’t exist, create the document.

I can’t seem to come up with the query that accomplishes this. Any help would be appreciated.

2

Use UPSERT .

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/upsert.html

3

How does upsert know if the document is already created? @vsr1

4

In couchbase KEY must be unique.

5

My goal here is to either use the ARRAY_APPEND() on a an already existing document, or create a document with the array. Is this possible with upsert? @vsr1

6

UPSERT you need to provide whole document. Post samples .

You can also use MERGE if you want to update specific field or insert whole document.

7

For example, when a user is to make a new appointment on my Admin panel that I am creating it should first search for the document with a given date. If that document exists then I need to append the new Appointment Object to the end of the schedule array. Otherwise I need create the document with date X and also create the array which will contain the new appointment. @vsr1

8 
MERGE INTO default AS d USING
   default AS o USE KEYS "testuser.2019-02-11"
ON KEY MTEA(o).id
WHEN MATCHED THEN UPDATE SET d.schedule  = ARRAY_APPEND(d.schedule, {})
WHEN NOT MATCHED THEN INSERT { new document}
;

Other option is UPDATE , If none updated then do insert. (insert and if fails do update)

9

shouldn’t the query:

MERGE INTO HomeCarePlus AS d USING HomeCarePlus AS o USE KEYS “2019-02-10”
ON KEY o.date
WHEN MATCHED THEN UPDATE SET d.schedule = ARRAY_APPEND(d.schedule, {“test”:“test”});

Append the test: test to the array? @vsr1

10

ON KEY provide the document key

INSERT INTO default VALUES("2019-02-11", { "_id":"testuser.2019-02-11", "type":"appointment", "employee_id":"testuser", "date":"2019-02-11", "schedule": [ { "appointment_id":"10", "first_name":"Mike", "last_name":"Kelly", "address":"10 fake street", "gender":"male", "status":"NEW", "start_time":"1:00 pm", "end_time":"1:30 pm", "punched_in_time":"", "punched_out_time":"" } ] });

MERGE INTO default AS d USING [1] AS o
ON KEY "2019-02-11"
WHEN MATCHED THEN UPDATE SET d.schedule  = ARRAY_APPEND(d.schedule, {"test":"test"})
WHEN NOT MATCHED THEN INSERT {"schedule":[], "date":META(o).id};

MERGE INTO default AS d USING [1] AS o
ON KEY "2019-02-10"
WHEN MATCHED THEN UPDATE SET d.schedule  = ARRAY_APPEND(d.schedule, {"test":"test"})
WHEN NOT MATCHED THEN INSERT {"schedule":[], "date":"2019-02-10"};