UPSERT only a single document

SQL++
1

I would like to UPDATE a single document in a bucket with new values:

UPDATE bucket_name USE KEYS 'document_id' SET variable1 = new_value1

UPDATE bucket_name USE KEYS 'document_id' SET variable2 = new_value2

If variable1 and variable2 don’t exist in the document, then I want them to be added (UPDATE will fail if a variable does not exist).

I thought I could use UPSERT to resolve this, but from what I see, UPSERT will apply changes to ALL documents in a bucket, but I want to UPSERT to only a single document. How can I achieve this?

2

Hi @deamontg, which version are you using? UPDATE works as expected.

select meta().id, * from default use keys "k_blr" ;
[
  {
    "default": {
      "country": "India",
      "faa": "BLR",
    },
    "id": "k_blr"
  }
]

update default use keys "k_blr" set faa = "blr2", name = "Bangalore" returning *;
[
  {
    "default": {
      "country": "India",
      "faa": "blr2",
      "name": "Bangalore"
    }
  }
]
2 Likes
3

@deamontg,

In N1QL UPDATE, if you update a nonexistent field, we simply ADD the field to the document and set it to the value specified.

On the reverse, if you want to remove a field, simply set the field to MISSING.

update default use keys "k_blr" set name = MISSING returning *;

4

Thanks for the replies, and for the advice. I am still being faced with an issue. Suppose I have this file:

{
  "id": "12345"
}

If I try to do this:

UPDATE bucket_name USE KEYS 'document_id' SET dictionary.value = new_value

The expected output should be:

{
  "id": "12345"
  "dictionary": {
    "value": "new_value"
  }
}

But this does NOT happen, the document remains the same. If the key “dictionary” existed beforehand though, then the update would work as expected.

5

UPDATE doesn’t create new objects, you’ll have to create the object before creating elements within it.
The following will do the job.

UPDATE bucket_name USE KEYS 'document_id' SET dictionary = CASE dictionary WHEN MISSING THEN {} ELSE dictionary, dictionary.value = new_value

6

Awesome, that’s exactly what I was looking for. Thank you!

I ended up using this:

UPDATE bucket_name USE KEYS 'document_id' SET dictionary = CASE
WHEN `dictionary` IS MISSING THEN {} ELSE `dictionary` END,
dictionary.value = 'new_value'
7

you can try this as well to create/update object field. It works irrespective of whether ‘dictionary’ exists already…

update default use keys "document_id" set dictionary = { "value": "new_value" } returning *;