Inserting, Modifying, and Deleting Data
You can perform various data manipulation operations in your table. You can add data, modify an existing data and remove data.
If you want to follow along with the examples, download the script baggageschema_loaddata.sql and execute it as shown below. This script creates the table used in the example and loads data into the table.
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
load
command, execute the
script.load -file baggageschema_loaddata.sql
Insert data
The INSERT statement is used to construct a new row and add it to a specified table. Optional column(s) may be specified after the table name. This list contains the column names for a subset of the table’s columns. The subset must include all the primary key columns. If no columns list is present, the default columns list is the one containing all the columns of the table, in the order, they are specified in the CREATE TABLE statement.
The columns in the columns list correspond one-to-one to the expressions (or DEFAULT keywords) listed after the VALUES clause (an error is raised if the number of expressions/DEFAULTs is not the same as the number of columns). These expressions/DEFAULTs compute the value for their associated column in the new row. An error is raised if an expression returns more than one item. If an expression returns no result, NULL is used as the result of that expression. If instead of an expression, the DEFAULT keyword appears in the VALUES list, the default value of the associated column is used as the value of that column in the new row. The default value is also used for any missing columns when the number of columns in the columns list is less than the total number of columns in the table.
BaggageInfo
table
providing all column
values:INSERT INTO BaggageInfo VALUES(
1762392196147,
"Birgit Naquin",
"M",
"165-742-5715",
"QD1L0T",
[ {
"id" : "7903989918469",
"tagNum" : "17657806240229",
"routing" : "JFK/MAD",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MAD",
"flightLegs" : [ {
"flightNo" : "BM495",
"flightDate" : "2019-03-07T07:00:00Z",
"fltRouteSrc" : "JFK",
"fltRouteDest" : "MAD",
"estimatedArrival" : "2019-03-07T14:00:00Z",
"actions" : [ {
"actionAt" : "MAD",
"actionCode" : "Offload to Carousel at MAD",
"actionTime" : "2019-03-07T13:54:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "ONLOAD to MAD",
"actionTime" : "2019-03-07T07:00:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "BagTag Scan at JFK",
"actionTime" : "2019-03-07T06:53:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "Checkin at JFK",
"actionTime" : "2019-03-07T05:03:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
"bagArrivalDate" : "2019-03-07T13:51:00Z"
} ]
)
Example 2: Skipping some data while doing an INSERT statement by specifying the DEFAULT clause.
INSERT INTO BaggageInfo VALUES(
1762397286805,
"Bonnie Williams",
DEFAULT,
DEFAULT,
"CZ1O5I",
[ {
"id" : "79039899129693",
"tagNum" : "17657806216554",
"routing" : "SFO/ORD/FRA",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "FRA",
"flightLegs" : [ {
"flightNo" : "BM572",
"flightDate" : "2019-03-02T05:00:00Z",
"fltRouteSrc" : "SFO",
"fltRouteDest" : "ORD",
"estimatedArrival" : "2019-03-02T09:00:00Z",
"actions" : [ {
"actionAt" : "SFO",
"actionCode" : "ONLOAD to ORD",
"actionTime" : "2019-03-02T05:24:00Z"
}, {
"actionAt" : "SFO",
"actionCode" : "BagTag Scan at SFO",
"actionTime" : "2019-03-02T04:52:00Z"
}, {
"actionAt" : "SFO",
"actionCode" : "Checkin at SFO",
"actionTime" : "2019-03-02T03:28:00Z"
} ]
}, {
"flightNo" : "BM582",
"flightDate" : "2019-03-02T05:24:00Z",
"fltRouteSrc" : "ORD",
"fltRouteDest" : "FRA",
"estimatedArrival" : "2019-03-02T13:24:00Z",
"actions" : [ {
"actionAt" : "FRA",
"actionCode" : "Offload to Carousel at FRA",
"actionTime" : "2019-03-02T13:20:00Z"
}, {
"actionAt" : "ORD",
"actionCode" : "ONLOAD to FRA",
"actionTime" : "2019-03-02T12:54:00Z"
}, {
"actionAt" : "ORD",
"actionCode" : "OFFLOAD from ORD",
"actionTime" : "2019-03-02T12:30:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-02T13:18:00Z",
"bagArrivalDate" : "2019-03-02T13:18:00Z"
} ]
)
Example 3: Specifying column names and skipping columns in the insert statement.
INSERT INTO BaggageInfo(ticketNo, fullName,confNo,bagInfo) VALUES(
1762355349471,
"Bryant Weber",
"LI7N1W",
[ {
"id" : "79039899149056",
"tagNum" : "17657806234185",
"routing" : "MEL/LAX/MIA",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MIA",
"flightLegs" : [ {
"flightNo" : "BM114",
"flightDate" : "2019-03-01T12:00:00Z",
"fltRouteSrc" : "MEL",
"fltRouteDest" : "LAX",
"estimatedArrival" : "2019-03-02T02:00:00Z",
"actions" : [ {
"actionAt" : "MEL",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-03-01T12:20:00Z"
}, {
"actionAt" : "MEL",
"actionCode" : "BagTag Scan at MEL",
"actionTime" : "2019-03-01T11:52:00Z"
}, {
"actionAt" : "MEL",
"actionCode" : "Checkin at MEL",
"actionTime" : "2019-03-01T11:43:00Z"
} ]
}, {
"flightNo" : "BM866",
"flightDate" : "2019-03-01T12:20:00Z",
"fltRouteSrc" : "LAX",
"fltRouteDest" : "MIA",
"estimatedArrival" : "2019-03-02T16:21:00Z",
"actions" : [ {
"actionAt" : "MIA",
"actionCode" : "Offload to Carousel at MIA",
"actionTime" : "2019-03-02T16:18:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MIA",
"actionTime" : "2019-03-02T16:12:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-03-02T16:02:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-02T16:09:00Z",
"bagArrivalDate" : "2019-03-02T16:09:00Z"
} ]
)
stream_acct
table
providing all column
values:INSERT INTO stream_acct VALUES(
1,
"AP",
"2023-10-18",
{
"firstName": "Adam",
"lastName": "Phillips",
"country": "Germany",
"contentStreamed": [{
"showName": "At the Ranch",
"showId": 26,
"showtype": "tvseries",
"genres": ["action", "crime", "spanish"],
"numSeasons": 4,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Season 1 episode 1",
"lengthMin": 85,
"minWatched": 85,
"date": "2022-04-18"
},
{
"episodeID": 30,
"lengthMin": 60,
"episodeName": "Season 1 episode 2",
"minWatched": 60,
"date": "2022 - 04 - 18 "
}]
},
{
"seasonNum": 2,
"numEpisodes": 2,
"episodes": [{
"episodeID": 40,
"episodeName": "Season 2 episode 1",
"lengthMin": 50,
"minWatched": 50,
"date": "2022-04-25"
},
{
"episodeID": 50,
"episodeName": "Season 2 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022-04-27"
}]
},
{
"seasonNum": 3,
"numEpisodes": 2,
"episodes": [{
"episodeID": 60,
"episodeName": "Season 3 episode 1",
"lengthMin": 50,
"minWatched": 50,
"date": "2022-04-25"
},
{
"episodeID": 70,
"episodeName": "Season 3 episode 2",
"lengthMin": 45,
"minWatched": 30,
"date": "2022 - 04 - 27 "
}]
}]
},
{
"showName": "Bienvenu",
"showId": 15,
"showtype": "tvseries",
"genres": ["comedy", "french"],
"numSeasons": 2,
"seriesInfo": [{
"seasonNum": 1,
"numEpisodes": 2,
"episodes": [{
"episodeID": 20,
"episodeName": "Bonjour",
"lengthMin": 45,
"minWatched": 45,
"date": "2022-03-07"
},
{
"episodeID": 30,
"episodeName": "Merci",
"lengthMin": 42,
"minWatched": 42,
"date": "2022-03-08"
}]
}]
}]
});
Using APIs to insert data into tables
private static void writeRows(NoSQLHandle handle, MapValue value)
throws Exception {
PutRequest putRequest =
new PutRequest().setValue(value).setTableName(tableName);
PutResult putResult = handle.put(putRequest);
if (putResult.getVersion() != null) {
System.out.println("Added a row to the stream_acct table");
} else {
System.out.println("Put failed");
}
}
def insert_record(handle,table_name,acct_data):
request = PutRequest().set_table_name(table_name)
.set_value_from_json(acct_data)
handle.put(request)
print('Added a row to the stream_acct table')
func insertData(client *nosqldb.Client, err error,
tableName string,value1 *types.MapValue )(){
putReq := &nosqldb.PutRequest{
TableName: tableName,
Value: value1,
}
putRes, err := client.Put(putReq)
if err != nil {
fmt.Printf("failed to put single row: %v\n", err)
return
}
fmt.Printf("Added a row to the stream_acct table\n")
}
/* Adding 3 records in acct_stream table */
let putResult = await handle.put(TABLE_NAME, JSON.parse(acct1));
let putResult1 = await handle.put(TABLE_NAME, JSON.parse(acct2));
let putResult2 = await handle.put(TABLE_NAME, JSON.parse(acct3));
console.log("Added rows to the stream_acct table");
private static async Task insertData(NoSQLClient client, String acctdet){
var putResult = await client.PutAsync(TableName,
FieldValue.FromJsonString(acctdet).AsMapValue);
if (putResult.ConsumedCapacity != null)
{
Console.WriteLine(" Added a row to the stream_acct table");
}
}
Upsert Data
The word UPSERT
combines UPDATE
and
INSERT
, describing the statement's function. Use an UPSERT statement to
insert a row where it does not exist, or to update the row with new values when it does.
Example 1: Updating data in the BaggageInfo
table using
UPSERT command.
Adam Phillips
is
shown
below.SELECT * FROM BaggageInfo WHERE fullname="Adam Phillips"
{
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1064",
"confNo" : "LE6J4Z",
"bagInfo" : [{
"bagArrivalDate" : "2019-02-01T16:13:00Z",
"flightLegs" : [{
"actions" : [{
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T06:13:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T05:47:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T04:38:00Z"
}],
"estimatedArrival" : "2019-02-01T11:00:00Z",
"flightDate" : "2019-02-01T06:00:00Z",
"flightNo" : "BM604",
"fltRouteDest" : "LAX",
"fltRouteSrc" : "MIA"
}, {
"actions" : [{
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-01T16:15:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T15:35:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T15:18:00Z"
}],
"estimatedArrival" : "2019-02-01T16:15:00Z",
"flightDate" : "2019-02-01T06:13:00Z",
"flightNo" : "BM667",
"fltRouteDest" : "MEL",
"fltRouteSrc" : "LAX"
}],
"id" : "79039899165297",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"lastSeenTimeGmt" : "2019-02-01T16:13:00Z",
"routing" : "MIA/LAX/MEL",
"tagNum" : "17657806255240"
}]
}
1 row returned
UPSERT
command. You can use an
optional RETURNING clause to fetch the values after UPSERT is performed. The updated
value for the customer with full name Adam Phillips is fetched as shown
below.UPSERT INTO BaggageInfo VALUES(
1762344493810,
"Adam Phillips",
"M",
"893-324-1864",
"LE6J4Y",
[ {
"id" : "79039899165297",
"tagNum" : "17657806255240",
"routing" : "MIA/LAX/MEL",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"flightLegs" : [ {
"flightNo" : "BM604",
"flightDate" : "2019-02-01T06:00:00Z",
"fltRouteSrc" : "MIA",
"fltRouteDest" : "LAX",
"estimatedArrival" : "2019-02-01T11:00:00Z",
"actions" : [ {
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T06:13:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T05:47:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T04:38:00Z"
} ]
}, {
"flightNo" : "BM667",
"flightDate" : "2019-02-01T06:13:00Z",
"fltRouteSrc" : "LAX",
"fltRouteDest" : "MEL",
"estimatedArrival" : "2019-02-01T16:15:00Z",
"actions" : [ {
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-01T16:15:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T15:35:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T15:18:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-02-01T16:18:00Z",
"bagArrivalDate" : "2019-02-01T16:18:00Z"
} ]
) RETURNING *
{
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1864",
"confNo" : "LE6J4Y",
"bagInfo" : [{
"bagArrivalDate" : "2019-02-01T16:18:00Z",
"flightLegs" : [{
"actions" : [{
"actionAt" : "MIA",
"actionCode" : "ONLOAD to LAX",
"actionTime" : "2019-02-01T06:13:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "BagTag Scan at MIA",
"actionTime" : "2019-02-01T05:47:00Z"
}, {
"actionAt" : "MIA",
"actionCode" : "Checkin at MIA",
"actionTime" : "2019-02-01T04:38:00Z"
}],
"estimatedArrival" : "2019-02-01T11:00:00Z",
"flightDate" : "2019-02-01T06:00:00Z",
"flightNo" : "BM604",
"fltRouteDest" : "LAX",
"fltRouteSrc" : "MIA"
}, {
"actions" : [{
"actionAt" : "MEL",
"actionCode" : "Offload to Carousel at MEL",
"actionTime" : "2019-02-01T16:15:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "ONLOAD to MEL",
"actionTime" : "2019-02-01T15:35:00Z"
}, {
"actionAt" : "LAX",
"actionCode" : "OFFLOAD from LAX",
"actionTime" : "2019-02-01T15:18:00Z"
}],
"estimatedArrival" : "2019-02-01T16:15:00Z",
"flightDate" : "2019-02-01T06:13:00Z",
"flightNo" : "BM667",
"fltRouteDest" : "MEL",
"fltRouteSrc" : "LAX"
}],
"id" : "79039899165297",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MEL",
"lastSeenTimeGmt" : "2019-02-01T16:18:00Z",
"routing" : "MIA/LAX/MEL",
"tagNum" : "17657806255240"
}]
}
Note:
If you do not supply values for all the columns in a UPSERT statement, then those columns get a DEFAULT value if such an option is specified in the corresponding CREATE TABLE statement or those columns are assigned NULL values.Example 2: Inserting data in the BaggageInfo
table using UPSERT
command.
Birgit Naquin
is added
using the UPSERT
command.SELECT * FROM BaggageInfo WHERE fullname="Birgit Naquin";
0 row returned
UPSERT INTO BaggageInfo VALUES(
1762392196147,
"Birgit Naquin",
"M",
"165-742-5715",
"QD1L0T",
[ {
"id" : "7903989918469",
"tagNum" : "17657806240229",
"routing" : "JFK/MAD",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MAD",
"flightLegs" : [ {
"flightNo" : "BM495",
"flightDate" : "2019-03-07T07:00:00Z",
"fltRouteSrc" : "JFK",
"fltRouteDest" : "MAD",
"estimatedArrival" : "2019-03-07T14:00:00Z",
"actions" : [ {
"actionAt" : "MAD",
"actionCode" : "Offload to Carousel at MAD",
"actionTime" : "2019-03-07T13:54:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "ONLOAD to MAD",
"actionTime" : "2019-03-07T07:00:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "BagTag Scan at JFK",
"actionTime" : "2019-03-07T06:53:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "Checkin at JFK",
"actionTime" : "2019-03-07T05:03:00Z"
} ]
} ],
"lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
"bagArrivalDate" : "2019-03-07T13:51:00Z"
} ]
)
{"NumRowsInserted":1}
1 row returned
{"NumRowsInserted":1}
which implies a new row has been
inserted. The value inserted using the UPSERT
command can be viewed as
shown
below:SELECT * FROM BaggageInfo where fullname="Birgit Naquin"
{
"ticketNo" : 1762392196147,
"fullName" : "Birgit Naquin",
"gender" : "M",
"contactPhone" : "165-742-5715",
"confNo" : "QD1L0T",
"bagInfo" : [{
"bagArrivalDate" : "2019-03-07T13:51:00Z",
"flightLegs" : [{
"actions" : [{
"actionAt" : "MAD",
"actionCode" : "Offload to Carousel at MAD",
"actionTime" : "2019-03-07T13:54:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "ONLOAD to MAD",
"actionTime" : "2019-03-07T07:00:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "BagTag Scan at JFK",
"actionTime" : "2019-03-07T06:53:00Z"
}, {
"actionAt" : "JFK",
"actionCode" : "Checkin at JFK",
"actionTime" : "2019-03-07T05:03:00Z"
}],
"estimatedArrival" : "2019-03-07T14:00:00Z",
"flightDate" : "2019-03-07T07:00:00Z",
"flightNo" : "BM495",
"fltRouteDest" : "MAD",
"fltRouteSrc" : "JFK"
}],
"id" : "7903989918469",
"lastActionCode" : "OFFLOAD",
"lastActionDesc" : "OFFLOAD",
"lastSeenStation" : "MAD",
"lastSeenTimeGmt" : "2019-03-07T13:51:00Z",
"routing" : "JFK/MAD",
"tagNum" : "17657806240229"
}]
}
1 row returned
Note:
If you do not supply values for all the columns in a UPSERT statement, then those columns get a DEFAULT value if such an option is specified in the corresponding CREATE TABLE statement or those columns are assigned NULL values. You can also use an optional RETURNING clause as part of the UPSERT command.Update Data
- The
SET
clause consists of two expressions: the target expression and the new-value expression. The target expression returns the items to be updated. The new-value expression may return zero or more items. If it returns an empty result, the SET is a no-op. If it returns more than one item, the items are enclosed inside a newly constructed array (this is the same as the way the SELECT clause treats multi-valued expressions in the select list)) So, effectively, the result of the new-value expression contains at most one item. - The
WHERE
clause specifies what row to update. In the current implementation, only single-row updates are allowed, so theWHERE
clause must specify a complete primary key. - There is an optional
RETURNING
clause which acts the same way as theSELECT
clause: it can be a "*", in which case, the full updated row will be returned, or it can have a list of expressions specifying what needs to be returned. - Furthermore, if no row satisfies the
WHERE
conditions, the update statement returns an empty result.
Example 1: Simple example to change the column values.
UPDATE BaggageInfo
SET contactPhone = "823-384-1964",
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810
Example 2: Update row data and fetch the values with a RETURNING clause.
UPDATE BaggageInfo
SET contactPhone = "823-384-1964",
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810 RETURNING *
{"ticketNo":1762344493810,"fullName":"Adam Phillips","gender":"M","contactPhone":"823-384-1964",
"confNo":"LE6J4Y",
"bagInfo":{"bagInfo":[{"bagArrivalDate":"2019.02.02 at 03:13:00 AEDT","flightLegs":
[{"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019.02.01 at 01:13:00 EST"},
{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019.02.01 at 00:47:00 EST"},
{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019.01.31 at 23:38:00 EST"}],
"estimatedArrival":"2019.02.01 at 03:00:00 PST","flightDate":"2019.02.01 at 01:00:00 EST",
"flightNo":"BM604","fltRouteDest":"LAX","fltRouteSrc":"MIA"},{"actions":
[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019.02.02 at 03:15:00 AEDT"},
{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019.02.01 at 07:35:00 PST"},
{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019.02.01 at 07:18:00 PST"}],
"estimatedArrival":"2019.02.02 at 03:15:00 AEDT","flightDate":"2019.01.31 at 22:13:00 PST",
"flightNo":"BM667","fltRouteDest":"MEL","fltRouteSrc":"LAX"}],"id":"79039899165297",
"lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
"lastSeenTimeGmt":"2019.02.02 at 03:13:00 AEDT","routing":"MIA/LAX/MEL","tagNum":"17657806255240"}]}}
Modify JSON data
WHERE
,
SET
and RETURNING
clause, the following clauses
can be used..
- The
ADD
clause is used to add new elements into one or more arrays. It consists of a target expression, which should normally return one or more array items, an optional position expression, which specifies the position within each array where the new elements should be placed, and a new-elements expression that returns the new elements to insert. - The
PUT
clause is used primarily to add new fields to a JSON document. It consists of a target expression, which should normally return one or more fields to be inserted into the target JSON document. - The
REMOVE
clause consists of a single target expression, which computes the items to be removed.
Example 1: Update table and add data in a JSON object
UPDATE BaggageInfo bag
ADD bag.bagInfo[0].flightLegs[0].actions 2 {"actionAt" : "LAX",
"actionCode" : "WAITING at LAX",
"actionTime" : "2019-02-01T06:13:00Z"}
WHERE ticketNo=1762344493810
RETURNING *
Example 2: Update table and update data from a JSON object.
UPDATE BaggageInfo bag
SET bag.bagInfo[0].flightLegs[0].actions[2]=
{"actionAt" : "LAX",
"actionCode" : "STILL WAITING at LAX",
"actionTime" : "2019-02-01T06:15:00Z"}
WHERE ticketNo=1762344493810 RETURNING *
Example 3: Update table and remove data in a JSON object.
REMOVE
clause to remove a given element
from an array. You need to specify which element of the array needs to be removed using
the index of the
array.UPDATE BaggageInfo bag
REMOVE bag.bagInfo[0].flightLegs[0].actions[1]
WHERE ticketNo=1762344493810
RETURNING *
Delete Data
The DELETE statement is used to remove from a table a set of rows satisfying a condition. The condition is specified in a WHERE clause that behaves the same way as in the SELECT expression. The result of the DELETE statement depends on whether a RETURNING clause is present or not. Without a RETURNING clause the DELETE returns the number of rows deleted. Otherwise, for each deleted row the expressions following the RETURNING clause are computed the same way as in the SELECT clause and the result is returned to the application.
Example 1: Delete data from a table with a simple WHERE clause.
DELETE FROM BaggageInfo
WHERE fullName = "Bonnie Williams"
Example 2: Delete data from a table with a RETURNING clause.
DELETE FROM BaggageInfo
WHERE ticketNo = 1762392196147
RETURNING fullName,confNo
{"fullName":"Birgit Naquin","confNo":"QD1L0T"}
Note:
If any error occurs during the execution of a DELETE statement, there is a possibility that some rows will be deleted and some not. The system does not keep track of what rows got deleted and what rows are not yet deleted. This is because Oracle NoSQL Database focuses on low latency operations. Long-running operations across shards are not coordinated using a two-phase commit and lock mechanism. In such cases, it is recommended that the application re-run the DELETE statement.