Use Cases
Wallet queries
List all tokens in a wallet

4. List all tokens in a wallet

Some assets, particularly NFTs, may include additional information detailed in their metadata. For instance, in-game NFTs can have attributes such as power, speed, and strength encoded in this metadata. This information follows the standards set by various Cardano Improvement Proposals, such as CIP-25 (opens in a new tab), which define how metadata should be structured for assets on the Cardano blockchain.

💡

Metadata standards for Cardano are covered in CIPs (Cardano Improvement Proposals) 25 (opens in a new tab), 26 (opens in a new tab), 60 (opens in a new tab) and 68 (opens in a new tab)

To keep things simple, we will use the same address as before:

addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t

🔎 You can also verify the details on one of many Cardano Community Explorers listed here (opens in a new tab). For this one, lets use Cardanoscan (opens in a new tab) 🎯

To retrieve the metadata, you can use the following query:

SELECT
     ma.fingerprint,
    ma.name AS name,
    sum(mto.quantity) AS quantity
FROM
    ma_tx_out mto
JOIN multi_asset ma 
	ON ma.id = mto.ident 
JOIN tx_out 
	ON mto.tx_out_id = tx_out.id
LEFT JOIN tx_in ti 
	ON ti.tx_out_index = tx_out."index" AND ti.tx_out_id = tx_out.tx_id 
WHERE
    tx_out.address = 'addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t'
    AND ti.tx_out_id is null
GROUP BY ma.fingerprint, ma.name

Expected results format

     fingerprint                              |       name      |    quantity
----------------------------------------------+-----------------+--------------- 
asset1anpme6pcrc4jax2h230gm4pgmwsz80u27qsufq  | tBigTokenName03 |     20           
asset1etk8c8pscc0f6hy6q4wc3nphk9znj0xagv9acz  | tBigTokenName01 |     20                
asset1gvxdk7yhzupnruv9h54jc9eenf30w55xuq43vf  | tBigTokenName05 |     20         
asset1nvcwnq60jnm27efjm87xnhqt6alsv024tdyxjm  |     tDRIP       |  1,000,020
asset1qftk8hza6hn50lm2pl88wl8gea5fan5zhgx8gm  | tBigTokenName10 |     10
asset1rpsx8nx8gzmwt62tuzdwq66qj8jrdmsgjv6qsa  | tBigTokenName08 |     20
asset1t87nuhlan33l68hhtgmt3zfks3lwupayncz0k3  | tBigTokenName07 |     20
asset1tfpfjaz57vpcddwzxdz49f8pc7zmlswjfjfwwx  | tBigTokenName04 |     20
asset1uvkv85rk6pklfcdm39ht0yq60pf2fawtwndu2w  | tBigTokenName06 |     20
asset1vyddw8jc6w9679smf4d4d8r4qn7g2pzkqhgktd  | tBigTokenName02 |     20
asset1zutmprtn8te56av95m09sxj305rxm7yps3e3vr  | tBigTokenName09 |     20            
(11 rows)

At a high level, we are listing all the distinct tokens (fungible tokens) and NFTs (non-fungible tokens) present in our specific Cardano wallet address, along with their respective quantities. This is essential for wallet applications to display a user's asset holdings accurately. We are checking unspent Multi-Asset transaction output from the ma_tx_out (opens in a new tab) table for our specific address. 🔎 Let's break it down line by line:

SELECT the right information:

  • ma.fingerprint ,ie. the unique fingerprint of the token/NFT.
  • ma.name ...this is the name of the token/NFT (often used to distinguish NFTs).
  • sum(mto.quantity), ie. the total quantity of that specific token/NFT held in the wallet, calculated by summing quantities across UTXOs.

Table JOINs

  • ma_tx_out mto table stores information about multi-asset quantities within transaction outputs.
  • multi_asset ma table stores the metadata for each multi-asset.
  • The tx_out (opens in a new tab) table stores general transaction output information.
  • tx_in ti table stores transaction input information. We use this to check if a UTXO is spent.

Filters

  • tx_out.address = 'addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t'...we are filtering for our specific wallet address.
  • ti.tx_out_id IS NULL is an important condition ensuring we only consider unspent transaction outputs (UTXOs) since those are the ones containing the assets currently held in the wallet.

Grouping and Aggregating

  • GROUP BY ma.fingerprint, ma.name clause groups the results by the unique fingerprint and name of each token/NFT, enabling us to calculate the total quantity held.
  • SUM(mto.quantity) calculates the total quantity of each token/NFT by summing up the quantities found in all relevant UTXOs.

Summary

  • The query looks at all transaction outputs containing multi-assets.
  • It checks if the outputs belong to the specified wallet address and are unspent.
  • It retrieves the fingerprint and name of the multi-asset from the multi_asset (opens in a new tab) table.
  • It groups the results by fingerprint and name, summing up the quantities to get the total amount of each token/NFT held at our wallet address.
  • It returns a list of all the distinct tokens and NFTs in the wallet, along with their respective quantities.
Review wallet transaction historyQuery collection details