Querying and Updating Your Data

After creating tables in your QLDB ledger and loading them with data, you can run queries to review the vehicle registration data that you just inserted. You can also make changes to your documents and query the complete history of those modifications.

Open the Query editor and run queries

In the navigation pane of the QLDB console, go to the “Query editor” page and then choose “vehicle-registration” in the list of ledgers. Query Editor

Query the Vehicle table for a particular VIN. In the query editor window, enter the following statement, and then click ‘Run’:

SELECT * FROM Vehicle AS v WHERE v.VIN = '1N4AL11D75C109151'

Your query results should look similar to the following:

VIN Type Year Make Model Color
“1N4AL11D75C109151” “Sedan” 2011 “Audi” “A5” “Silver”

Then, you can join the Vehicle and VehicleRegistration tables to get the vehicle’s registration information. Run the following statement:

SELECT v.VIN, r.LicensePlateNumber, r.State, r.City, r.Owners FROM Vehicle AS v, VehicleRegistration AS r WHERE v.VIN = '1N4AL11D75C109151' AND v.VIN = r.VIN

Your query results should look similar to the following:

VIN LicensePlateNumber State City Owners
“1N4AL11D75C109151” “LEWISR261LL” “WA” “Seattle” {PrimaryOwner:{PersonId:“BFJKdXhnLRT27sXBnoj NGW”},SecondaryOwners:[]}

Modify documents in your ledger

Now that you have data to work with, you can start making changes to documents in your ledger. For example, consider the car with VIN 1N4AL11D75C109151 which is registered to a driver named Raul Lewis. Suppose that you want to transfer its ownership to Brent Logan.

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). The _ql_committed_ prefix signifies 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.

Here is an example of a complete document with metadata from the _ql_committed_Person view:

{
  blockAddress: {
    strandId: "2EOkrLUtQa226ddeoU7ja3",
    sequenceNo: 36
  },
  hash: {{LCynMYTJiPwmLoTOL0xl3KB+Ikju5jbfb39Ynsw240g=}},
  data: {
    FirstName: "Brent",
    LastName: "Logan",
    DOB: 1967-07-03T,
    GovId: "LOGANB486CG",
    GovIdType: "Driver License",
    Address: "43 Stockert Hollow Road, Everett, WA, 98203"
  },
  metadata: {
    id: "JuvoLmXrENBEFUDWacVuOb",
    version: 0,
    txTime: 2019-09-05T18:16:50.542Z,
    txId: "DZ1zjlG5gGm6m3Jf9JJBhK"
  }
}

First, find Brent’s document id from the Person table with the following query:

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

Then, take the document id returned by your query and run the following statement. Be sure to replace the <<replace with your id>> value with your own document ID as appropriate:

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

Run the following query to confirm that you successfully updated the PrimaryOwner nested document:

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

Next, let’s add Alexis Pena as a secondary owner on the registration. First, find Alexa’s document id from the Person table’s committed view (_ql_committed_Person):

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

Then, take Alexis’ document id returned by your query and run the statement below. Be sure to replace the <<replace with your id>> value in the query statement below with your own document ID as appropriate:

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

Run the following query to confirm that you successfully appended an additional owner to the SecondaryOwners nested document:

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

Query a document’s history

After modifying registration data for the car with VIN 1N4AL11D75C109151, you can query the history of all its registered owners and any other updated fields using the history() function. The query also returns metadata that shows exactly when each revision was made, in what order, and which transaction committed them.

To query the history of a document, start by finding its unique id.

SELECT metadata.id FROM _ql_committed_VehicleRegistration AS r WHERE r.data.VIN = '1N4AL11D75C109151'

Then, take the document id returned by your query and run the statement below. Be sure to replace the <<replace with your id>> value with your own document ID as appropriate:

SELECT * FROM history(VehicleRegistration) AS h
WHERE h.metadata.id = '<<replace with your id>>'

Your query results contain all versions of the document and should look similar to the following:

blockAddress hash data metadata
{strandId:“Ad3A17n mNIGLFxMhY7DDTl “,sequenceNo:0} {{fwx9Sy3PABxQHo H0TnTM/GfOEtpJXm 7AdZC2kwjaO8g=}} {VIN:“1N4AL11D75C109151”,LicensePlateNumber:” LEWISR261LL”,State:“WA”,City:“Seattle”,PendingPenaltyTicketAmount:90.25,ValidFromDate:2017-08-21T,ValidToDate:2020-05-11T,Owners:{PrimaryOwner:{PersonId:“BqZVK31T1z OI2sz2vtgeJc”},SecondaryOwners:[]}} {id:“6mW3XIkmw2Z 6HMzhkdOy7S”,vers ion:0,txTime:2019- 10- 28T16:37:47.903Z,t xId:“HLF4iYIVYNc5E GBH8EeZAN”}
{strandId:“Ad3A17n mNIGLFxMhY7DDTl “,sequenceNo:13} {{wpsGTBAq8qKXpeT RBgUbmYoDkSiAj6ep XXQ8eB7tIDk=}} {{wpsGTBAq8qKXpeT RBgUbmYoDkSiAj6ep XXQ8eB7tIDk=}}{VIN:“1N4AL11D75C109151”,LicensePlateNumber:” LEWISR261LL”,State:“WA”,PendingPenaltyTicketAmount:90.25,ValidFromDate:2017-08-21T,ValidToDate:2020-05- 11T,Owners:{PrimaryOwner:{PersonId:“3Qv6BaVYn2 1497hc76kuKn”},SecondaryOwners:[]},City:“Everett” } {id:“6mW3XIkmw2Z 6HMzhkdOy7S”,vers ion:1,txTime:2019-10-28T16:48:26.456Z,txId:“J3hZ9aKfPRw88 oogqevlWc”}
{strandId:“Ad3A17n mNIGLFxMhY7DDTl “,sequenceNo:25} {{QKS4W1VF2gcdlgs 46koDJcFROR0gmjS kYzrnN9b32XA=}} {VIN:“1N4AL11D75C109151”,LicensePlateNumber:“LEWISR261LL”,State:“WA”,PendingPenaltyTicketAmount:90.25,ValidFromDate:2017-08-21T,ValidToDate:2020-05-11T,Owners:{PrimaryOwner:{PersonId:“3Qv6BaVYn2 1497hc76kuKn”},SecondaryOwners:[{PersonId:“1yQ gJbt7x8qJ0m1Nmtfgjy”}]},City:“Everett”} {id:“6mW3XIkmw2Z 6HMzhkdOy7S”,vers ion:2,txTime:2019-10-28T16:50:32.981Z,t xId:“Gucx6nIzfNaDa 4M9tPtxNi”}

For more information about using the history() function, see the documentation.