JSON to Avro conversion

How records are converted when a file destination such as S3 writes Avro or Parquet files.

When a file destination such as S3 writes Avro or Parquet files, each stream's JSON schema is first converted to an Avro schema, and each record to an Avro record that follows it. Parquet files are then written from those Avro records, so everything on this page applies to Parquet too.

Records can come from any source, so the conversion follows fixed rules, and some JSON schemas can't be expressed exactly in Avro. This page lists the rules and where they lose information.

Type mapping

JSON type	Avro type
string	string
number	double
integer	long
boolean	boolean
null	null
object	record
array	array

Every field is nullable

Every field becomes a union with null. For example, a JSON string field has the Avro type ["null", "string"]. Sources can leave fields out of a record, so a field can't be required.

Dates and times

These JSON string formats become Avro logical types:

JSON type	JSON format	Avro type	Avro logical type	Stored as
`string`	`date`	`int`	`date`	Days since 1970-01-01 (spec)
`string`	`time`	`long`	`time-micros`	Microseconds after midnight (spec)
`string`	`date-time`	`long`	`timestamp-micros`	Microseconds since `1970-01-01T00:00:00Z` (spec)

Like every other field, each one is a union with null. Times and timestamps are stored in UTC, taking the value's time zone into account where it has one.

If a value can't be converted, for example a date-time string that isn't a valid timestamp, the field is written as null and the change is recorded in the record's _airbyte_meta.changes (see Metadata fields).

For example, a date field:

{ "type": "string", "format": "date" }

gets this Avro schema:

{ "type": ["null", { "type": "int", "logicalType": "date" }] }

A time field becomes { "type": "long", "logicalType": "time-micros" } and a date-time field becomes { "type": "long", "logicalType": "timestamp-micros" }, each in a union with null in the same way.

Combined schemas (allOf, anyOf, oneOf)

allOf, anyOf and oneOf become Avro unions, which are less strict than the JSON schema. For example:

{ "oneOf": [{ "type": "string" }, { "type": "integer" }] }

becomes:

{ "type": ["null", "string", "long"] }

Some unions behave unexpectedly or make the sync fail:

A union of two time types, or of two timestamp types (with and without a time zone), works as expected. A union of any time type with any timestamp type makes the sync fail.
A union of a date with a time or timestamp type has undefined results.
A union of a string and a date or time type is always written as a string, even when the value is a valid timestamp.
A union of an integer and a timestamp type writes null for timestamps, and records the change in _airbyte_meta.changes.

The not keyword isn't supported, because Avro schemas have no equivalent.

Names

Stream and field names can only contain letters, digits and underscores (a-z, A-Z, 0-9, _). Other characters are replaced: accented letters by their plain letter, anything else by an underscore. For example, spécial:character_names becomes special_character_names.

The original name is kept in the field's doc property, as _airbyte_original_name:<original name>.

A name can't start with a digit, so names that do get an underscore in front.

Arrays

Arrays with a schema per position

In JSON schema, items can be a list, which gives each position in the array its own schema. In this example, the first item is a string and the second a number:

{
  "array_field": {
    "type": "array",
    "items": [{ "type": "string" }, { "type": "number" }]
  }
}

Avro doesn't support this, so the item types are combined into one union, which is less strict:

{
  "name": "array_field",
  "type": [
    "null",
    { "type": "array", "items": ["null", "string", "double"] }
  ],
  "default": null
}

Arrays of different objects

When the positions hold different objects, the objects are merged, recursively, into one Avro record. In this schema, the first object has an id, and the second has an id of slightly different types plus a message:

{
  "array_field": {
    "type": "array",
    "items": [
      {
        "type": "object",
        "properties": {
          "id": {
            "type": "object",
            "properties": {
              "id_part_1": { "type": "integer" },
              "id_part_2": { "type": "string" }
            }
          }
        }
      },
      {
        "type": "object",
        "properties": {
          "id": {
            "type": "object",
            "properties": {
              "id_part_1": { "type": "string" },
              "id_part_2": { "type": "integer" }
            }
          },
          "message": { "type": "string" }
        }
      }
    ]
  }
}

The merged Avro schema has one record with every field from both objects. Where the two disagree on a field's type, as with id_part_1, the field becomes a union of both:

{
  "name": "array_field",
  "type": [
    "null",
    {
      "type": "array",
      "items": [
        "null",
        {
          "type": "record",
          "name": "array_field",
          "fields": [
            {
              "name": "id",
              "type": [
                "null",
                {
                  "type": "record",
                  "name": "id",
                  "fields": [
                    { "name": "id_part_1", "type": ["null", "long", "string"], "default": null },
                    { "name": "id_part_2", "type": ["null", "string", "long"], "default": null }
                  ]
                }
              ],
              "default": null
            },
            { "name": "message", "type": ["null", "string"], "default": null }
          ]
        }
      ]
    }
  ],
  "default": null
}

