Basic queries Projections and filters Joins Nested data

Querying your data

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.

The user view returns the latest non-deleted revision of your user data only. This is the default view in Amazon QLDB. This means that no special qualifiers are needed when you want to query only your data.

For details on the syntax and parameters of the following query examples, see SELECT in the Amazon QLDB PartiQL reference.

Basic queries

Basic SELECT queries return the documents that you inserted into the table.

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.

The following queries show results for the vehicle registration documents that you previously inserted in Creating tables with indexes and inserting documents. 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.


SELECT * FROM VehicleRegistration
WHERE LicensePlateNumber IN ('LEWISR261LL', 'CA762X')


{
    VIN: "1N4AL11D75C109151",
    LicensePlateNumber: "LEWISR261LL",
    State: "WA",
    City: "Seattle",
    PendingPenaltyTicketAmount: 90.25,
    ValidFromDate: 2017-08-21T,
    ValidToDate: 2020-05-11T,
    Owners: {
        PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" },
        SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }]
    }
},
{
    VIN: "KM8SRDHF6EU074761",
    LicensePlateNumber: "CA762X",
    State: "WA",
    City: "Kent",
    PendingPenaltyTicketAmount: 130.75,
    ValidFromDate: 2017-09-14T,
    ValidToDate: 2020-06-25T,
    Owners: {
        PrimaryOwner: { PersonId: "IN7MvYtUjkp1GMZu0F6CG9" },
        SecondaryOwners: []
    }
}


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


{
    VIN: "1N4AL11D75C109151",
    Type: "Sedan",
    Year: 2011,
    Make: "Audi",
    Model: "A5",
    Color: "Silver"
},
{
    VIN: "KM8SRDHF6EU074761",
    Type: "Sedan",
    Year: 2015,
    Make: "Tesla",
    Model: "Model S",
    Color: "Blue"
}

Important

In PartiQL, you use single quotation marks to denote strings in data manipulation language (DML) or query statements. But the QLDB console and the QLDB shell return query results in Amazon Ion text format, so you see strings enclosed in double quotation marks.

This syntax allows the PartiQL query language to maintain SQL compatibility, and the Amazon Ion text format to maintain JSON compatibility.

Projections and filters

You can do projections (targeted SELECT) and other standard filters (WHERE clauses). The following query returns a subset of document fields from the VehicleRegistration table. It filters for vehicles with the following criteria:

String filter – It's registered in Seattle.
Decimal filter – It has a pending penalty ticket amount less than 100.0.
Date filter – It has a registration date that is valid on or after September 4, 2019.


SELECT r.VIN, r.PendingPenaltyTicketAmount, r.Owners
FROM VehicleRegistration AS r
WHERE r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')
AND r.City = 'Seattle' --string
AND r.PendingPenaltyTicketAmount < 100.0 --decimal
AND r.ValidToDate >= `2019-09-04T` --timestamp with day precision


{
    VIN: "1N4AL11D75C109151",
    PendingPenaltyTicketAmount: 90.25,
    Owners: {
        PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" },
        SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }]
    }
}

Joins

You can also write inner join queries. The following example shows an implicit inner join query that returns all registration documents along with attributes of the registered vehicles.


SELECT * FROM VehicleRegistration AS r, Vehicle AS v
WHERE r.VIN = v.VIN
AND r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')


{
    VIN: "1N4AL11D75C109151",
    LicensePlateNumber: "LEWISR261LL",
    State: "WA",
    City: "Seattle",
    PendingPenaltyTicketAmount: 90.25,
    ValidFromDate: 2017-08-21T,
    ValidToDate: 2020-05-11T,
    Owners: {
        PrimaryOwner: { PersonId: "294jJ3YUoH1IEEm8GSabOs" },
        SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }]
    },
    Type: "Sedan",
    Year: 2011,
    Make: "Audi",
    Model: "A5",
    Color: "Silver"
},
{
    VIN: "KM8SRDHF6EU074761",
    LicensePlateNumber: "CA762X",
    State: "WA",
    City: "Kent",
    PendingPenaltyTicketAmount: 130.75,
    ValidFromDate: 2017-09-14T,
    ValidToDate: 2020-06-25T,
    Owners: {
        PrimaryOwner: { PersonId: "IN7MvYtUjkp1GMZu0F6CG9" },
        SecondaryOwners: []
    },
    Type: "Sedan",
    Year: 2015,
    Make: "Tesla",
    Model: "Model S",
    Color: "Blue"
}

Or, you can write the same inner join query in the explicit syntax as follows.


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

Nested data

You can use PartiQL in QLDB to query nested data in documents. The following example shows a correlated subquery that flattens nested data. 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')


{
    VIN: "1N4AL11D75C109151",
    SecondaryOwners: [{ PersonId: "5Ufgdlnj06gF5CWcOIu64s" }]
},
{
    VIN: "KM8SRDHF6EU074761",
    SecondaryOwners: []
}

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


{
    Make: "Audi",
    Model: "A5",
    PrimaryOwner: ["294jJ3YUoH1IEEm8GSabOs"]
},
{
    Make: "Tesla",
    Model: "Model S",
    PrimaryOwner: ["IN7MvYtUjkp1GMZu0F6CG9"]
}

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


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

{
    PersonId: "5Ufgdlnj06gF5CWcOIu64s",
    owner_idx: 0
}

To learn how to query your document metadata, proceed to Querying document metadata.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Creating tables with indexes and inserting documents

Querying document metadata