hi everyone, I have a postgres db in production using the json data type, can airbyte mapping json to bigquery as json not as a string?

Hi. Airbyte creates a json object to bq. You can use json bq functions to map

hi @Eli Sigal, can you share the docs?

Bq json https://cloud.google.com/bigquery/docs/reference/standard-sql/json-data Airbyte json blob https://docs.airbyte.com/integrations/destinations/bigquery/

thanks

Hi, As far as I know, there is no option to configure default normalization to select the proper data type in AirByte UI, so you have few options: use another specific tool or build transformation on your own. 1. You can move and transform data using Datuum.ai: setup postgres as source connection, bigquery as target connection and do the mapping. Datuum.ai detects and casts data types from string to int/double/boolean/etc automatically. Also you can change data types manually if needed. Then, Datuum generates a complete Airbyte pipeline for you 2. You may build a pipeline manually in AirByte transformations. I guess you still need basic airbyte normalization, but you can add your own transformation (https://docs.airbyte.com/operator-guides/transformation-and-normalization/transformations-with-sql) and do manual casts to required data type (make it safe to handle errors): https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-reference. Although it may take time to set up a dbt project, create models and configure the pipeline, if you are not experienced with it yet. Hope it helps!