Ethereum Query Guide

Select Query

All the transaction data will be stored in continuous growing list of records which are called as blocks. Block structure is divided into two tables- transaction and block.

The structure of these tables are as shown in tables 2.1 and 2.2.

Column Name	Column Description
blockhash	Hash of the block
blocknumber	Block where transaction is stored
creates
from	Source wallet address
gas	Gas provided by the sender
gasprice	Amount in ether per unit of gas
hash	Hash of the transaction
input	The data sent along with transaction
nonce	Numeric value to identify unique transactions
publickey
r	Value for the transaction signature
raw	Raw data
s	Value for the transaction signature
to	Destination wallet address
transactionindex	Index of transaction in a particular block
v	Value for the transaction signature
value	Value of the transaction

Table 2.1(transaction)

Column Name	Column Description
blocknumber	Height of the block in the ledger
hash	Hash of the current block
parenthash	Hash of the parent block
nonce	Hash of the generated proof of work
sha3uncles	SHA3 of the uncles data in the current block
logsbloom	Bloom filter value for the logs
transactionsroot	The root of the transaction trie
stateroot	The root of the final state trie
receiptsroot	The root of the receipt trie
author
miner	Miner’s wallet address
mixhash
totaldifficulty	Total difficulty till the current block
extradata
size	Size of the block
gaslimit	Maximum gas allowed for the current block
gasused	Total gas used by the transactions
timestamp	The time when block was made
transactions	List of transaction objects in the current block
uncles	List of uncles hashes
sealfields

Table 2.2(block)

Clauses supported with select query are :

• SELECT
• FROM
• WHERE
• GROUP BY
• ORDER BY
• HAVING
• LIMIT

There are 3 types of filter conditions in where clause:

• Range -> Operators like >,<,= etc. can be used (defined in bold in table 2.1 and 2.2)
• Direct query-> Only = operator can be used(defined in bold and italic in table 2.1 and 2.2)
• Other filters-> filtering on the columns which can’t be queried though directly by ethereum API’s

Note: Support for select query without where clause is not added in this release as it requires fetching huge amount of data. Also some other where conditions which requires fetching huge amount of data can’t be queried.

Examples:

1. select * from transaction where blocknumber=1652339;
2. select * from block where blocknumber >1652339 and blocknumber<1652345 order by hash;
3. select blocknumber, blockhash,to,value,gasprice from transaction where blocknumber >1652339 and blocknumber<1652345 and hash='0x99c7e80d394b3f1c7272706ab817959cefa50b05d6f3bebe0f089f741bcd6f28';
4. select gas as gp,count(blocknumber) as count from transaction where blocknumber=1652339 or blocknumber=1652340 group by gas having count(*) >1 order by count desc;
5. select * from block where blocknumber >1652339 and blocknumber<1652345 group by hash;

Insert Query

Transferring ethers from one wallet to other can be achieved through insert query command.

Insert query looks like as below:

insert into transaction (toAddress, value, unit, async) values ('a76cd046cf6089fe2adcf1680fcede500e44bacd', 0.01, 'ETHER', true)

Column Name	Column information
toAddress	Recipient wallet address.
value	Transaction value
unit	The unit of the transaction value like ETHER, WEI etc.
Supported units are	ETHER, WEI, KWEI, MWEI, GWEI, SZABO, FINNEY
async	Defines transaction processing type