Querying Ion with PartiQL - Amazon Quantum Ledger Database (Amazon QLDB)

Querying Ion with PartiQL

When you use the AWS Management Console to query data in Amazon QLDB, you write statements in PartiQL, but results are shown in Amazon Ion. PartiQL is intended to be SQL-compatible, whereas Ion is an extension of JSON. This leads to syntactic differences with how you notate data in your queries, compared to how the QLDB console presents your query results.

This section describes PartiQL semantics for running manual statements using the Query editor on the console or the QLDB shell command line interface.

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 tutorials in Getting started with the driver. For example, see CRUD operations in the Java Cookbook reference, or Step 3: Create tables, indexes, and sample data in the Java tutorial.

Syntax and semantics

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

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 in bare text without any quotes.

In PartiQL, double quotation marks ("...") denote a quoted PartiQL identifier. Unquoted text represents a regular PartiQL identifier, such as a table name.

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 is 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 cannot 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': '1nmeDdLo3AhGswBtyM1eYh' } ] }, '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 are not 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 are 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.