Querying document metadata

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.

An INSERT statement creates the initial revision of a document with a version number of zero. To uniquely identify each document, Amazon QLDB assigns a document ID as part of the metadata.

In addition to the document ID and version number, QLDB stores other system-generated metadata for each document in a table. This metadata includes transaction information, journal attributes, and the document's hash value.

All system-assigned IDs are universally unique identifiers (UUID) that are each represented in a Base62-encoded string. For more information, see Unique IDs in Amazon QLDB.

Committed view

You can access document metadata by querying the committed view. This view returns documents from the system-defined table that directly corresponds to your user table. It includes the latest committed, non-deleted revision of both your data and the system-generated metadata. To query this view, add the prefix _ql_committed_ to the table name in your query. (The prefix _ql_ is reserved in QLDB for system objects.)

SELECT * FROM _ql_committed_VehicleRegistration AS r
WHERE r.data.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

Using the data previously inserted in Creating tables with indexes and inserting documents, the output of this query shows the system contents of each non-deleted document's latest revision. The system document has metadata nested in the metadata field, and your user data nested in the data field.

{
    blockAddress:{
        strandId:"JdxjkR9bSYB5jMHWcI464T",
        sequenceNo:14
    },
    hash:{{wCsmM6qD4STxz0WYmE+47nZvWtcCz9D6zNtCiM5GoWg=}},
    data:{
        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" }]
        }
    },
    metadata:{
        id:"3Qv67yjXEwB9SjmvkuG6Cp",
        version:0,
        txTime:2019-06-05T20:53:321d-3Z,
        txId:"HgXAkLjAtV0HQ4lNYdzX60"
    }
},
{
    blockAddress:{
        strandId:"JdxjkR9bSYB5jMHWcI464T",
        sequenceNo:14
    },
    hash:{{wPuwH60TtcCvg/23BFp+redRXuCALkbDihkEvCX22Jk=}},
    data:{
        VIN: "KM8SRDHF6EU074761",
        LicensePlateNumber: "CA762X",
        State: "WA",
        City: "Kent",
        PendingPenaltyTicketAmount: 130.75,
        ValidFromDate: 2017-09-14T,
        ValidToDate: 2020-06-25T,
        Owners: {
            PrimaryOwner: { PersonId: "IN7MvYtUjkp1GMZu0F6CG9" },
            SecondaryOwners: []
        }
    },
    metadata:{
        id:"JOzfB3lWqGU727mpPeWyxg",
        version:0,
        txTime:2019-06-05T20:53:321d-3Z,
        txId:"HgXAkLjAtV0HQ4lNYdzX60"
    }
}

Committed view fields

• blockAddress – The location of the block in your ledger's journal where the document revision was committed. An address, which can be used for cryptographic verification, has the following two fields.

  • strandId – The unique ID of the journal strand that contains the block.

  • sequenceNo – An index number that specifies the location of the block within the strand.

  Note

  Both documents in this example have an identical blockAddress with the same sequenceNo. Because these documents were inserted within a single transaction (and in this case, in a single statement), they were committed in the same block.

• hash – The SHA-256 Ion hash value that uniquely represents the document revision. The hash covers the revision's data and metadata fields and can be used for cryptographic verification.

• data – The document's user data attributes.

  If you redact a revision, this data structure is replaced by a dataHash field, whose value is the Ion hash of the removed data structure.

• metadata – The document's metadata attributes.

  • id – The system-assigned unique ID of the document.

  • version – The version number of the document. This is a zero-based integer that increments with each document revision.

  • txTime – The timestamp when the document revision was committed to the journal.

  • txId – The unique ID of the transaction that committed the document revision.

Joining the committed and user views

You can write queries that join a table in the committed view with a table in the user view. For example, you might want to join the document id of one table with a user-defined field of another table.

The following query joins two tables named DriversLicense and Person on their PersonId and document id fields respectively, using the committed view for the latter.

SELECT * FROM DriversLicense AS d INNER JOIN _ql_committed_Person AS p
ON d.PersonId = p.metadata.id
WHERE p.metadata.id = '1CWScY2qHYI9G88C2SjvtH'

To learn how to query the document ID field in the default user view, proceed to Using the BY clause to query document ID.