TL;DR Short answer: to convert JSON to a database, the simplest route is often your database's own loader: Snowflake loads JSON into a VARIANT column, BigQuery loads newline-delimited JSON, and DuckDB reads JSON files straight into tables. An ETL tool earns its place when JSON files arrive continuously in cloud storage and need to land on a schedule alongside your other sources. Either way, decide early whether to keep nested data as JSON or flatten it into columns. Ten options:
Airbyte Fivetran Snowflake COPY INTO BigQuery load jobs DuckDB Hevo Data Matillion Talend Pentaho Data Integration Meltano One JSON constraint: many loaders expect JSON Lines, one object per line, not a single JSON array. BigQuery only accepts newline-delimited JSON, and most file-based ETL connectors read JSONL. Check which shape your files are in before choosing, because converting a large array file is an extra step.How do you convert JSON to a database table? JSON is hierarchical and a table is flat. A single record can contain nested objects several levels deep and arrays of any length, none of which map cleanly onto rows and columns, and nothing enforces a schema, so a field that is a string in one record can be an object in the next. There are three ways to bridge that:
Keep it as JSON: load each document into a semi-structured column, such as VARIANT in Snowflake, JSON in BigQuery, SUPER in Redshift or jsonb in Postgres, and extract fields in SQL. Robust to schema drift, and the most common modern approach.Flatten on load: map nested fields to columns as the data arrives. Easy to query, but new or changed fields can break the pipeline or be dropped.Normalise into tables: split arrays into child tables keyed to the parent record. The most relational result, and the most work to maintain.How do the JSON ETL tools compare? Tool Nested data handling Reads from Hosting Pricing basis Airbyte Lands nested data as JSON columns; flatten in dbt S3, GCS, Azure, SFTP, URL Cloud or self-hosted Free self-hosted; Cloud volume-based; Pro and Flex capacity-based Fivetran Managed schema handling Cloud storage, FTP Cloud Monthly active rows Snowflake COPY INTO VARIANT column, FLATTEN in SQL Snowflake stages Snowflake Snowflake compute BigQuery load jobs Nested fields or a JSON column Cloud Storage, local upload Google Cloud Free to load; storage and queries DuckDB Infers structs and lists; unnest in SQL Local files, S3, HTTP Runs where you run it Free, open source Hevo Data Optional auto-flattening Cloud storage Cloud Events Matillion Loads raw, flattens in the warehouse Cloud storage Your cloud account Credits Talend JSONPath mapping to columns Files, storage, APIs Cloud or on-premises Quoted subscription Pentaho Data Integration Path-based field mapping Files, URLs Self-hosted Community and enterprise editions Meltano Depends on the target Local and cloud files Self-hosted Free, open source
Which JSON ETL tools should you consider? The list mixes ETL platforms, which load JSON files on a schedule alongside other sources, with the native loaders in warehouses and DuckDB, which are often all a one-off or simple recurring load needs.
1. Airbyte Airbyte is an open-source data integration platform with 700+ connectors and a community of 25,000+ data engineers. Its file-based sources for Amazon S3, Google Cloud Storage, Azure Blob Storage and SFTP read JSON Lines files matched by a glob pattern and pick up new files incrementally, and its File source reads a single JSON file from a URL or cloud storage. Nested objects land as JSON columns in your destination, so the raw structure is preserved and you flatten it with SQL or dbt.
What's unique about Airbyte? Keeping nested data as JSON means a new field in tomorrow's files adds data rather than breaking the load. JSON files usually sit alongside database and SaaS sources, and Airbyte moves all of them through one pipeline. Connectors are open source and editable, and the Connector Development Kit covers anything else. It runs as self-hosted open source, on Airbyte Cloud, or as Enterprise Flex.
2. Fivetran Fivetran is a closed-source, managed ELT service with 700+ connectors, including file connectors for S3, Google Cloud Storage, Azure Blob Storage, FTP and SFTP that read JSON alongside CSV, Parquet and other formats. It handles schema changes automatically and bills on monthly active rows.
Here is a closer look at Airbyte vs Fivetran .
3. Snowflake COPY INTO Snowflake loads JSON natively. Define a file format of type JSON, set STRIP_OUTER_ARRAY if a file holds one large array, and COPY INTO a table with a VARIANT column from an internal or external stage. You then query fields with path notation and explode arrays with LATERAL FLATTEN. Snowpipe can run the same load automatically as new files arrive. It is the most direct route if your warehouse is Snowflake and the files are already in cloud storage.
4. BigQuery load jobs BigQuery loads newline-delimited JSON from Cloud Storage or a local upload, either auto-detecting a schema of nested and repeated fields or keeping each record in a JSON column. Loading is free; you pay for storage and queries. The catch is the format: a file containing a single JSON array has to be converted to one object per line first. BigQuery Data Transfer Service can schedule recurring loads from Cloud Storage.
5. DuckDB DuckDB is a free, in-process analytical database that reads JSON and JSON Lines files directly with read_json, inferring a schema of structs and lists, from local disk, S3 or HTTP. You can query the files immediately, unnest arrays in SQL, save the result as a table, or write it out to Parquet or into an attached Postgres database. It is often the quickest way to turn a folder of JSON into tables, though it is a query engine, not a scheduler.
6. Hevo Data Hevo Data is a no-code cloud ELT platform that loads JSON files from cloud storage, with an option to flatten nested fields automatically as data arrives. It suits teams that want flat tables without writing SQL, with the trade-off that auto-flattening can produce wide tables as files evolve. It is cloud-only and billed on events.
7. Matillion Matillion loads JSON files from cloud storage into Snowflake, Databricks or Redshift and then flattens them inside the warehouse with visual components that generate SQL, so the heavy lifting uses compute you already pay for. It has dbt support since v1.70 and bills in credits tied to job runtime.
Here is a closer look at Airbyte vs Matillion .
8. Talend Talend has JSON input components that map nested fields to columns using JSONPath or XPath-style expressions, and pairs integration with data quality and governance. Qlik acquired Talend in 2023 and now sells it as Qlik Talend Cloud, and Talend Open Studio, the free edition, was retired on 31 January 2024, so there is no free tier or self-serve route in.
9. Pentaho Data Integration Pentaho Data Integration, long known as Kettle, has a JSON Input step that reads files or URLs and maps nested fields by path into columns, with the rest of its visual transformation library available before loading into a database. It suits scheduled batch loads in teams already running Pentaho. Check the licensing of the edition you need, as the community and enterprise editions differ.
10. Meltano Meltano is an open-source, CLI-first platform spun out of GitLab that manages taps and targets as a version-controlled project. Its file taps read JSON and JSON Lines from local disk or cloud storage, and the target you choose decides whether nested data is flattened or kept as JSON. It suits engineers who want file loads defined in code.
How do you flatten nested JSON? Flattening turns nested fields into columns and arrays into rows. Most teams now load JSON as-is and flatten it in SQL, because that keeps the raw data and lets the model change without reloading. Each engine has its own syntax:
Snowflake: path notation such as data:customer.name for objects, and LATERAL FLATTEN to turn an array into rows.BigQuery: dot notation for nested fields, UNNEST for repeated fields, and JSON_VALUE or JSON_QUERY for JSON columns.Postgres: the -> and ->> operators on jsonb, and jsonb_array_elements or jsonb_to_recordset for arrays.SQL Server: OPENJSON with a WITH clause to project fields into columns, after reading the file with OPENROWSET.Wrap that SQL in dbt models and the flattened tables rebuild on every load. Flattening during extraction is worth it mainly when the destination cannot store JSON at all.
How do you start loading JSON files with Airbyte? Step 1: Set up your JSON files as a source In Airbyte, open Sources, choose New source and select the connector for where the files live: Amazon S3, Google Cloud Storage, Azure Blob Storage or SFTP Bulk for folders of files, or File for a single file at a URL. Enter the bucket or path and credentials, add a stream with a glob pattern such as data/*.jsonl, choose the JSON Lines format, and run the connection test.
Step 2: Set up a destination Choose where the data lands: a cloud warehouse such as Snowflake, BigQuery or Redshift, or a database such as Postgres.
Step 3: Configure the connection Use incremental sync so only new or modified files are read on each run, set a schedule that matches how often files arrive, and flatten the loaded data with SQL or dbt.
The process is the same in Airbyte Open Source, which you can deploy yourself , and in Airbyte Cloud, which you can try free for 14 days .
Which JSON ETL tool should you choose? For a one-off load, use your destination's own loader, or DuckDB if you just need tables quickly. For JSON files that keep arriving, use a tool that picks up new files incrementally: Airbyte, self-hosted or in Cloud, if they should land alongside your other sources, Fivetran for a managed equivalent, or Snowpipe and BigQuery scheduled loads if the files only ever go to one warehouse. Hevo suits teams that want automatic flattening, Matillion teams that transform in the warehouse, and Talend, Pentaho and Meltano teams already running them.
For tools that cover more than JSON files, see our guide to the top data integration tools .
Load JSON files with Airbyte Pick up JSON and JSON Lines files from S3, GCS, Azure or SFTP incrementally and land them in Snowflake, BigQuery, Postgres or any Airbyte destination, self-hosted or in Airbyte Cloud.
Try Airbyte Cloud free for 14 days or talk to our team .
Frequently Asked Questions (FAQs) 1. What is the difference between JSON and JSON Lines? A JSON file usually holds a single value, often one large array of objects. JSON Lines, also called newline-delimited JSON, puts one complete object on each line. Loaders prefer JSON Lines because they can read it in parallel and stream it, and BigQuery accepts only that format.
2. Should you flatten JSON before loading it into a database? Usually not, if the database supports semi-structured columns such as Snowflake VARIANT, BigQuery JSON or Postgres jsonb. Loading raw JSON and flattening in SQL keeps the original data and copes with new fields; flattening on load is mainly for databases that cannot store JSON.
3. How do you import a JSON file into SQL Server? Read the file with OPENROWSET using the BULK option, then use OPENJSON with a WITH clause to map JSON properties to columns and insert the result into a table. For recurring loads, an ETL tool with a SQL Server destination saves writing that each time.
4. What is the fastest free way to turn JSON files into tables? DuckDB. It reads JSON and JSON Lines directly with read_json, infers the schema, and can save the result as a table, write Parquet or insert into Postgres, all without a server.
Suggested reads CSV File ETL Tools
Parquet File ETL Tools
MongoDB ETL Tools
SFTP ETL Tools
.webp)