Amazon QLDB documents

Amazon QLDB stores data records as documents, which are just Amazon Ion struct objects that are inserted into a table. For the Ion specification, see the Amazon Ion GitHub site.

Ion document structure

Like JSON, QLDB documents are composed of name-value pairs in the following structure.

{
  name1: value1,
  name2: value2,
  name3: value3,
  ...
  nameN: valueN
}

The names are symbol tokens, and the values are unrestricted. Each name-value pair is called a field. The value of a field can be any of the Ion Data types, including container types: nested structures, lists, and lists of structures.

Also like JSON, a struct is denoted by curly braces ( {...} ), and a list is denoted by square brackets ( [...] ). The following example is a document from the sample data in Getting started with the Amazon QLDB console that contains values of various types.

{
    VIN: "1N4AL11D75C109151",
    LicensePlateNumber: "LEWISR261LL",
    State: "WA",
    City: "Seattle",
    PendingPenaltyTicketAmount: 90.25,
    ValidFrom: 2017-08-21T,
    ValidTo: 2020-05-11T,
    Owners: {
        PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" },
        SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }]
    }
}

Important

In Ion, double quotation marks denote string values, and unquoted symbols represent field names. But in PartiQL, single quotation marks denote both strings and field names.

This difference in syntax allows the PartiQL query language to maintain SQL compatibility, and the Amazon Ion data format to maintain JSON compatibility. For details on the syntax and semantics of PartiQL in QLDB, see Querying Ion with PartiQL.

PartiQL-Ion type mapping

In QLDB, PartiQL extends SQL's type system to cover the Ion data model. This mapping is described as follows:

• SQL scalar types are covered by their Ion counterparts. For example:

  • CHAR and VARCHAR are Unicode sequences that map to the Ion string type.

  • NUMBER maps to the Ion decimal type.

• Ion's struct type is equivalent to a SQL tuple, which traditionally represents a table row.

  • However, with open content and without schema, queries that rely on the ordered nature of a SQL tuple are not supported (such as the output order of SELECT *).

• In addition to NULL, PartiQL has a MISSING type. This is a specialization of NULL and indicates the lack of a field. This type is necessary because Ion struct fields might be sparse.

Document ID

QLDB assigns a document ID to each document that you insert into a table. All system-assigned IDs are universally unique identifiers (UUID) that are each represented in a Base62-encoded string (for example, 3Qv67yjXEwB9SjmvkuG6Cp). For more information, see Unique IDs in Amazon QLDB.

Each document revision is uniquely identified by a combination of the document ID and a zero-based version number.

The document ID and version fields are included in the document's metadata, which you can query in the committed view (the system-defined view of a table). For more information about views in QLDB, see Core concepts. To learn more about metadata, see Querying document metadata.