can we retrieve document revision by transaction hash in qldb

Question

we're trying to retrieve commited document revision in qldb with its transaction hash. however it is not returning a row that has particular hash while trying below query

SELECT * FROM history(Users) AS h WHERE h.hash='8kmIsF2X2HonQDhuoosBbKZtSQCjHZgnyUmPGZa9pJc='

I believe that tx hash in qldb revision is ion literal so it should not be treated as string. so how can we retreive document revision by hash

Answer 1

To execute this query successfully you'll need to treat the hash value as a blob type Ion literal, which can be done using backticks and{{...}} :

SELECT * FROM history(Users) AS h WHERE h.hash = `{{8kmIsF2X2HonQDhuoosBbKZtSQCjHZgnyUmPGZa9pJc=}}`

Reference: https://docs.aws.amazon.com/qldb/latest/developerguide/ql-reference.query.html#ql-reference.query.backtick

It is worth noting that this query scans every revision for all documents in the Users table. QLDB's history does not support indexes and this query will degrade as the table grows.