Goldsky Documentation

Overview

Turbo pipelines SQL transforms are powered by Apache DataFusion and include custom functions designed specifically for blockchain and Web3 data processing. These functions extend standard SQL with capabilities for:

Decoding EVM logs and Solana instructions
Working with 256-bit integers (U256/I256) common in smart contracts
Processing blockchain-specific data formats
Advanced array and JSON manipulation
Cryptographic operations

All standard DataFusion SQL functions are also available. This reference focuses on Turbo-specific custom functions.

Function Categories

EVM Functions

Decode logs, hash data, work with contract ABIs

Solana Functions

Decode instructions, process accounts, analyze transactions

Large Numbers

U256/I256 arithmetic for token amounts and calculations

Array Processing

Filter, enumerate, and transform arrays

String & Encoding

Hex, Base58, and text manipulation

JSON Functions

Parse, query, and construct JSON

EVM & Ethereum Functions

evm_log_decode

Decode Ethereum event logs using contract ABI. Signature:

evm_log_decode(abi_json, topics, data) -> STRUCT<name: VARCHAR, event_params: LIST<VARCHAR>>

Parameters:

abi_json - Contract ABI as JSON string (can be full ABI or just event definitions)
topics - Event topics array (topic[0] is event signature)
data - Event data field (hex string)

Returns: Struct containing:

name - Event name (e.g., “Transfer”)
event_params - Array of decoded parameter values as strings

Example: Decode ERC-20 Transfer Events

transforms:
  decoded_transfers:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        transaction_hash,
        address as contract_address,
        -- Decode the Transfer event
        evm_log_decode(
          '[{"anonymous":false,"inputs":[{"indexed":true,"name":"from","type":"address"},{"indexed":true,"name":"to","type":"address"},{"indexed":false,"name":"value","type":"uint256"}],"name":"Transfer","type":"event"}]',
          topics,
          data
        ) as decoded,
        block_timestamp
      FROM ethereum_logs
      WHERE topics[1] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'  -- Transfer signature

Accessing decoded fields:

sql: |
  WITH decoded AS (
    SELECT
      id,
      evm_log_decode(abi, topics, data) as evt
    FROM ethereum_logs
  )
  SELECT
    id,
    evt.name as event_name,
    evt.event_params[1] as from_address,
    evt.event_params[2] as to_address,
    evt.event_params[3] as value
  FROM decoded

The decoder uses caching internally for performance. The same ABI can be reused across millions of records efficiently.

Aliases: _gs_log_decode, evm_decode_log

_gs_keccak256

Compute Keccak256 hash (same as Solidity’s keccak256). Signature:

_gs_keccak256(input) -> VARCHAR

Parameters:

input - String to hash (can be regular string or hex with “0x” prefix)

Returns: Hex-encoded hash with “0x” prefix Example: Compute Event Signatures

sql: |
  SELECT
    _gs_keccak256('Transfer(address,address,uint256)') as transfer_signature,
    _gs_keccak256('Approval(address,address,uint256)') as approval_signature
  -- Results:
  -- 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef
  -- 0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925

Example: Verify Topic Matches

sql: |
  SELECT *
  FROM ethereum_logs
  WHERE topics[1] = _gs_keccak256('Transfer(address,address,uint256)')

_gs_hex_to_byte / _gs_byte_to_hex

Convert between hex strings and bytes. Signatures:

_gs_hex_to_byte(hex_string) -> BINARY
_gs_byte_to_hex(bytes) -> VARCHAR

Example:

sql: |
  SELECT
    _gs_hex_to_byte('0x1234') as bytes,
    _gs_byte_to_hex(data) as hex_string
  FROM ethereum_transactions

Solana Functions

Turbo pipelines include specialized functions for decoding Solana program instructions and analyzing transaction data. Many of the below are already used in our default solana.instructions dataset and other datasets.

_gs_decode_instruction_data (IDL Decode)

Decode Solana program instruction or event data using an IDL (Interface Definition Language) specification. This is the most flexible Solana decoding function - it works with any custom Solana program that has an IDL. Signature:

_gs_decode_instruction_data(idl_json, data) -> STRUCT<name: VARCHAR, value: VARCHAR>