So this record:

{
  "array_field": [
    { "id": { "id_part_1": 1000, "id_part_2": "abcde" } },
    { "id": { "id_part_1": "wxyz", "id_part_2": 2000 }, "message": "test message" }
  ]
}

is written with a null message in the first item, because that field now exists in both:

{
  "array_field": [
    { "id": { "id_part_1": 1000, "id_part_2": "abcde" }, "message": null },
    { "id": { "id_part_1": "wxyz", "id_part_2": 2000 }, "message": "test message" }
  ]
}

Arrays without item types

An array with no items can hold values of any type, but every Avro array needs one item type. These arrays are written as a string holding the JSON array. With this schema:

{
  "type": "object",
  "properties": {
    "identifier": { "type": "array" }
  }
}

the record { "identifier": ["151", 152, true, { "id": 153 }, null] } is written as:

{ "identifier": "[\"151\",152,true,{\"id\":153},null]" }

with the field typed ["null", "string"].

Objects

Properties the schema doesn't list

A JSON object can have properties that its schema doesn't declare. Avro can't hold fields of unknown type, so these properties are dropped without a warning. With this schema:

{
  "type": "object",
  "properties": {
    "username": { "type": ["null", "string"] }
  }
}

this record:

{
  "username": "admin",
  "active": true,
  "age": 21,
  "auth": { "auth_type": "ssl", "admin": false, "id": 1000 }
}

is written as:

{ "username": "admin" }

If you need those properties, check that the source's schema for the stream declares them.

Objects without properties

An object field whose schema has no properties is written as a string holding the JSON object. With the schema { "type": "object" }, the value { "username": "343-guilty-spark", "password": 1439, "active": true } is written as the string:

"{\"username\":\"343-guilty-spark\",\"password\":1439,\"active\":true}"

Fields without a type

A field whose schema has no type is treated as a string, and its value is written as a string.

Metadata fields

Every Avro record gets these fields besides your data. They're described in Connector concepts.

Field	Avro type
`_airbyte_raw_id`	`string` with the `uuid` logical type
`_airbyte_extracted_at`	`long` with the `timestamp-millis` logical type
`_airbyte_generation_id`	`long`
`_airbyte_meta`	A record with `sync_id` (`long`) and `changes`, a list of records with `field`, `change` and `reason` (all `string`)

A full example

With this stream schema:

{
  "type": "object",
  "$schema": "http://json-schema.org/draft-07/schema#",
  "properties": {
    "id": { "type": "integer" },
    "user": {
      "type": ["null", "object"],
      "properties": {
        "id": { "type": "integer" },
        "field_with_spécial_character": { "type": "integer" }
      }
    },
    "created_at": { "type": ["null", "string"], "format": "date-time" }
  }
}

the Avro schema is:

{
  "name": "stream_name",
  "type": "record",
  "fields": [
    { "name": "_airbyte_raw_id", "type": { "type": "string", "logicalType": "uuid" } },
    { "name": "_airbyte_extracted_at", "type": { "type": "long", "logicalType": "timestamp-millis" } },
    { "name": "_airbyte_generation_id", "type": "long" },
    {
      "name": "_airbyte_meta",
      "type": {
        "type": "record",
        "name": "_airbyte_meta",
        "namespace": "",
        "fields": [
          { "name": "sync_id", "type": "long" },
          {
            "name": "changes",
            "type": {
              "type": "array",
              "items": {
                "type": "record",
                "name": "change",
                "fields": [
                  { "name": "field", "type": "string" },
                  { "name": "change", "type": "string" },
                  { "name": "reason", "type": "string" }
                ]
              }
            }
          }
        ]
      }
    },
    { "name": "id", "type": ["null", "long"], "default": null },
    {
      "name": "user",
      "type": [
        "null",
        {
          "type": "record",
          "name": "user",
          "fields": [
            { "name": "id", "type": ["null", "long"], "default": null },
            {
              "name": "field_with_special_character",
              "type": ["null", "long"],
              "doc": "_airbyte_original_name:field_with_spécial_character",
              "default": null
            }
          ]
        }
      ],
      "default": null
    },
    {
      "name": "created_at",
      "type": ["null", { "type": "long", "logicalType": "timestamp-micros" }],
      "default": null
    }
  ]
}

On this page