SELECT - Amazon Quantum Ledger Database (Amazon QLDB)

SELECT

Use the SELECT statement to retrieve data from one or more tables in Amazon QLDB. Every SELECT query in QLDB is processed in a transaction and is subject to a transaction timeout limit.

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 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 in the Working with data and history topic.

Syntax

SELECT [ VALUE ] expression [ AS field_alias ] [, ...] FROM source [ AS source_alias ] [ AT idx_alias ] [ BY id_alias ] [, source, ...] [ WHERE condition ]

Parameters

VALUE

A qualifier for your expression that makes the query return the raw data type value, rather than the value being wrapped in a tuple structure.

expression

A projection formed from the * wildcard or a projection list of one or more document fields from the result set. An expression can consist of calls to PartiQL functions or fields that are modified by PartiQL operators.

AS field_alias

(Optional) A temporary, user-defined alias for the field that is used in the final result set. The AS keyword is optional.

If you don't specify an alias for an expression that isn't a simple field name, the result set applies a default name to that field.

source

A source to be queried. The only sources currently supported are table names, inner joins between tables, nested SELECT queries (subject to Nested query limitations), and history function calls for a table.

You must specify at least one source. Multiple sources must be separated by commas.

AS source_alias

(Optional) A user-defined alias that ranges over a source to be queried. All source aliases that are used in the SELECT OR WHERE clause must be declared in the FROM clause. The AS keyword is optional.

AT idx_alias

(Optional) A user-defined alias that binds to the index number of an element within a list structure. In other words, this specifies the position of an element within a list. The alias must be declared in the FROM clause using the AT keyword.

BY id_alias

(Optional) A user-defined alias that binds to the id metadata field of each document in the result set. The alias must be declared in the FROM clause using the BY keyword. This is useful when you want to project or filter on the document ID while querying the default user view. For more information, see Using the BY clause to query document ID.

condition

The selection criteria and join criteria (if applicable) for the query.

Note

If you omit the WHERE clause, then all of the documents in the table are retrieved.

Joins

Only inner joins are currently supported. You can write inner join queries using the explicit INNER JOIN clause, as follows. In this syntax, JOIN must be paired with ON, and the INNER keyword is optional.

SELECT expression FROM table1 AS t1 [ INNER ] JOIN table2 AS t2 ON t1.element = t2.element

Or, you can write inner joins using the implicit syntax, as follows.

SELECT expression FROM table1 AS t1, table2 AS t2 WHERE t1.element = t2.element

Nested query limitations

You can write nested queries (subqueries) within SELECT expressions and within FROM sources. The main restriction is that only the outermost query can access the global database environment. For example, suppose that you have a ledger with tables VehicleRegistration and Person. The following nested query is not valid because the inner SELECT tries to access Person.

SELECT r.VIN, (SELECT p.PersonId FROM Person AS p WHERE p.PersonId = r.Owners.PrimaryOwner.PersonId) AS PrimaryOwner FROM VehicleRegistration AS r

Whereas the following nested query is valid.

SELECT r.VIN, (SELECT o.PrimaryOwner.PersonId FROM @r.Owners AS o) AS PrimaryOwner FROM VehicleRegistration AS r

Examples

The following query shows a basic SELECT all wildcard with a standard WHERE predicate clause that uses the IN operator.

SELECT * FROM Vehicle WHERE VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

The following shows SELECT projections with a string filter.

SELECT FirstName, LastName, Address FROM Person WHERE Address LIKE '%Seattle%' AND GovId = 'LEWISR261LL'

The following shows a correlated subquery that flattens nested data. Note that 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')

The following shows a subquery in the SELECT list that projects nested data, and an implicit 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')

The following shows an explicit inner join.

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

The following shows a projection of the document id metadata field, using the BY clause.

SELECT r_id, r.VIN FROM VehicleRegistration AS r BY r_id WHERE r_id = 'documentId'

The following uses the BY clause to join the DriversLicense and Person tables on their PersonId and document id fields respectively.

SELECT * FROM DriversLicense AS d INNER JOIN Person AS p BY pid ON d.PersonId = pid WHERE pid = 'documentId'

The following uses the Committed view to join the DriversLicense and Person tables on their PersonId and document id fields respectively.

SELECT * FROM DriversLicense AS d INNER JOIN _ql_committed_Person AS cp ON d.PersonId = cp.metadata.id WHERE cp.metadata.id = 'documentId'

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

SELECT s.PersonId, owner_idx FROM VehicleRegistration AS r, @r.Owners.SecondaryOwners AS s AT owner_idx WHERE r.VIN = 'KM8SRDHF6EU074761'

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: