Modifying Data with N1QL

how-to

How to modify documents using N1QL.

Introduction

To modify documents in a keyspace, you can use the UPDATE statement or the the MERGE statement.

If you want to try out the examples in this section, follow the instructions given in Do a Quick Install to install Couchbase Server, configure a cluster, and load a sample dataset. Read the following for further information about the tools available for editing and executing queries:

Please note that the examples in this guide will alter the data in your sample buckets. To restore your sample data, remove and reinstall the travel-sample bucket. Refer to Sample Buckets for details.

Modifying Documents by Key

To modify one or more documents by key, use the UPDATE statement with the USE KEYS hint:

Use the FROM keyword to specify the keyspace which contains the documents to be modified.
Use the USE KEYS hint to specify a document keys or array of document keys.
Use the SET and UNSET clauses to set and unset attributes as required.
If required, use the RETURNING clause to specify what should be returned when the documents are deleted.

You can combine this approach with the WHERE clause if necessary.

The following query adds an attribute to a document with the key "landmark_10090" and returns the added attribute.

Query

UPDATE `travel-sample`.inventory.landmark
USE KEYS "landmark_10090"
SET nickname = "Squiggly Bridge"
RETURNING landmark.nickname;

Results

[
  {
    "nickname": "Squiggly Bridge"
  }
]

The following query removes an attribute from a document with the key "landmark_10090" and returns the name attribute.

Query

UPDATE `travel-sample`.inventory.landmark
USE KEYS "landmark_10090"
UNSET nickname
RETURNING landmark.name;

Results

[
  {
    "name": "Tradeston Pedestrian Bridge"
  }
]

Modifying Documents by Filter

To modify documents by filter, use the UPDATE statement with the WHERE clause:

Use the FROM keyword to specify the keyspace which contains the documents to be modified.
Use the SET and UNSET clauses to set and unset attributes as required.
Use the WHERE clause to specify the condition that needs to be met for documents to be modified.
If required, use the LIMIT clause to specify the greatest number of documents that may be modified.
If required, use the RETURNING clause to specify what should be returned when the documents are modified.

You can combine this approach with the USE KEYS hint if necessary.

The following query standardizes the capitalization of the city field for all airports in San Francisco.

Query

UPDATE `travel-sample`.inventory.airport
SET city = "San Francisco"
WHERE lower(city) = "san francisco"
RETURNING *;

Results

[
  {
    "airport": {
      "airportname": "San Francisco Intl",
      "city": "San Francisco",
      "country": "United States",
      "faa": "SFO",
      "geo": {
        "alt": 13,
        "lat": 37.618972,
        "lon": -122.374889
      },
      "icao": "KSFO",
      "id": 3469,
      "type": "airport",
      "tz": "America/Los_Angeles"
    }
  }
]

Modifying Documents by Subquery

To modify documents based on the results returned by a SELECT query, use a subquery in the filter.

The following query adds an attribute to the airport whose FAA code is NCE, containing a list of every hotel in Nice.

Query

UPDATE `travel-sample`.inventory.airport AS a
SET hotels =
  (SELECT  h.name, h.id
  FROM  `travel-sample`.inventory.hotel AS h
  WHERE h.city = "Nice")
WHERE a.faa ="NCE"
RETURNING a;

Results

[
  {
    "a": {
      "airportname": "Cote D\\'Azur",
      "city": "Nice",
      "country": "France",
      "faa": "NCE",
      "geo": {
        "alt": 12,
        "lat": 43.658411,
        "lon": 7.215872
      },
      "hotels": [
        {
          "id": 20419,
          "name": "Best Western Hotel Riviera Nice"
        },
        {
          "id": 20420,
          "name": "Hotel Anis"
        },
        {
          "id": 20421,
          "name": "NH Nice"
        },
        {
          "id": 20422,
          "name": "Hotel Suisse"
        },
        {
          "id": 20423,
          "name": "Gounod"
        },
        {
          "id": 20424,
          "name": "Grimaldi Hotel Nice"
        },
        {
          "id": 20425,
          "name": "Negresco"
        }
      ],
      "icao": "LFMN",
      "id": 1354,
      "type": "airport",
      "tz": "Europe/Paris"
    }
  }
]

Modifying Nested Arrays and Objects

To modify nested objects in an array, use the FOR clause within the SET or UNSET clause.

Use the FOR keyword to specify a dummy variable that refers to each object in the array.
Use the IN keyword to specify the path to the array.
If required, use the WHEN clause to filter the objects in the array.

The following query adds an attribute to each object in the schedule array in document "landmark_10090".

Query

UPDATE `travel-sample`.inventory.route t
USE KEYS "route_10003"
SET s.codeshare = NULL FOR s IN schedule END
RETURNING t;

Results

[
  {
    "t": {
      "airline": "AF",
      "airlineid": "airline_137",
      "destinationairport": "ATL",
      "distance": 654.9546621929924,
      "equipment": "757 739",
      "id": 10003,
      "schedule": [
        {
          "codeshare": null,
          "day": 0,
          "flight": "AF986",
          "utc": "22:26:00"
        },
        {
          "codeshare": null,
          "day": 0,
          "flight": "AF962",
          "utc": "04:25:00"
        },
// ...
      ],
      "sourceairport": "TPA",
      "stops": 0,
      "type": "route"
    }
  }
]

Using Object Functions and Array Functions

To modify an object attribute, use an object function. Similarly, to modify an array attribute, use an array function.

The following query adds an attribute to the ratings object within each review in document "landmark_10025".

Query

UPDATE `travel-sample`.inventory.hotel AS h USE KEYS "hotel_10025"
SET i.ratings = OBJECT_ADD(i.ratings, "new", "new_value" ) FOR i IN reviews END
RETURNING h.reviews[*].ratings;

Results

[
  {
    "ratings": [
      {
        "Cleanliness": 5,
        "Location": 4,
        "Overall": 4,
        "Rooms": 3,
        "Service": 5,
        "Value": 4,
        "new": "new_value"
      },
      {
        "Business service (e.g., internet access)": 4,
        "Check in / front desk": 4,
        "Cleanliness": 4,
        "Location": 4,
        "Overall": 4,
        "Rooms": 3,
        "Service": 3,
        "Value": 5,
        "new": "new_value"
      }
    ]
  }
]

For more information and examples, refer to Object Functions and Array Functions.

Using the ARRAY Operator

Alternatively, to access deeply-nested attributes, use the ARRAY operator.

The following query removes an attribute from the ratings object within each review in document "landmark_10025".

Query

UPDATE `travel-sample`.inventory.hotel AS h USE KEYS "hotel_10025"
UNSET i.new FOR i IN
  (ARRAY j.ratings FOR j IN reviews END)
END
RETURNING h.reviews[*].ratings;

Results

[
  {
    "ratings": [
      {
        "Cleanliness": 5,
        "Location": 4,
        "Overall": 4,
        "Rooms": 3,
        "Service": 5,
        "Value": 4
      },
      {
        "Business service (e.g., internet access)": 4,
        "Check in / front desk": 4,
        "Cleanliness": 4,
        "Location": 4,
        "Overall": 4,
        "Rooms": 3,
        "Service": 3,
        "Value": 5
      }
    ]
  }
]

For more information and examples, refer to ARRAY.

Merging and Updating Documents

You can also update documents by merging: that is, by joining one data source to another, and updating any documents that match.

To update documents using a merge, use the MERGE statement with the UPDATE action:

Use the INTO keyword to specify the target. This is the data source in which documents will be updated.
Use the USING keyword to specify the source. This is the data source to check against the target.
Use the ON keyword to specify the merge predicate. This is a condition that must be met to match an object in the source with an object in the target.
Use WHEN MATCHED THEN UPDATE to specify that when a document in the source matches a document in the target, the document in the target should be updated.
1. Use the SET and UNSET clauses to set and unset attributes as required.
2. If necessary, use the WHERE clause to specify any further conditions that must be met for documents to be updated.
If required, use the LIMIT clause to specify the greatest number of documents that may be updated.
If required, use the RETURNING clause to specify what should be returned when the documents are updated.

The following query compares a source set of hotel data with the target hotel keyspace. If the hotel already exists in the hotel keyspace, the record is updated, and the query returns the target document key and the attributes which were changed.

Query

MERGE INTO `travel-sample`.inventory.hotel t
USING [
  {"id":"21728", "vacancy": true},
  {"id":"21730", "vacancy": true}
] source
ON meta(t).id = "hotel_" || source.id
WHEN MATCHED THEN
  UPDATE SET t.old_vacancy = t.vacancy,
             t.vacancy = source.vacancy
RETURNING meta(t).id, t.old_vacancy, t.vacancy;

Results

[
  {
    "id": "hotel_21728",
    "old_vacancy": false,
    "vacancy": true
  },
  {
    "id": "hotel_21730",
    "old_vacancy": true,
    "vacancy": true
  }
]

For more information and examples, refer to MERGE.

Reference and explanation:

UPDATE
MERGE

Querying with SDKs:

C | .NET | Go | Java | Node.js | PHP | Python | Ruby | Scala

Modifying Data with N1QL

Introduction

Modifying Documents by Key

Modifying Documents by Filter

Modifying Documents by Subquery

Modifying Nested Arrays and Objects

Using Object Functions and Array Functions

Using the ARRAY Operator

Merging and Updating Documents

Related Links