Parameters:

idl_json - IDL specification as JSON string (can be fetched with _gs_fetch_abi())
instruction_data - Base58-encoded instruction data

Returns: Struct containing:

name - Instruction or event name (e.g., “initialize”, “swap”, “transfer”)
value - JSON string with decoded parameter values

How it works:

Parses the IDL JSON specification
Attempts to decode as an instruction first
Falls back to event decoding if instruction decoding fails
Returns both the name and decoded values

Example: Access Decoded Fields after decoding

sql: |
  WITH decoded AS (
    SELECT
      id,
      _gs_decode_instruction_data(
        _gs_fetch_abi('https://api.example.com/jupiter-v6.json', 'raw'),
        data
      ) as decoded_ix,
      accounts
    FROM solana_instructions
    WHERE program_id = 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4'
  )
  SELECT
    id,
    decoded_ix.name as instruction_name,
    -- Parse the JSON value to extract specific fields
    json_value(decoded_ix.value, '$.amountIn') as amount_in,
    json_value(decoded_ix.value, '$.minimumAmountOut') as min_amount_out,
    accounts[1] as user_account
  FROM decoded
  WHERE decoded_ix.name = 'sharedAccountsRoute'

Example: Decode with Inline IDL For simpler programs, you can provide the IDL directly:

sql: |
  SELECT
    id,
    _gs_decode_instruction_data(
      '{"instructions":[{"name":"initialize","accounts":[{"name":"authority","isMut":false,"isSigner":true}],"args":[{"name":"bump","type":"u8"}]}]}',
      data
    ) as decoded
  FROM solana_instructions

Performance: IDL decoders are cached internally. Reusing the same IDL JSON string (via _gs_fetch_abi()) across multiple rows is very efficient - the IDL is only parsed once.

When to use:

Custom Solana programs with available IDL specifications
Decoding instructions from DEXes, DeFi protocols, NFT marketplaces
Any program where you need flexible, schema-driven decoding

Use program-specific decoders instead for:

Solana system programs (Token, System, Stake, Vote, etc.) - use gs_solana_decode_* functions below
Better performance for known programs with hardcoded decoders

gs_solana_decode_token_program_instruction

Decode Solana Token Program (SPL Token) instructions. Signature:

gs_solana_decode_token_program_instruction(data) -> STRUCT

Example: Decode Token Transfers

transforms:
  decoded_token_ops:
    type: sql
    primary_key: signature
    sql: |
      SELECT
        signature,
        gs_solana_decode_token_program_instruction(
          instructions[1].data
        ) as decoded_instruction
      FROM solana_transactions
      WHERE instructions[1].programId = 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'

gs_solana_decode_system_program_instruction

Decode Solana System Program instructions (transfers, account creation, etc.). Signature:

gs_solana_decode_system_program_instruction(data) -> STRUCT

gs_solana_get_accounts

Extract account information from Solana transactions. Signature:

gs_solana_get_accounts(transaction_data) -> LIST<STRUCT>

Example: Analyze Account Interactions

sql: |
  SELECT
    signature,
    gs_solana_get_accounts(transaction) as accounts,
    array_length(gs_solana_get_accounts(transaction)) as account_count
  FROM solana_transactions

gs_solana_get_balance_changes

Track SOL balance changes within a transaction. Signature:

gs_solana_get_balance_changes(transaction_data) -> LIST<STRUCT<account: VARCHAR, pre_balance: BIGINT, post_balance: BIGINT>>

Example: Monitor Large Balance Changes

sql: |
  WITH balance_changes AS (
    SELECT
      signature,
      gs_solana_get_balance_changes(transaction) as changes
    FROM solana_transactions
  )
  SELECT
    signature,
    change.account,
    change.post_balance - change.pre_balance as net_change
  FROM balance_changes
  CROSS JOIN UNNEST(changes) AS change
  WHERE ABS(change.post_balance - change.pre_balance) > 1000000000  -- > 1 SOL

Other Solana Decoders

All follow similar patterns:

