celo.enriched.events

celo.enriched.events (referred to as Events going forward) is a derived table from celo.core.logs. Everytime a smart contract emits an event, it is stored in Logs with index param stored as topics and non-indexed params stored as data. Topics and Data are 64-bit hex strings. It is generally tedious to parse these 64-bit hex strings into human readable format.

In order to make it easier for users to query events, we parse the logs and store them in Events table. We parse the logs using the ABI of the verified contracts. We use the ABI to parse the topics and data into human readable format. We also store the contract name and event name in the table. This makes it easier for users to query events. Parsed params are stored in parsed_args column.

parsed_args is a dictionary in the following format {arg_name: arg_value...}

Check if a contract is parsed in Events

Run the following example query to check if a contract is parsed in Events table.

SELECT *
FROM celo.enriched.events
-- Update the contract address below. It is currently set to ExchangeProxy
WHERE events.contract_address=lower('0x67316300f17f063085Ca8bCa4bd3f7a5a3C66275')
LIMIT 10

Note: contract_address is the address of the contract and is required to be in lowercase. In case a contract uses Proxy pattern, you can use the Proxy address to check if the contract is parsed.

Find all parsed events of a given contract

Run the following example query to find all the events that are parsed for a given contract.

SELECT DISTINCT event_name
FROM celo.enriched.events
-- Update the contract name below. It is currently set to ExchangeProxy
WHERE events.contract_name='ExchangeProxy'

parsed_args for a given event

parsed_args is a dictionary in the following format {arg_name: arg_value...}.

Run the following example query to find all the parsed_args for a given event.

SELECT events.parsed_args
FROM celo.enriched.events
-- Update the contract name below. It is currently set to ExchangeProxy
WHERE events.contract_name='ExchangeProxy'
  -- Update the event name below. It is currently set to Exchanged
  AND events.event_name='Exchanged'
LIMIT 5

Sample output:

{"buyAmount":200000000000000000000,"exchanger":"0x4c9c42dcc8062528d87266be769bc21d9a26bdcc", "sellAmount":165964366225814079450,"soldGold":false}
{"buyAmount":200000000000000000000,"exchanger":"0x4c9c42dcc8062528d87266be769bc21d9a26bdcc","sellAmount":165993532940305229378,"soldGold":false}
{"buyAmount":200000000000000000000,"exchanger":"0x4c9c42dcc8062528d87266be769bc21d9a26bdcc","sellAmount":166022707340936956089,"soldGold":false}
{"buyAmount":200000000000000000000,"exchanger":"0x4c9c42dcc8062528d87266be769bc21d9a26bdcc","sellAmount":166051889430409580329,"soldGold":false}
{"buyAmount":0,"exchanger":"0xa7704a73583f234f56a1e9fb4b117e7a222f1940","sellAmount":1,"soldGold":true}
{"buyAmount":195316914635994314,"exchanger":"0xa7cbd42ee1d9832ac6d15c04be4e88340dd3951f","sellAmount":236003762017476498,"soldGold":true}

JSON Operations on parsed_args

You can use Trino’s JSON functions and operators to extract the relevant parsed fields.

Run the following example query to extract the exchanger from parsed_args.

-- Get all exchangers from Exchanged event of ExchangeProxy
SELECT JSON_EXTRACT_SCALAR(e.parsed_args, '$.exchanger') as exchanger
FROM celo.enriched.events e
WHERE e.contract_name='ExchangeProxy'
  AND e.event_name='Exchanged'
LIMIT 10

JSON_EXTRACT_SCALAR takes JSON and JSON_PATH of the key that needs to be extracted. It returns the value of the key as a string. JSON_PATH is a string that is a path to the key in the JSON. It is a dot separated string. For example, if you want to extract the value of exchanger from the above parsed_args, the JSON_PATH is $.exchanger. $. is the root of the JSON and exchanger is the key.

Extracting a field into a certain datatype

You can extract a field into a certain datatype using CAST function. If you would like to extract soldGold into a boolean, you can use the following query.

-- Get all soldGold from Exchanged event of ExchangeProxy
SELECT CAST(JSON_EXTRACT_SCALAR(e.parsed_args, '$.soldGold') AS boolean) as soldGold
FROM celo.enriched.events e
WHERE e.contract_name='ExchangeProxy'
  AND e.event_name='Exchanged'
LIMIT 10

Extracting a field into a certain datatype using Macros

We created Macros to make it easier to extract a field into a certain datatype. Type json and hit ctrl + space to see the available Macros. It will show the following Macros.

jsonExtractString
jsonExtractType

jsonExtractString Macro: After selecting this macro, it will autofill with the Macro json_extract_scalar(parsed_args, '$.key'). Update key to the desired key.

jsonExtractType Macro: After selecting this macro, it will autofill with the Macro CAST(json_extract_scalar(parsed_args, '$.key') AS decimal). Update key to the desired key and the datatype to desired data type.

Missing a contract in Events table?

If your desired smart contract is not available in Events table, please submit the request here