Amazon Quantum Ledger Database (Amazon QLDB)
Developer Guide

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 owned by Raul Lewis in Seattle, WA, and it needs to be initially registered.

Then, suppose that Raul sells the car to a resident in Everett, WA named Brent Logan. Afterwards, 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.

To modify documents

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

  2. In the navigation pane, choose Query 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. To learn more about views in QLDB, see Core Concepts in Amazon QLDB.

    Note

    Best practice is to use the document id metadata field as a foreign key. For more information, see Querying Document Metadata.

  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 in the VehicleRegistration table.

    UPDATE VehicleRegistration AS r SET r.Owners.PrimaryOwner.PersonId = 'IN7MvYtUjkp1GMZu0F6CG9', --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 Modification History for a Document.