Querying your data - Amazon Quantum Ledger Database (Amazon QLDB)

Querying your data

The user view returns the latest non-deleted revision of your user data only. This is the default view in Amazon QLDB. This means that no special qualifiers are needed when you want to query only your data.

For details on the syntax and parameters of the following query examples, see SELECT in the Amazon QLDB PartiQL reference.

Basic queries

Basic SELECT queries return the documents that you inserted into the table.

Warning

When you run a query in QLDB without an indexed lookup, it invokes a full table scan. PartiQL is SQL compatible, so it supports such queries. However, do not run table scans for production use cases in QLDB, as they can cause performance problems on large tables, including concurrency conflicts and transaction timeouts.

To avoid table scans, you must run statements with a WHERE predicate clause using an equality operator on an indexed field or a document ID; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789). For more information, see Optimizing query performance.

The following queries show results for the vehicle registration documents that you previously inserted in Creating tables with indexes and inserting documents. The order of the results is not specific and can vary for each SELECT query. You should not rely on the results order for any query in QLDB.

SELECT * FROM VehicleRegistration WHERE LicensePlateNumber IN ('LEWISR261LL', 'CA762X')
{ VIN: "1N4AL11D75C109151", LicensePlateNumber: "LEWISR261LL", State: "WA", City: "Seattle", PendingPenaltyTicketAmount: 90.25, ValidFromDate: 2017-08-21T, ValidToDate: 2020-05-11T, Owners: { PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" }, SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }] } }, { VIN: "KM8SRDHF6EU074761", LicensePlateNumber: "CA762X", State: "WA", City: "Kent", PendingPenaltyTicketAmount: 130.75, ValidFromDate: 2017-09-14T, ValidToDate: 2020-06-25T, Owners: { PrimaryOwner: { PersonId: "IN7MvYtUjkp1GMZu0F6CG9" }, SecondaryOwners: [] } }
SELECT * FROM Vehicle WHERE VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')
{ VIN: "1N4AL11D75C109151", Type: "Sedan", Year: 2011, Make: "Audi", Model: "A5", Color: "Silver" }, { VIN: "KM8SRDHF6EU074761", Type: "Sedan", Year: 2015, Make: "Tesla", Model: "Model S", Color: "Blue" }
Important

In PartiQL, you use single quotation marks to denote strings in data manipulation language (DML) or query statements. But the QLDB console returns query results in Amazon Ion format, so you see strings enclosed in double quotation marks.

This syntax allows the PartiQL query language to maintain SQL compatibility, and the Amazon Ion data format to maintain JSON compatibility.

Projections and filters

You can do projections (targeted SELECT) and other standard filters (WHERE clauses). The following query returns a subset of document fields from the VehicleRegistration table. It filters for vehicles with the following criteria:

  • String filter – It is registered in Seattle.

  • Decimal filter – It has a pending penalty ticket amount less than 100.0.

  • Date filter – It has a registration date that is valid on or after September 4, 2019.

SELECT r.VIN, r.PendingPenaltyTicketAmount, r.Owners FROM VehicleRegistration AS r WHERE r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761') AND r.City = 'Seattle' --string AND r.PendingPenaltyTicketAmount < 100.0 --decimal AND r.ValidToDate >= `2019-09-04T` --timestamp with day precision
{ VIN: "1N4AL11D75C109151", PendingPenaltyTicketAmount: 90.25, Owners: { PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" }, SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }] } }

Joins

You can also write inner join queries. The following example shows an implicit inner join query that returns all registration documents along with attributes of the registered vehicles.

SELECT * FROM VehicleRegistration AS r, Vehicle AS v WHERE r.VIN = v.VIN AND r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')
{ VIN: "1N4AL11D75C109151", LicensePlateNumber: "LEWISR261LL", State: "WA", City: "Seattle", PendingPenaltyTicketAmount: 90.25, ValidFromDate: 2017-08-21T, ValidToDate: 2020-05-11T, Owners: { PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" }, SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }] }, Type: "Sedan", Year: 2011, Make: "Audi", Model: "A5", Color: "Silver" }, { VIN: "KM8SRDHF6EU074761", LicensePlateNumber: "CA762X", State: "WA", City: "Kent", PendingPenaltyTicketAmount: 130.75, ValidFromDate: 2017-09-14T, ValidToDate: 2020-06-25T, Owners: { PrimaryOwner: { PersonId: "IN7MvYtUjkp1GMZu0F6CG9" }, SecondaryOwners: [] }, Type: "Sedan", Year: 2015, Make: "Tesla", Model: "Model S", Color: "Blue" }

Or, you can write the same inner join query in the explicit syntax as follows.

SELECT * FROM VehicleRegistration AS r INNER JOIN Vehicle AS v ON r.VIN = v.VIN WHERE r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

Nested data

PartiQL has Amazon Ion extensions that enable you to query nested data in documents. The following example shows a correlated subquery that flattens nested data. The @ character is technically optional here. But it explicitly indicates that you want the Owners structure within VehicleRegistration, not a different collection named Owners (if one existed).

SELECT r.VIN, o.SecondaryOwners FROM VehicleRegistration AS r, @r.Owners AS o WHERE r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')
{ VIN: "1N4AL11D75C109151", SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }] }, { VIN: "KM8SRDHF6EU074761", SecondaryOwners: [] }

The following shows a subquery in the SELECT list that projects nested data, in additional to an inner join.

SELECT v.Make, v.Model, (SELECT VALUE o.PrimaryOwner.PersonId FROM @r.Owners AS o) AS PrimaryOwner FROM VehicleRegistration AS r, Vehicle AS v WHERE r.VIN = v.VIN AND r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')
{ Make: "Audi", Model: "A5", PrimaryOwner: ["294jJ3YUoH1IEEm8GSabOs"] }, { Make: "Tesla", Model: "Model S", PrimaryOwner: ["IN7MvYtUjkp1GMZu0F6CG9"] }

The following query returns the PersonId and index (ordinal) number of each person in the Owners.SecondaryOwners list for a VehicleRegistration document.

SELECT s.PersonId, owner_idx FROM VehicleRegistration AS r, @r.Owners.SecondaryOwners AS s AT owner_idx WHERE r.VIN = '1N4AL11D75C109151'
{
    PersonId: "5Ufgdlnj06gF5CWcOIu64s",
    owner_idx: 0
}

To learn how to query your document metadata, proceed to Querying document metadata.