# Extracting and Converting Avro Values

## Extracting Avro Values

`subvalue_mapping` specifies which subvalues are extracted and the column or variable to which each one is assigned.

`LOAD DATA` uses the `::`-separated list of names in a `subvalue_path` to perform successive field name or union branch type name lookups in nested Avro records or unions. `subvalue_path` may not be used to extract elements of Avro arrays or maps. The path `%` refers to the entire Avro value being processed. Leading `%::` may be omitted from paths which are otherwise non-empty.

If a path can’t be found in an input Avro value, then:

* If a prefix of the path matches a record whose schema has no field matching the next name in the path, then `LOAD DATA` will terminate with an error. 
* If a prefix matches a union whose schema has no branch matching the next name, then `LOAD DATA` will terminate with an error. 
* If a prefix matches a union whose schema has a branch matching the next name, but that branch isn’t the selected branch in that instance of the union schema, then Avro `null` will be extracted instead and `LOAD DATA` will continue.

Path components naming union branches must use the two-part [*fullname*](http://avro.apache.org/docs/1.8.2/spec.html#names) of the branch’s type if that type is in a namespace.

Path components containing whitespace or punctuation must be surrounded by backticks.

Array and map elements may be indirectly extracted by applying [`JSON_EXTRACT_<type>`](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-extract-type.md) in a `SET` clause.

For example, consider two Avro records with the union schema:

```json
[
    "int",
    { "type" : "record",
      "name" : "a",
      "namespace" : "n",
      "fields" : [{ "name" : "f1",
                    "type" : "int" }]
    }
]

```

The paths ``%::`n.a`::f1`` and `` `n.a`::f1 `` will both extract `1` from an instance of this schema whose JSON encoding is `{"n.a":{"f1":1}}`.

They will extract `null` from an instance whose encoding is `{"int":2}`.

The paths `%::int` and `int` will extract `2` from the second instance and `null` from the first.

## Converting Avro Values

Before assignment or set clause evaluation, the Avro value extracted according to a `subvalue_path` is converted to an unspecified SQL type which may be further explicitly or implicitly converted as if from a SQL string whose value is as follows:

| Avro Type | Converted Value                                                                              |
| --------- | -------------------------------------------------------------------------------------------- |
| `null`    | SQL`NULL`                                                                                    |
| `boolean` | `"1"`/`"0"`                                                                                  |
| `int`     | The string representation of the value                                                       |
| `long`    | The string representation of the value                                                       |
| `float`   | SQL`NULL`if not finite. Otherwise, a string convertible without loss of precision to`FLOAT`  |
| `double`  | SQL`NULL`if not finite. Otherwise, a string convertible without loss of precision to`DOUBLE` |
| `enum`    | The string representation of the enum.                                                       |
| `bytes`   | Verbatim, from input bytes                                                                   |
| `string`  | Verbatim, from input bytes                                                                   |
| `fixed`   | Verbatim, from input bytes                                                                   |
| `record`  | [The JSON encoding of the value](http://avro.apache.org/docs/1.8.2/spec.html#json_encoding)  |
| `map`     | [The JSON encoding of the value](http://avro.apache.org/docs/1.8.2/spec.html#json_encoding)  |
| `array`   | [The JSON encoding of the value](http://avro.apache.org/docs/1.8.2/spec.html#json_encoding)  |
| `union`   | [The JSON encoding of the value](http://avro.apache.org/docs/1.8.2/spec.html#json_encoding)  |

`logicalType` attributes are ignored and have no effect on conversion.

***

Modified at: November 28, 2025

Source: [/db/v9.1/load-data/load-data-from-files/load-data-from-avro-files/extracting-and-converting-avro-values/](https://docs.singlestore.com/db/v9.1/load-data/load-data-from-files/load-data-from-avro-files/extracting-and-converting-avro-values/)

(An index of the documentation is available at /llms.txt)