gs_solana_decode_associated_token_program_instruction - Decode ATA program
gs_solana_decode_stake_program_instruction - Decode staking operations
gs_solana_decode_vote_program_instruction - Decode vote program
gs_solana_decode_bpf_loader_instruction - Decode BPF loader
gs_solana_decode_bpf_upgradeable_loader_instruction - Decode upgradeable programs
gs_solana_decode_address_lookup_table_instruction - Decode address lookup tables

_gs_from_base58

Decode Base58 strings (common in Solana for addresses and signatures). Signature:

_gs_from_base58(base58_string) -> BINARY

Example:

sql: |
  SELECT
    _gs_from_base58(account_address) as decoded_address
  FROM solana_accounts

Large Number Arithmetic (U256 & I256)

Blockchain smart contracts frequently use 256-bit integers for token amounts, balances, and calculations. These functions provide precise arithmetic without JavaScript’s number precision limits.

U256 Functions (Unsigned 256-bit)

to_u256

Convert various types to U256. Signature:

to_u256(value) -> U256

Accepts: VARCHAR, INT64, UINT64, INT32, UINT32, INT16, UINT16, INT8, UINT8 Example:

sql: |
  SELECT
    to_u256('1000000000000000000') as one_eth_wei,
    to_u256(value) as value_u256
  FROM erc20_transfers

u256_to_string

Convert U256 back to decimal string. Signature:

u256_to_string(u256_value) -> VARCHAR

u256_add / u256_sub / u256_mul / u256_div / u256_mod

U256 arithmetic operations. Signatures:

u256_add(a, b) -> U256
u256_sub(a, b) -> U256
u256_mul(a, b) -> U256
u256_div(a, b) -> U256
u256_mod(a, b) -> U256

Automatic Operator Rewriting: Once values are cast to U256 or I256 types, you can use standard SQL operators (+, -, *, /, %) instead of explicit function calls. Turbo’s SQL preprocessor automatically rewrites these to the appropriate functions.

-- These are equivalent:
to_u256(value) / to_u256('1000000')
u256_div(to_u256(value), to_u256('1000000'))

-- Use the cleaner syntax:
to_u256(value) + to_u256(fee)  -- Automatically becomes u256_add()
to_u256(balance) * to_u256(2)  -- Automatically becomes u256_mul()

Example: Calculate Token Amounts in ETH

transforms:
  token_amounts_eth:
    type: sql
    primary_key: id
    sql: |
      SELECT
        id,
        from_address,
        to_address,
        value,
        -- Convert wei to ETH (divide by 10^18) - using clean operator syntax
        u256_to_string(
          to_u256(value) / to_u256('1000000000000000000')
        ) as amount_eth,
        -- Calculate 1% fee
        u256_to_string(
          to_u256(value) / to_u256('100')
        ) as fee_1_percent
      FROM erc20_transfers

Example: Sum Multiple Token Amounts

sql: |
  SELECT
    to_address,
    -- Add two transfer amounts - using clean operator syntax
    u256_to_string(
      to_u256(transfer1_value) + to_u256(transfer2_value)
    ) as total_received
  FROM combined_transfers

U256 division truncates (no decimals). For percentage calculations or conversions to smaller units, multiply first, then divide.Good: (amount * to_u256('100')) / to_u256('1000000') (multiply by 100 first)Bad: (amount / to_u256('1000000')) * to_u256('100') (loses precision)

I256 Functions (Signed 256-bit)

Similar to U256 but supports negative numbers. Available functions:

to_i256(value) - Convert to I256
i256_to_string(i256_value) - Convert to string
i256_add(a, b) / i256_sub(a, b) - Addition/subtraction
i256_mul(a, b) / i256_div(a, b) / i256_mod(a, b) - Multiplication/division/modulo
i256_neg(value) - Negate (multiply by -1)
i256_abs(value) - Absolute value

Example: Track Profit/Loss

sql: |
  SELECT
    address,
    -- Calculate profit/loss using clean operator syntax
    i256_to_string(
      to_i256(current_balance) - to_i256(initial_balance)
    ) as profit_loss,
    -- Get absolute value of change
    i256_to_string(
      i256_abs(
        to_i256(current_balance) - to_i256(initial_balance)
      )
    ) as abs_change
  FROM balances

I256 and U256 operators work the same way - standard operators (+, -, *, /, %) are automatically rewritten to their corresponding function calls by the SQL preprocessor.

Array Processing Functions

array_filter

Filter array elements based on field value. Signature:

array_filter(array, field_name, value) -> LIST

Parameters:

array - Array of structs to filter
field_name - Name of field to match
value - Value to match against

Example: Filter Logs by Event Name

sql: |
  SELECT
    signature,
    -- Get only 'Transfer' events from instruction logs
    array_filter(
      instruction_logs,
      'event_name',
      'Transfer'
    ) as transfer_events
  FROM solana_transactions

Example: Filter Failed Instructions

sql: |
  SELECT
    slot,
    array_filter(
      transactions,
      'err',
      null
    ) as successful_transactions
  FROM solana_blocks

array_filter_first

Like array_filter but returns only the first matching element. Signature:

array_filter_first(array, field_name, value) -> STRUCT (or NULL)

Example:

sql: |
  SELECT
    signature,
    array_filter_first(
      instructions,
      'programId',
      'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
    ) as first_token_instruction
  FROM solana_transactions

array_enumerate

Add index to each array element. Signature:

array_enumerate(array) -> LIST<STRUCT<index: INT, value: T>>

Example:

sql: |
  SELECT
    signature,
    array_enumerate(instructions) as indexed_instructions
  FROM solana_transactions

  -- Access with: indexed_instructions[1].index, indexed_instructions[1].value

zip_arrays

Combine multiple arrays element-wise. Signature:

zip_arrays(array1, array2, ...) -> LIST<STRUCT>

Example:

sql: |
  SELECT
    zip_arrays(
      account_keys,
      pre_balances,
      post_balances
    ) as account_balance_changes
  FROM solana_transactions

String & Encoding Functions

split_string_to_array

Split string into array by delimiter. Signature:

split_string_to_array(string, delimiter) -> LIST<VARCHAR>

Example:

sql: |
  SELECT
    split_string_to_array(metadata_tags, ',') as tags_array
  FROM nft_metadata

Regular Expression Functions

Turbo pipelines include Flink-compatible regex functions: regexp_extract - Extract regex match groups

regexp_extract(string, pattern, group_index) -> VARCHAR

regexp_replace - Replace regex matches

regexp_replace(string, pattern, replacement) -> VARCHAR

regexp_count - Count regex matches

regexp_count(string, pattern) -> INT

Example: Extract Address from Log Message

sql: |
  SELECT
    regexp_extract(
      log_message,
      'address: (0x[0-9a-fA-F]{40})',
      1
    ) as extracted_address
  FROM contract_logs

URL Functions

parse_url - Extract URL components

parse_url(url, component) -> VARCHAR
-- component: 'HOST', 'PATH', 'QUERY', 'PROTOCOL', etc.

url_encode / url_decode - URL encoding/decoding

url_encode(string) -> VARCHAR
url_decode(string) -> VARCHAR

Example:

sql: |
  SELECT
    parse_url(metadata_url, 'HOST') as host,
    parse_url(metadata_url, 'PATH') as path
  FROM nft_metadata

Other String Functions

bin(number) - Convert to binary representation
translate(string, from_chars, to_chars) - Character translation
elt(index, string1, string2, ...) - Return element at index
locate(substring, string) - Find substring position
unhex(hex_string) - Decode hex string

Plus all standard SQL string functions: lower, upper, trim, substring, concat, replace, reverse, etc.

JSON Functions

Turbo pipelines have comprehensive JSON support compatible with Flink SQL.

json_query

Query JSON documents using path expressions. Signature:

json_query(json_string, path) -> VARCHAR

Example: Extract NFT Metadata

transforms:
  nft_attributes:
    type: sql
    primary_key: token_id
    sql: |
      SELECT
        token_id,
        json_query(metadata, '$.name') as nft_name,
        json_query(metadata, '$.description') as description,
        json_query(metadata, '$.image') as image_url,
        json_query(metadata, '$.attributes') as attributes_json
      FROM nft_tokens

json_value

Extract scalar value from JSON. Signature:

json_value(json_string, path) -> VARCHAR

Example:

sql: |
  SELECT
    json_value(metadata, '$.properties.category') as category,
    json_value(metadata, '$.properties.rarity') as rarity
  FROM nft_metadata

parse_json / try_parse_json

Parse JSON string to validate or convert. Signatures:

parse_json(json_string) -> JSON  -- Errors on invalid JSON
try_parse_json(json_string) -> JSON  -- Returns NULL on invalid JSON

Example: Safe JSON Parsing

sql: |
  SELECT
    token_id,
    try_parse_json(metadata_string) as parsed_metadata,
    -- Check if JSON is valid
    CASE
      WHEN try_parse_json(metadata_string) IS NOT NULL THEN true
      ELSE false
    END as is_valid_json
  FROM nft_tokens

is_json

Check if string is valid JSON. Signature:

is_json(string) -> BOOLEAN

json_object / json_array

Construct JSON objects and arrays. Signatures:

json_object(key1, value1, key2, value2, ...) -> JSON
json_array(value1, value2, ...) -> JSON

Example: Build JSON Response

sql: |
  SELECT
    json_object(
      'address', from_address,
      'amount', value,
      'timestamp', block_timestamp
    ) as transfer_json
  FROM erc20_transfers

Null handling variants:

json_object_absent_on_null - Omit null fields
json_array_absent_on_null - Omit null elements

json_exists

Check if JSON path exists. Signature:

json_exists(json_string, path) -> BOOLEAN

Example:

sql: |
  SELECT *
  FROM nft_metadata
  WHERE json_exists(metadata, '$.attributes.rare')

Time Functions

now / current_time / current_date

Get current timestamp, time, or date. Signatures:

now() -> TIMESTAMP
current_time() -> TIME
current_date() -> DATE

These functions are volatile - they return different values on each call. Use them for adding processing timestamps, not for filtering historical data.

Example: Add Processing Timestamp

sql: |
  SELECT
    *,
    now() as processed_at,
    current_date() as processing_date
  FROM ethereum_transactions

date_part

Extract part of a timestamp. Signature:

date_part(part, timestamp) -> INT
-- part: 'year', 'month', 'day', 'hour', 'minute', 'second', 'epoch', etc.

Example: Analyze by Hour

sql: |
  SELECT
    date_part('hour', to_timestamp(blockTime)) as hour_of_day,
    date_part('epoch', to_timestamp(blockTime)) - blockTime as age_seconds
  FROM solana_blocks

to_timestamp / to_timestamp_micros

Convert Unix timestamp to TIMESTAMP. Signatures:

to_timestamp(seconds) -> TIMESTAMP
to_timestamp_micros(microseconds) -> TIMESTAMP

Example:

sql: |
  SELECT
    slot,
    to_timestamp(blockTime) as block_timestamp,
    to_timestamp_micros(blockTime * 1000000) as block_timestamp_precise
  FROM solana_blocks

Hash Functions

xxhash

Fast non-cryptographic hash function. Signature:

xxhash(input) -> BIGINT

Example: Create Deterministic IDs

sql: |
  SELECT
    xxhash(concat(transaction_hash, '_', log_index::VARCHAR)) as unique_id,
    transaction_hash,
    log_index
  FROM ethereum_logs

Dynamic Table Functions

dynamic_table_check

Check if a value exists in a dynamic table (async function). Signature:

dynamic_table_check(table_name, value) -> BOOLEAN

Parameters:

table_name - Reference name of the dynamic table (from your pipeline config)
value - Value to check for existence

Returns: true if value exists in the table, false otherwise See the Dynamic Tables documentation for complete details. Example:

transforms:
  tracked_wallets:
    type: dynamic_table
    backend_type: Postgres
    backend_entity_name: user_wallets
    secret_name: MY_POSTGRES

  filtered_transfers:
    type: sql
    primary_key: id
    sql: |
      SELECT *
      FROM erc20_transfers
      WHERE dynamic_table_check('tracked_wallets', from_address)
         OR dynamic_table_check('tracked_wallets', to_address)

Function Composition

