Wallet06 – Ledger Sync

6. Query Token metadata

In this SQL query, we retrieve the JSON metadata associated with a specific asset by joining multiple tables and applying filters.

SELECT tm.json
FROM ma_tx_mint mtm
JOIN tx_metadata tm ON tm.tx_id = mtm.tx_id
JOIN multi_asset ma ON ma.id = mtm.ident
WHERE
    ma.fingerprint = 'asset1nvcwnq60jnm27efjm87xnhqt6alsv024tdyxjm'
    AND tm.key = 721
ORDER BY
    mtm.id DESC
LIMIT 1

Expected results format

{
   "698a6ea0ca99f315034072af31eaac6ec11fe8558d3f48e9775aab9d":{
      "tDRIP":{
         "image":"ipfs://QmfUKSqkaJ8WNuvPzuYsTVZ8aDht3GGKKuzxrhi6S2orNw",
         "name":"tDRIP",
         "mediaType":"image/png",
         "attributes":{
            "publisher":[
               "zeronin ❤️ Memecoin (@Contactdrip) on X ",
               "https://dripdropz.io"
            ]
         }
      }
   }

🔎 Here's a breakdown of how this query works:

Select JSON Metadata:

SELECT tm.json

This part of the query specifies that we want to retrieve the json column from the tx_metadata (opens in a new tab) table.

From ma_tx_mint Table with Joins

FROM ma_tx_mint mtm
JOIN tx_metadata tm ON tm.tx_id = mtm.tx_id
JOIN multi_asset ma ON ma.id = mtm.ident

The data is being retrieved from the ma_tx_mint (opens in a new tab) table, aliased as mtm.

The query joins the tx_metadata (opens in a new tab) table tm, using the tx_id (transaction id) column. The multi_asset (opens in a new tab) table, alised ma, is joined with the indent column. using ma.id = mtm.ident.

Filter by Fingerprint and Metadata Key

WHERE
    ma.fingerprint = 'asset1nvcwnq60jnm27efjm87xnhqt6alsv024tdyxjm'
    AND tm.key = 721

This condition filters the rows to include only those where the ma.fingerprint matches our specific native asset id asset1nvcwnq60jnm27efjm87xnhqt6alsv024tdyxjm. tm.key = 721 is referring to 721 which is the metatdata standard for NFTs on Cardano.

Order and Limit the Results

ORDER BY
    mtm.id DESC
LIMIT 1

The results are ordered by mtm.id in descending order, which means the most recent entry comes first. The LIMIT 1 clause ensures that only the most recent entry is returned.

Summary

The query retrieves the JSON metadata for a specific asset by:

Joining the ma_tx_mint (opens in a new tab), tx_metadata (opens in a new tab), and multi_asset (opens in a new tab) tables from Ledger Sync's database schema.
Filtering by the asset's fingerprint and extract the JSON metadata associated with the minting transaction, specifically the metadata related to the NFT standard (721). This metadata contains details about the NFT's name, image, policy ID, etc.
Ordering by the most recent minting transaction and limiting the result to one.

Now that you understood about metadata and have a list of all the assets, you may want to gather more details about a specific collection. This could include information such as the policy ID of the collection, the creation date, the number of assets minted, the number of owners, number of transactions in the last 24 hours, and any unique attributes associated with it.

Query collection details Query Active Stake Pools