SELECT command in Amazon QLDB - Amazon Quantum Ledger Database (Amazon QLDB)
SyntaxParametersJoinsNested query limitationsExamplesRunning programmatically using the driver

SELECT command in Amazon QLDB

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

The order of the results is not specific and can vary for each SELECT query. You shouldn't rely on the results order for any query in QLDB.

To learn how to control access to run this PartiQL command on specific tables, see Getting started with the standard permissions mode in Amazon QLDB.

Warning

When you run a query in QLDB without an indexed lookup, it invokes a full table scan. PartiQL supports such queries because it's SQL compatible. However, don't run table scans for production use cases in QLDB. Table scans 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.

Syntax

SELECT [ VALUE ] expression [ AS field_alias ] [, expression, ... ]
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.

FROM 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 (ordinal) number of each element within a list from the source. 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.

WHERE 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 that is nested within VehicleRegistration, not a different collection named Owners (if one existed). For more context, see Nested data in the chapter Working with data and history.

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 (ordinal) 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: