https://linen.dev logo
Join Slack
Powered by
<@U04SE163WC8> when syncing data form an API, how ...
# replication-ask-ai
y
@kapa.ai when syncing data form an API, how does Airbyte handle nested objects in responses when writing records to a destination?
k
Airbyte uses JsonSchema to model the schema of its streams, which allows it to handle arbitrary nesting of data in a way that a table/column-based model cannot. This is particularly useful when a data source contains "nested" data, where each record contains other objects nested inside it. For example, consider a flight object with nested data:
Copy code
json {    "airline": "alaska",    "origin": {    "airport_code": "SFO",    "terminal": "2",    "gate": "G23"    },    "destination": {    "airport_code": "JFK",    "terminal": "7",    "gate": "1"    }   }
The 
AirbyteCatalog
would model this as follows:
Copy code
json {    "streams": [    {    "name": "flights",    "supported_sync_modes": [    "full_refresh"    ],    "source_defined_cursor": false,    "json_schema": {    "type": "object",    "properties": {    "airline": {    "type": "string"    },    "origin": {    "type": "object",    "properties": {    "airport_code": {    "type": "string"    },    "terminal": {    "type": "string"    },    "gate": {    "type": "string"    }    }    },    "destination": {    "type": "object",    "properties": {    "airport_code": {    "type": "string"    },    "terminal": {    "type": "string"    },    "gate": {    "type": "string"    }    }    }    }    }    }    ]   }
When it comes to writing these records to a destination, destinations must have handling for all types, but they are free to cast types to a convenient representation. For example, if a destination cannot natively handle 
array
fields, the destination connector is free to simply JSON-serialize the array back to a string when pushing data into the end platform. Destinations are free to choose the most convenient/reasonable representation for any given value. JSON serialization is just one possible strategy. Many SQL destinations will fall back to a native JSON type (e.g. Postgres' JSONB type, or Snowflake's VARIANT). For more details, you can refer to the [Airbyte documentation on supported data types](https://docs.airbyte.com/understanding-airbyte/supported-data-types) and the [beginner's guide to the AirbyteCatalog](https://docs.airbyte.com/understanding-airbyte/beginners-guide-to-catalog).
y
would Airbyte create tables for nested objects? @kapa.ai
k
A message from kapa.ai
Yes, Airbyte's Basic Normalization process expands nested objects and arrays into separate tables to allow more ergonomic querying of your data. For example, consider a nested object like this:
Copy code
json {    "make": "alfa romeo",    "model": "4C coupe",    "powertrain_specs": { "horsepower": 247, "transmission": "6-speed" }   }
The normalized schema would create separate tables for the car and its powertrain specifications:
Copy code
sql CREATE TABLE "cars" (    "_airbyte_cars_hashid" VARCHAR,    "_airbyte_emitted_at" TIMESTAMP_WITH_TIMEZONE,    "_airbyte_normalized_at" TIMESTAMP_WITH_TIMEZONE,    "make" VARCHAR,    "model" VARCHAR   );      CREATE TABLE "powertrain_specs" (    "_airbyte_powertrain_hashid" VARCHAR,    "_airbyte_cars_foreign_hashid" VARCHAR,    "_airbyte_emitted_at" TIMESTAMP_WITH_TIMEZONE,    "_airbyte_normalized_at" TIMESTAMP_WITH_TIMEZONE,    "horsepower" INTEGER,    "transmission" VARCHAR   );
In the case of naming collisions for un-nested objects, Basic Normalization chooses the expanded name following a specific naming scheme to avoid name collisions and ensure a more consistent naming scheme. However, with the introduction of Destinations V2, columns of Airbyte type 
Object
in the Destination are no longer "unnested" into separate tables. Instead, they are kept as JSON objects within the main table. For more details, you can refer to the [Airbyte documentation on Basic Normalization](https://docs.airbyte.com/understanding-airbyte/basic-normalization).
Open in Slack
PreviousNext
Powered by