FROM (INSERT, REMOVE, or SET) command in Amazon QLDB

In Amazon QLDB, a statement that starts with FROM is a PartiQL extension that lets you to insert and remove specific elements within a document. You can also use this statement to update existing elements in a document, similar to the UPDATE command.

Syntax

FROM-INSERT

Insert a new element within an existing document. To insert a new top-level document into a table, you must use INSERT.

FROM table_name [ AS table_alias ] [ BY id_alias ]
[ WHERE condition ]
INSERT INTO element VALUE data [ AT key_name ]

FROM-REMOVE

Remove an existing element within a document, or remove an entire top-level document. The latter is semantically the same as the traditional DELETE syntax.

FROM table_name [ AS table_alias ] [ BY id_alias ]
[ WHERE condition ]
REMOVE element

FROM-SET

Update one or more elements within a document. If an element doesn't exist, it's inserted. This is semantically the same as the traditional UPDATE syntax.

FROM table_name [ AS table_alias ] [ BY id_alias ]
[ WHERE condition ]
SET element = data [, element = data, ... ]

Parameters

Nested collections

While you can run a DML statement on a single table only, you can specify nested collections within documents in that table as additional sources. Each alias that you declare for a nested collection can be used in the WHERE clause and the SET, INSERT INTO, or REMOVE clause.

For example, the FROM sources of the following statement include both the VehicleRegistration table and the nested Owners.SecondaryOwners structure.

FROM VehicleRegistration r, @r.Owners.SecondaryOwners o
WHERE r.VIN = '1N4AL11D75C109151' AND o.PersonId = 'abc123'
SET o.PersonId = 'def456'

This example updates the specific element of the SecondaryOwners list that has a PersonId of 'abc123' within the VehicleRegistration document that has a VIN of '1N4AL11D75C109151'. This expression lets you specify an element of a list by its value rather than its index.

Return value

documentId – The unique ID of each document that you updated or deleted.

Examples

Modify an element within a document. If the element doesn't exist, it's inserted.

FROM Vehicle AS v
WHERE v.VIN = '1N4AL11D75C109151' AND v.Color = 'Silver'
SET v.Color = 'Shiny Gray'

Modify or insert an element and filter on the system-assigned document id metadata field.

FROM Vehicle AS v BY v_id
WHERE v_id = 'documentId'
SET v.Color = 'Shiny Gray'

Modify the PersonId field of the first element in the Owners.SecondaryOwners list within a document.

FROM VehicleRegistration AS r
WHERE r.VIN = '1N4AL11D75C109151'
SET r.Owners.SecondaryOwners[0].PersonId = 'abc123'

Remove an existing element within a document.

FROM Person AS p
WHERE p.GovId = '111-22-3333'
REMOVE p.Address

Remove a whole document from a table.

FROM Person AS p
WHERE p.GovId = '111-22-3333'
REMOVE p

Remove the first element of the Owners.SecondaryOwners list within a document in the VehicleRegistration table.

FROM VehicleRegistration AS r
WHERE r.VIN = '1N4AL11D75C109151'
REMOVE r.Owners.SecondaryOwners[0]

Insert {'Mileage':26500} as a top-level name-value pair within a document in the Vehicle table.

FROM Vehicle AS v 
WHERE v.VIN = '1N4AL11D75C109151'
INSERT INTO v VALUE 26500 AT 'Mileage'

Append {'PersonId':'abc123'} as a name-value pair in the Owners.SecondaryOwners field of a document in the VehicleRegistration table. Note that Owners.SecondaryOwners must already exist and must be a list data type for this statement to be valid. Otherwise, the keyword AT is required in the INSERT INTO clause.

FROM VehicleRegistration AS r 
WHERE r.VIN = '1N4AL11D75C109151'
INSERT INTO r.Owners.SecondaryOwners VALUE { 'PersonId' : 'abc123' }

Insert {'PersonId':'abc123'} as the first element in the existing Owners.SecondaryOwners list within a document.

FROM VehicleRegistration AS r 
WHERE r.VIN = '1N4AL11D75C109151'
INSERT INTO r.Owners.SecondaryOwners VALUE {'PersonId' : 'abc123'} AT 0

Append multiple name-value pairs to the existing Owners.SecondaryOwners list within a document.

FROM VehicleRegistration AS r 
WHERE r.VIN = '1N4AL11D75C109151'
INSERT INTO r.Owners.SecondaryOwners << {'PersonId' : 'abc123'}, {'PersonId' : 'def456'} >>

Running programmatically using the driver

To learn how to programmatically run this statement using the QLDB driver, see the following tutorials in Getting started with the driver: