> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# Stellar Functions

Status: Experimental.

Stellar datasets keep the network's protocol data as it comes: in XDR, Stellar's binary format, written in base64. These
functions decode it. `stellar_scval_type` and `stellar_decode_scval` read the values smart contracts use, called
`ScVal`s, into typed columns, and `stellar_xdr_to_json` turns any other XDR record into JSON.

## Prerequisites

* A [connection](/querying/connecting) to a query server. The examples use `ampsql`.
* Nothing else for the examples: they decode values copied from Stellar mainnet transactions, in ledgers 65,221,500,
  65,280,046, and 65,280,048.
* For the [`stellar_xdr_to_json`](/querying/functions/stellar#stellar_xdr_to_json) example, [jq](https://jqlang.org), to format the JSON it
  returns.

## What to decode

:::warning
In v0.0.42, you can't query a [Stellar dataset](/datasets/tables/stellar): every query fails with
[`blocks table 'blocks' not found`](/providers/stellar#blocks-table-blocks-not-found-in-dataset). The functions decode
the values you give them, like the ones below, but not a Stellar dataset's columns.
:::

In a [Stellar dataset](/datasets/tables/stellar), these columns hold XDR:

| Column                                | Type               | XDR type                                      | Decode it with                               |
| ------------------------------------- | ------------------ | --------------------------------------------- | -------------------------------------------- |
| `events.topics_scval_xdr`             | `List(Utf8)`       | `ScVal`, for each topic                       | `stellar_decode_scval`, `stellar_scval_type` |
| `events.value_scval_xdr`              | `Utf8`             | `ScVal`                                       | `stellar_decode_scval`, `stellar_scval_type` |
| `transactions.envelope_xdr`           | `Utf8`             | `TransactionEnvelope`                         | `stellar_xdr_to_json`                        |
| `transactions.result_xdr`             | `Utf8`             | `TransactionResult`                           | `stellar_xdr_to_json`                        |
| `transactions.result_meta_xdr`        | `Utf8`             | `TransactionMeta`                             | `stellar_xdr_to_json`                        |
| `transactions.diagnostic_events_xdr`  | `List(Utf8)`       | `DiagnosticEvent`, for each event             | `stellar_xdr_to_json`                        |
| `transactions.transaction_events_xdr` | `List(Utf8)`       | `TransactionEvent`, for each event            | `stellar_xdr_to_json`                        |
| `transactions.contract_events_xdr`    | `List(List(Utf8))` | `ContractEvent`, in a list for each operation | `stellar_xdr_to_json`                        |
| `ledgers.metadata_xdr`                | `Utf8`             | `LedgerCloseMeta`                             | `stellar_xdr_to_json`                        |

A contract event has topics, a list of `ScVal`s that usually starts with the event's name, and a value. When a token
transfers, its contract emits a `transfer` event, with the sender, the recipient, and the asset as its other topics, and
the amount as its value, or a [map that holds the amount](/querying/functions/stellar#read-a-transfers-amount).

The type you decode to is a string literal, so Amp knows the result's type before it reads any rows: every row decodes
to that type, and a row that isn't valid XDR of that type returns `NULL`.

## Functions

### `stellar_scval_type`

Returns which kind of `ScVal` an XDR value holds, to choose the type to [decode it to](/querying/functions/stellar#stellar_decode_scval).

```sql
stellar_scval_type(xdr)
```

| Argument | Type   | Description                       |
| -------- | ------ | --------------------------------- |
| `xdr`    | `Utf8` | A `ScVal`, in base64-encoded XDR. |

It returns the name of the `ScVal`'s variant, like `Symbol`, `Address`, `I128`, `Vec`, or `Map`, as `Utf8`. It returns
`NULL` when `xdr` is `NULL`, or isn't a valid `ScVal`. Here, the topics and the value of a `transfer` event:

```sql
SELECT xdr, stellar_scval_type(xdr) AS type
FROM (VALUES
  ('AAAADwAAAAh0cmFuc2Zlcg=='),
  ('AAAAEgAAAAAAAAAAZh2xL4Keiy8X3xCgk+Rs+eyOsDPBU5gjcxoOHf6GlKQ='),
  ('AAAADgAAAAZuYXRpdmUAAA=='),
  ('AAAACgAAAAAAAAAAAAAAAAAChcg='),
  ('not XDR')
) AS scvals(xdr)
```

```text [Output]
+--------------------------------------------------------------+---------+
| xdr                                                          | type    |
+--------------------------------------------------------------+---------+
| AAAADwAAAAh0cmFuc2Zlcg==                                     | Symbol  |
| AAAAEgAAAAAAAAAAZh2xL4Keiy8X3xCgk+Rs+eyOsDPBU5gjcxoOHf6GlKQ= | Address |
| AAAADgAAAAZuYXRpdmUAAA==                                     | String  |
| AAAACgAAAAAAAAAAAAAAAAAChcg=                                 | I128    |
| not XDR                                                      |         |
+--------------------------------------------------------------+---------+
```

### `stellar_decode_scval`

Decodes a `ScVal` to a type you choose.

```sql
stellar_decode_scval(xdr, type)
```

| Argument | Type           | Description                                                      |
| -------- | -------------- | ---------------------------------------------------------------- |
| `xdr`    | `Utf8`         | A `ScVal`, in base64-encoded XDR.                                |
| `type`   | string literal | The type to decode it to, from the table below, like `'symbol'`. |

It returns the value, or `NULL` when `xdr` is `NULL`, isn't a valid `ScVal`, or holds another type than the one you
chose:

| `type`                         | Reads a `ScVal` of variant     | Returns                                                                              |
| ------------------------------ | ------------------------------ | ------------------------------------------------------------------------------------ |
| `bool`                         | `Bool`                         | `Boolean`                                                                            |
| `u32`, `i32`                   | `U32`, `I32`                   | `UInt32`, `Int32`                                                                    |
| `u64`, `i64`                   | `U64`, `I64`                   | `UInt64`, `Int64`                                                                    |
| `timepoint`, `duration`        | `Timepoint`, `Duration`        | `UInt64`, in seconds                                                                 |
| `u128`, `i128`, `u256`, `i256` | `U128`, `I128`, `U256`, `I256` | `Utf8`, in base 10, to keep every digit                                              |
| `bytes`                        | `Bytes`                        | `Binary`                                                                             |
| `string`, `symbol`             | `String`, `Symbol`             | `Utf8`. `NULL` for a string that isn't valid UTF-8.                                  |
| `address`                      | `Address`                      | `Utf8`, like `G…` for an account, `C…` for a contract, and `L…` for a liquidity pool |
| `json`, or its synonym `scval` | Any                            | `Utf8`: the value in JSON, like `{"i128":"165320"}`                                  |

Use `json` for the variants without a type of their own, like `Vec` and `Map`. Type names ignore case, `_`, and `-`, so
`'I128'` works too.

Here, the `transfer` event above, decoded: an account sent 165,320 stroops (0.016532 XLM) of `native`, Stellar's own
asset, the lumen:

```sql
SELECT
  stellar_decode_scval('AAAADwAAAAh0cmFuc2Zlcg==', 'symbol') AS event,
  stellar_decode_scval('AAAAEgAAAAAAAAAAZh2xL4Keiy8X3xCgk+Rs+eyOsDPBU5gjcxoOHf6GlKQ=', 'address') AS sender,
  stellar_decode_scval('AAAADgAAAAZuYXRpdmUAAA==', 'string') AS asset,
  stellar_decode_scval('AAAACgAAAAAAAAAAAAAAAAAChcg=', 'i128') AS amount
```

```text [Output]
+----------+----------------------------------------------------------+--------+--------+
| event    | sender                                                   | asset  | amount |
+----------+----------------------------------------------------------+--------+--------+
| transfer | GBTB3MJPQKPIWLYX34IKBE7ENT46ZDVQGPAVHGBDOMNA4HP6Q2KKJFLH | native | 165320 |
+----------+----------------------------------------------------------+--------+--------+
```

**Bad input.** The amount decodes as JSON, but not as a `u64`, and text that isn't XDR doesn't decode:

```sql
SELECT
  stellar_decode_scval('AAAACgAAAAAAAAAAAAAAAAAChcg=', 'json') AS as_json,
  stellar_decode_scval('AAAACgAAAAAAAAAAAAAAAAAChcg=', 'u64') AS as_u64,
  stellar_decode_scval('not XDR', 'i128') AS not_xdr
```

```text [Output]
+-------------------+--------+---------+
| as_json           | as_u64 | not_xdr |
+-------------------+--------+---------+
| {"i128":"165320"} |        |         |
+-------------------+--------+---------+
```

The query fails before it runs when:

| Cause                           | Message                                                                                                                                                                            |
| ------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `type` isn't one of the table's | `unsupported Stellar ScVal decode type 'text'; expected one of bool, u32, i32, u64, i64, timepoint, duration, bytes, string, symbol, address, u128, i128, u256, i256, json, scval` |
| `type` isn't a string literal   | `stellar_decode_scval: expected a string literal for the Stellar ScVal type`                                                                                                       |

#### Read a transfer's amount

A `transfer` event's value isn't always the amount on its own. A transfer to a muxed address, which adds an ID to a `G…`
address, has a map as its value, with the amount and the ID. Decoded to `json`, the map of a 100 XLM transfer to the ID
`4666` is:

```json
{
  "map": [
    { "key": { "symbol": "amount" }, "val": { "i128": "1000000000" } },
    { "key": { "symbol": "to_muxed_id" }, "val": { "u64": "4666" } }
  ]
}
```

A transfer to a plain `G…` address can have one too, when its transaction has a memo: `to_muxed_id` holds the memo, as a
`U64`, a `String`, or `Bytes`. This goes for every asset, and for `mint` events. Either way, the recipient topic is the
plain `G…` address.

Decoding a map to `i128` returns `NULL`, so a query that reads amounts that way silently misses these transfers. Read
the amount from the value's JSON instead, which works for both forms. Here, the transfer above, the one to the muxed
address, and one with a memo:

```sql
SELECT
  stellar_scval_type(value) AS type,
  stellar_decode_scval(value, 'i128') AS as_i128,
  regexp_match(
    stellar_decode_scval(value, 'json'),
    '"i128":"([0-9]+)"'
  )[1] AS amount
FROM (VALUES
  ('AAAACgAAAAAAAAAAAAAAAAAChcg='),
  ('AAAAEQAAAAEAAAACAAAADwAAAAZhbW91bnQAAAAAAAoAAAAAAAAAAAAAAAA7msoAAAAADwAAAAt0b19tdXhlZF9pZAAAAAAFAAAAAAAAEjo='),
  ('AAAAEQAAAAEAAAACAAAADwAAAAZhbW91bnQAAAAAAAoAAAAAAAAAAAAAAACqTf3wAAAADwAAAAt0b19tdXhlZF9pZAAAAAAOAAAACjE3NTYzMzE1NjgAAA==')
) AS transfers(value)
```

```text [Output]
+------+---------+------------+
| type | as_i128 | amount     |
+------+---------+------------+
| I128 | 165320  | 165320     |
| Map  |         | 1000000000 |
| Map  |         | 2857238000 |
+------+---------+------------+
```

The amount is `Utf8`. To add amounts up, cast them with `CAST(… AS DECIMAL(38, 0))`.

### `stellar_xdr_to_json`

Decodes an XDR record of a type you choose, and returns it as JSON.

```sql
stellar_xdr_to_json(xdr, type)
```

| Argument | Type           | Description                                                                                                                                                                                                                 |
| -------- | -------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `xdr`    | `Utf8`         | An XDR record, in base64.                                                                                                                                                                                                   |
| `type`   | string literal | Its XDR type: `ScVal`, `ContractEvent`, `DiagnosticEvent`, `TransactionEvent`, `TransactionEnvelope`, `TransactionResult`, `TransactionMeta`, `LedgerCloseMeta`, or `LedgerHeaderHistoryEntry`. Ignores case, `_`, and `-`. |

It returns the record as a JSON string, in the form Stellar's Rust XDR library, `stellar-xdr`, gives it. It returns
`NULL` when `xdr` is `NULL`, or isn't a valid record of that type.

Here, the `transfer` event above as a whole `ContractEvent`, from the transaction's `contract_events_xdr`. `ampsql`
returns the row as JSON, and jq formats the JSON in it:

:::terminal
```bash
ampsql --format jsonl "SELECT stellar_xdr_to_json('AAAAAAAAAAEltPzYWa7C+mNIQ4xImzw8EMmLbSG+T9PLMMtolT75dwAAAAEAAAAAAAAABAAAAA8AAAAIdHJhbnNmZXIAAAASAAAAAAAAAABmHbEvgp6LLxffEKCT5Gz57I6wM8FTmCNzGg4d/oaUpAAAABIAAAAAAAAAAH/bYrJF0xpUdXq6GH0hNVHjTP96fE+Fx4C9Pryf7izYAAAADgAAAAZuYXRpdmUAAAAAAAoAAAAAAAAAAAAAAAAAAoXI', 'ContractEvent') AS event" \
  | jq '.event | fromjson'
```

```text
{
  "ext": "v0",
  "contract_id": "CAS3J7GYLGXMF6TDJBBYYSE3HQ6BBSMLNUQ34T6TZMYMW2EVH34XOWMA",
  "type_": "contract",
  "body": {
    "v0": {
      "topics": [
        {
          "symbol": "transfer"
        },
        {
          "address": "GBTB3MJPQKPIWLYX34IKBE7ENT46ZDVQGPAVHGBDOMNA4HP6Q2KKJFLH"
        },
        {
          "address": "GB75WYVSIXJRUVDVPK5BQ7JBGVI6GTH7PJ6E7BOHQC6T5PE75YWNQYLM"
        },
        {
          "string": "native"
        }
      ],
      "data": {
        "i128": "165320"
      }
    }
  }
}
```
:::

The query fails before it runs when:

| Cause                          | Message                                                                                                                                                                                                                        |
| ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `type` isn't one of the list's | `unsupported Stellar XDR JSON type 'Transaction'; expected one of ScVal, ContractEvent, DiagnosticEvent, TransactionEvent, TransactionEnvelope, TransactionResult, TransactionMeta, LedgerCloseMeta, LedgerHeaderHistoryEntry` |
| `type` isn't a string literal  | `stellar_xdr_to_json: expected a string literal for the Stellar XDR type`                                                                                                                                                      |

## Limits

To keep bad data from using too much memory, the functions don't decode XDR nested more than 128 levels deep, or longer
than 64 MiB: they return `NULL` for it.