Functions can be composed to build complex transformations. Example: Decode Log and Convert Value

sql: |
  WITH decoded AS (
    SELECT
      id,
      evm_log_decode(abi, topics, data) as evt,
      block_timestamp
    FROM ethereum_logs
  )
  SELECT
    id,
    evt.name as event_name,
    evt.event_params[1] as from_address,
    evt.event_params[2] as to_address,
    -- Convert wei to ETH using U256 with clean operator syntax
    u256_to_string(
      to_u256(evt.event_params[3]) / to_u256('1000000000000000000')
    ) as amount_eth,
    block_timestamp
  FROM decoded

Example: Filter Array and Decode First Match

sql: |
  SELECT
    signature,
    gs_solana_decode_token_program_instruction(
      array_filter_first(
        instructions,
        'programId',
        'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
      ).data
    ) as decoded_token_instruction
  FROM solana_transactions

Performance Considerations

Function Caching

Some functions use internal caching for performance:

evm_log_decode caches ABI decoders (reuse the same ABI string)
Regex functions cache compiled patterns
For best performance, use literal strings for patterns/ABIs when possible

Async Functions

dynamic_table_check is async and may perform I/O operations (database lookups). Use it strategically:

Good: Filter with WHERE clause using dynamic table
Avoid: Using in complex calculations or deeply nested expressions

U256/I256 Operations

Large number operations are more expensive than native integers:

Convert to U256 only when needed for precision
Do as much filtering as possible before U256 operations
Chain operations to minimize conversions: u256_to_string(u256_div(...)) not u256_to_string(...) / u256_to_string(...)

Array Operations

Array processing can be expensive on large arrays:

Filter arrays early in the pipeline
Use array_filter_first instead of array_filter when you only need one element
Consider if SQL filtering can reduce array size before processing

Best Practices

Use type-specific functions

Use U256/I256 for token amounts, regular INT for counters and IDs

Validate data

Use try_parse_json instead of parse_json when data quality is uncertain

Minimize function calls

Store function results in CTEs when used multiple times:

WITH decoded AS (
  SELECT id, evm_log_decode(abi, topics, data) as evt
  FROM logs
)
SELECT
  evt.name,
  evt.event_params[1],
  evt.event_params[2]
FROM decoded

Handle NULLs

Many blockchain fields can be NULL - use COALESCE or NULL checks

Use appropriate hash functions

_gs_keccak256 for EVM-compatible hashing (event signatures, etc.)
xxhash for fast non-cryptographic hashing (IDs, deduplication)

Common Patterns

Pattern: Decode and Transform ERC-20 Transfers

transforms:
  decoded_erc20_transfers:
    type: sql
    primary_key: id
    sql: |
      WITH decoded AS (
        SELECT
          id,
          address as contract_address,
          transaction_hash,
          evm_log_decode(
            '[{"anonymous":false,"inputs":[{"indexed":true,"name":"from","type":"address"},{"indexed":true,"name":"to","type":"address"},{"indexed":false,"name":"value","type":"uint256"}],"name":"Transfer","type":"event"}]',
            topics,
            data
          ) as evt,
          block_number,
          block_timestamp
        FROM ethereum_logs
        WHERE topics[1] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
      )
      SELECT
        id,
        contract_address,
        transaction_hash,
        lower(evt.event_params[1]) as from_address,
        lower(evt.event_params[2]) as to_address,
        evt.event_params[3] as value_wei,
        u256_to_string(
          to_u256(evt.event_params[3]) / to_u256('1000000000000000000')
        ) as value_eth,
        block_number,
        block_timestamp
      FROM decoded

Pattern: Filter Solana Token Transfers

transforms:
  solana_token_transfers:
    type: sql
    primary_key: signature
    sql: |
      WITH token_ix AS (
        SELECT
          signature,
          slot,
          blockTime,
          array_filter(
            instructions,
            'programId',
            'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
          ) as token_instructions
        FROM solana_transactions
      )
      SELECT
        signature,
        slot,
        to_timestamp(blockTime) as block_timestamp,
        gs_solana_decode_token_program_instruction(
          token_instructions[1].data
        ) as decoded_instruction,
        array_length(token_instructions) as token_instruction_count
      FROM token_ix
      WHERE array_length(token_instructions) > 0

