Querying Ion with PartiQL in Amazon QLDB

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

When you query data in Amazon QLDB, you write statements in PartiQL format, but QLDB returns results in Amazon Ion format. PartiQL is intended to be SQL-compatible, whereas Ion is an extension of JSON. This leads to syntactic differences between how you notate data in your query statements compared to how your query results are displayed.

This section describes basic syntax and semantics for running PartiQL statements manually by using the QLDB console or the QLDB shell.

Tip

When you run PartiQL queries programmatically, the best practice is to use parameterized statements. You can use a question mark (?) as a bind variable placeholder in your statements to avoid these syntax rules. This is also more secure and efficient.

To learn more, see the following tutorials in Getting started with the driver:

• Java: Quick start tutorial | Cookbook reference

• .NET: Quick start tutorial | Cookbook reference

• Go: Quick start tutorial | Cookbook reference

• Node.js: Quick start tutorial | Cookbook reference

• Python: Quick start tutorial | Cookbook reference

Syntax and semantics

When using the QLDB console or the QLDB shell to query Ion data, the following are the fundamental syntax and semantics of PartiQL:

Case sensitivity

All QLDB system object names—including field names, table names, and ledger names—are case sensitive.

String values

In Ion, double quotation marks ("...") denote a string.

In PartiQL, single quotation marks ('...') denote a string.

Symbols and identifiers

In Ion, single quotation marks ('...') denote a symbol. A subset of symbols in Ion called identifiers are represented by unquoted text.

In PartiQL, double quotation marks ("...") denote a quoted PartiQL identifier, such as a reserved word that is used as a table name. Unquoted text represents a regular PartiQL identifier, such as a table name that isn't a reserved word.

Ion literals

Any Ion literals can be denoted with backticks (`...`) in a PartiQL statement.

Field names

Ion field names are case-sensitive symbols. PartiQL lets you denote field names with single quotation marks in a DML statement. This is a shorthand alternative to using PartiQL's cast function to define a symbol. It's also more intuitive than using backticks to denote a literal Ion symbol.

Literals

Literals of the PartiQL query language correspond to the Ion data types, as follows:

Scalars

Follow the SQL syntax when applicable, as described in PartiQL-Ion type mapping section. For example:

• 5

• 'foo'

• null

Structs

Also known as tuples or objects in many formats and other data models.

Denoted by curly braces ( {...} ) with struct elements separated by commas.

• { 'id' : 3, 'arr': [1, 2] }

Lists

Also known as arrays.

Denoted by square brackets ( [...] ) with list elements separated by commas.

• [ 1, 'foo' ]

Bags

Unordered collections in PartiQL.

Denoted by double angle brackets ( <<...>> ) with bag elements separated by commas. In QLDB, a table can be thought of as a bag. However, a bag can't be nested within documents in a table.

• << 1, 'foo' >>

Example

The following is an example of the syntax for an INSERT statement with various Ion types.

INSERT INTO VehicleRegistration VALUE
{
    'VIN' : 'KM8SRDHF6EU074761', --string
    'RegNum' : 1722, --integer
    'State' : 'WA',
    'City' : 'Kent',
    'PendingPenaltyTicketAmount' : 130.75, --decimal
    'Owners' : { --nested struct
        'PrimaryOwner' : { 'PersonId': '294jJ3YUoH1IEEm8GSabOs' },
        'SecondaryOwners' : [ --list of structs
            { 'PersonId' : '1nmeDdLo3AhGswBtyM1eYh' },
            { 'PersonId': 'IN7MvYtUjkp1GMZu0F6CG9' }
        ]
    },
    'ValidFromDate' : `2017-09-14T`, --Ion timestamp literal with day precision
    'ValidToDate' : `2020-06-25T`
}

Backtick notation

PartiQL fully covers all Ion data types, so you can write any statement without using backticks. But there are cases where this Ion literal syntax can make your statements clearer and more concise.

For example, to insert a document with Ion timestamp and symbol values, you can write the following statement using purely PartiQL syntax only.

INSERT INTO myTable VALUE
{
    'myTimestamp': to_timestamp('2019-09-04T'),
    'mySymbol': cast('foo' as symbol)
}

This is fairly verbose, so instead, you can use backticks to simplify your statement.

INSERT INTO myTable VALUE
{
    'myTimestamp': `2019-09-04T`,
    'mySymbol': `foo`
}

You can also enclose the entire structure in backticks to save a few more keystrokes.

INSERT INTO myTable VALUE
`{
    myTimestamp: 2019-09-04T,
    mySymbol: foo
}`

Important

Strings and symbols are different classes in PartiQL. This means that even if they have the same text, they aren't equal. For example, the following PartiQL expressions evaluate to different Ion values.

'foo'

`foo`

Path navigation

When writing data manipulation language (DML) or query statements, you can access fields within nested structures using path steps. PartiQL supports dot notation for accessing field names of a parent structure. The following example accesses the Model field of a parent Vehicle.

Vehicle.Model

To access a specific element of a list, you can use the square brackets operator to denote a zero-based ordinal number. The following example accesses the element of SecondaryOwners with an ordinal number of 2. In other words, this is the third element of the list.

SecondaryOwners[2]

Aliasing

QLDB supports open content and schema. So, when you're accessing particular fields in a statement, the best way to ensure that you get the results that you expect is to use aliases. For example, if you don't specify an explicit alias, the system generates an implicit one for your FROM sources.

SELECT VIN FROM Vehicle
--is rewritten to
SELECT Vehicle.VIN FROM Vehicle AS Vehicle

But the results are unpredictable for field name conflicts. If another field named VIN exists in a nested structure within the documents, the VIN values returned by this query might surprise you. As a best practice, write the following statement instead. This query declares v as an alias that ranges over the Vehicle table. The AS keyword is optional.

SELECT v.VIN FROM Vehicle [ AS ] v

Aliasing is particularly useful when pathing into nested collections within a document. For example, the following statement declares o as an alias that ranges over the collection VehicleRegistration.Owners.

SELECT o.SecondaryOwners
FROM VehicleRegistration AS r, @r.Owners AS o

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).

PartiQL specification

For more information about the PartiQL query language, see the PartiQL Specification.