Step 4: Modify documents in a ledger

Now that you have data to work with, you can start making changes to documents in the vehicle-registration ledger in Amazon QLDB. For example, consider the Audi A5 with VIN 1N4AL11D75C109151. This car is initially owned by a driver named Raul Lewis in Seattle, WA.

Suppose that Raul sells the car to a resident in Everett, WA named Brent Logan. Then, Brent and Alexis Pena decide to get married. Brent wants to add Alexis as a secondary owner on the registration. In this step, the following data manipulation language (DML) statements demonstrate how to make the appropriate changes in your ledger to reflect these events.

Tip

As a best practice, use a document's system-assigned id as a foreign key. While you can define fields that are intended to be unique identifiers (for example, a vehicle's VIN), the true unique identifier of a document is its id. This field is included in the document's metadata, which you can query in the committed view (the system-defined view of a table).

For more information about views in QLDB, see Core concepts. To learn more about metadata, see Querying document metadata.

To modify documents

1. Open the Amazon QLDB console at https://console.aws.amazon.com/qldb.

2. In the navigation pane, choose PartiQL editor.

3. Choose the vehicle-registration ledger.

   Note

   If you set up your ledger using the console's automatic Load sample data feature, skip ahead to step 6.

4. If you manually ran INSERT statements to load the sample data, continue with these steps.

   To initially register Raul as this vehicle's owner, start by finding his system-assigned document id in the Person table. This field is included in the document's metadata, which you can query in the system-defined view of the table, called the committed view.

   In the query editor window, enter the following statement, and then choose Run.

   SELECT metadata.id FROM _ql_committed_Person AS p
   WHERE p.data.FirstName = 'Raul' and p.data.LastName = 'Lewis'

   The prefix _ql_committed_ is a reserved prefix signifying that you want to query the committed view of the Person table. In this view, your data is nested in the data field, and metadata is nested in the metadata field.

5. Now, use this id in an UPDATE statement to modify the appropriate document in the VehicleRegistration table. Enter the following statement, and then choose Run.

   UPDATE VehicleRegistration AS r
   SET r.Owners.PrimaryOwner.PersonId = '294jJ3YUoH1IEEm8GSabOs' --replace with your id
   WHERE r.VIN = '1N4AL11D75C109151'

   Confirm that you modified the Owners field by issuing this statement.

   SELECT r.Owners FROM VehicleRegistration AS r
   WHERE r.VIN = '1N4AL11D75C109151'

6. To transfer the vehicle's ownership to Brent in the city of Everett, first find his id from the Person table with the following statement.

   SELECT metadata.id FROM _ql_committed_Person AS p
   WHERE p.data.FirstName = 'Brent' and p.data.LastName = 'Logan'

   Next, use this id to update the PrimaryOwner and the City in the VehicleRegistration table.

   UPDATE VehicleRegistration AS r
   SET r.Owners.PrimaryOwner.PersonId = '7NmE8YLPbXc0IqesJy1rpR', --replace with your id
       r.City = 'Everett'
   WHERE r.VIN = '1N4AL11D75C109151'

   Confirm that you modified the PrimaryOwner and City fields by issuing this statement.

   SELECT r.Owners.PrimaryOwner, r.City
   FROM VehicleRegistration AS r
   WHERE r.VIN = '1N4AL11D75C109151'

7. To add Alexis as a secondary owner of the car, find her Person id.

   SELECT metadata.id FROM _ql_committed_Person AS p
   WHERE p.data.FirstName = 'Alexis' and p.data.LastName = 'Pena'

   Then, insert this id into the SecondaryOwners list with the following FROM-INSERT DML statement.

   FROM VehicleRegistration AS r 
   WHERE r.VIN = '1N4AL11D75C109151'
   INSERT INTO r.Owners.SecondaryOwners
       VALUE { 'PersonId' : '5Ufgdlnj06gF5CWcOIu64s' } --replace with your id

   Confirm that you modified SecondaryOwners by issuing this statement.

   SELECT r.Owners.SecondaryOwners FROM VehicleRegistration AS r
   WHERE r.VIN = '1N4AL11D75C109151'

To review these changes in the vehicle-registration ledger, see Step 5: View the revision history for a document.