Modify Data with a Query
- Capella Operational
- how-to
How to modify documents using SQL++.
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 Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy 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 database. To restore your sample data, remove and reinstall the travel sample data. Refer to Import Data with the Capella UI 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.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
UPDATE landmark
USE KEYS "landmark_10090"
SET nickname = "Squiggly Bridge"
RETURNING landmark.nickname;[
{
"nickname": "Squiggly Bridge"
}
]The following query removes an attribute from a document with the key "landmark_10090" and returns the name attribute.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
UPDATE landmark
USE KEYS "landmark_10090"
UNSET nickname
RETURNING landmark.name;[
{
"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.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
UPDATE airport
SET city = "San Francisco"
WHERE lower(city) = "san francisco"
RETURNING *;[
{
"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.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
UPDATE airport AS a
SET hotels =
(SELECT h.name, h.id
FROM hotel AS h
WHERE h.city = "Nice")
WHERE a.faa ="NCE"
RETURNING a;[
{
"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".
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
UPDATE route t
USE KEYS "route_10003"
SET s.codeshare = NULL FOR s IN schedule END
RETURNING t;[
{
"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".
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
UPDATE 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;[
{
"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".
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
UPDATE 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;[
{
"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.
-
Use the SET and UNSET clauses to set and unset attributes as required.
-
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.
Set the query context to the inventory scope in the travel sample dataset.
For more information, see Setting the Query Context.
MERGE INTO 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;[
{
"id": "hotel_21728",
"old_vacancy": false,
"vacancy": true
},
{
"id": "hotel_21730",
"old_vacancy": true,
"vacancy": true
}
]For more information and examples, refer to MERGE.