Pattern: Process NFT Metadata with JSON

transforms:
  nft_enriched:
    type: sql
    primary_key: token_id
    sql: |
      SELECT
        token_id,
        contract_address,
        -- Extract metadata fields safely
        COALESCE(
          json_value(metadata, '$.name'),
          'Unknown'
        ) as name,
        json_value(metadata, '$.description') as description,
        json_value(metadata, '$.image') as image_url,
        -- Check for rarity trait
        json_exists(metadata, '$.attributes[?(@.trait_type == "Rarity")]') as has_rarity,
        -- Validate metadata
        is_json(metadata) as valid_metadata
      FROM nft_tokens
      WHERE metadata IS NOT NULL
        AND is_json(metadata)

Next Steps

SQL Transforms

Learn about SQL transform configuration

Dynamic Tables

Use dynamic_table_check for filtering

TypeScript Transforms

For logic beyond SQL capabilities

Transform Overview

Explore other transform types

​Overview

​Function Categories

EVM Functions

Solana Functions

Large Numbers

Array Processing

String & Encoding

JSON Functions

​EVM & Ethereum Functions

​evm_log_decode

​_gs_keccak256

​_gs_hex_to_byte / _gs_byte_to_hex

​Solana Functions

​_gs_decode_instruction_data (IDL Decode)

​gs_solana_decode_token_program_instruction

​gs_solana_decode_system_program_instruction

​gs_solana_get_accounts

​gs_solana_get_balance_changes

​Other Solana Decoders

​_gs_from_base58

​Large Number Arithmetic (U256 & I256)

​U256 Functions (Unsigned 256-bit)

​to_u256

​u256_to_string

​u256_add / u256_sub / u256_mul / u256_div / u256_mod

​I256 Functions (Signed 256-bit)

​Array Processing Functions

​array_filter

​array_filter_first

​array_enumerate

​zip_arrays

​String & Encoding Functions

​split_string_to_array

​Regular Expression Functions

​URL Functions

​Other String Functions

​JSON Functions

​json_query

​json_value

​parse_json / try_parse_json

​is_json

​json_object / json_array

​json_exists

​Time Functions

​now / current_time / current_date

​date_part

​to_timestamp / to_timestamp_micros

​Hash Functions

​xxhash

​Dynamic Table Functions

​dynamic_table_check

​Function Composition

​Performance Considerations

​Best Practices

​Common Patterns

​Pattern: Decode and Transform ERC-20 Transfers

​Pattern: Filter Solana Token Transfers

​Pattern: Process NFT Metadata with JSON

​Next Steps

SQL Transforms

Dynamic Tables

TypeScript Transforms

Transform Overview

Overview

Function Categories

EVM & Ethereum Functions

evm_log_decode

_gs_keccak256

_gs_hex_to_byte / _gs_byte_to_hex

Solana Functions

_gs_decode_instruction_data (IDL Decode)

gs_solana_decode_token_program_instruction

gs_solana_decode_system_program_instruction

gs_solana_get_accounts

gs_solana_get_balance_changes

Other Solana Decoders

_gs_from_base58

Large Number Arithmetic (U256 & I256)

U256 Functions (Unsigned 256-bit)

to_u256

u256_to_string

u256_add / u256_sub / u256_mul / u256_div / u256_mod

I256 Functions (Signed 256-bit)

Array Processing Functions

array_filter

array_filter_first

array_enumerate

zip_arrays

String & Encoding Functions

split_string_to_array

Regular Expression Functions

URL Functions

Other String Functions

JSON Functions

json_query

json_value

parse_json / try_parse_json

is_json

json_object / json_array

json_exists

Time Functions

now / current_time / current_date

date_part

to_timestamp / to_timestamp_micros

Hash Functions

xxhash

Dynamic Table Functions

dynamic_table_check

Function Composition

Performance Considerations

Best Practices

Common Patterns

Pattern: Decode and Transform ERC-20 Transfers

Pattern: Filter Solana Token Transfers

Pattern: Process NFT Metadata with JSON

Next Steps