Migrating JSON files from AWS S3 to PostgreSQL using AWS Glue and PySpark is one of the most common data engineering challenges teams face today. In this guide, you will learn exactly how to build a production-grade AWS Glue JSON to PostgreSQL migration pipeline — from reading raw JSON files in S3 to writing clean, validated data into PostgreSQL.

The typical architecture for this pipeline involves three key layers: storage, processing, and the target database. Each layer plays a critical role in ensuring data quality and reliability.

JSON Files → Amazon S3 → AWS Glue → PySpark Transformation → PostgreSQL

Specifically, by the end of this guide, you will understand:

• How JSON files are stored in S3
• How AWS Glue reads JSON data
• How to transform JSON using PySpark
• How to connect AWS Glue to PostgreSQL
• How to load data into PostgreSQL
• Incremental migration
• Handling nested JSON
• Error handling
• Data validation
• Performance considerations

AWS Glue Architecture: JSON S3 to PostgreSQL Migration Pipeline

The overall data flow is:

             JSON Files
                 |
                 v
        +----------------+
        |   Amazon S3    |
        |  Raw JSON Data |
        +----------------+
                 |
                 v
        +----------------+
        |    AWS Glue    |
        |  ETL / PySpark |
        +----------------+
                 |
        | Transform /
        | Validate /
        | Clean
                 |
                 v
        +----------------+
        |   PostgreSQL   |
        | Target Tables  |
        +----------------+

1. Source JSON Files in Amazon S3

Suppose we have customer JSON files in an S3 bucket.

Example:

s3://my-data-bucket/customer/

A JSON file might look like:

{
  "customer_id": 101,
  "customer_name": "John",
  "email": "john@example.com",
  "country": "USA",
  "created_date": "2026-08-01"
}

Another file could contain multiple records:

{"customer_id":101,"customer_name":"John","email":"john@example.com","country":"USA"}
{"customer_id":102,"customer_name":"David","email":"david@example.com","country":"UK"}
{"customer_id":103,"customer_name":"Smith","email":"smith@example.com","country":"India"}

Before starting the migration, we need to understand the source JSON structure and the expected PostgreSQL target schema.

2. Create the PostgreSQL Target Table

Suppose we want to migrate the JSON data into a PostgreSQL table.

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(200),
    country VARCHAR(100),
    created_date DATE
);

The mapping looks like this:

In other words, this mapping is important because JSON is schema-less while PostgreSQL is strongly typed.

Setting Up the AWS Glue ETL Job for JSON Migration

Create an AWS Glue ETL job.

For this example, we can use:

AWS Glue
   |
   +-- PySpark
   |
   +-- Amazon S3
   |
   +-- PostgreSQL

The Glue job will perform the following operations:

Read JSON
   ↓
Infer/Define Schema
   ↓
Validate Data
   ↓
Transform Data
   ↓
Convert Data Types
   ↓
Remove Duplicates
   ↓
Write to PostgreSQL

Read JSON From S3 Using PySpark in AWS Glue

AWS Glue provides PySpark, so we can use the familiar DataFrame API to read JSON. See the official AWS Glue PySpark documentation for full API reference.

from pyspark.sql import SparkSession
spark = SparkSession.builder \
    .appName("S3_JSON_to_PostgreSQL") \
    .getOrCreate()
source_path = "s3://my-data-bucket/customer/"
df = spark.read.json(source_path)
df.show()

The DataFrame could look like:

+-----------+-------------+-------------------+-------+
|customer_id|customer_name|email              |country|
+-----------+-------------+-------------------+-------+
|101        |John         |john@example.com   |USA    |
|102        |David        |david@example.com  |UK     |
|103        |Smith        |smith@example.com  |India  |
+-----------+-------------+-------------------+-------+

5. Explicit Schema vs Schema Inference

There are two common approaches to reading JSON schema in PySpark.

Schema Inference

Spark automatically determines the schema:

df = spark.read.json(source_path)

As a result, this approach is convenient during development and exploration.

However, for production pipelines, explicit schema definition is strongly recommended.

Define an Explicit Schema

from pyspark.sql.types import (
    StructType,
    StructField,
    IntegerType,
    StringType
)
schema = StructType([
    StructField("customer_id", IntegerType(), True),
    StructField("customer_name", StringType(), True),
    StructField("email", StringType(), True),
    StructField("country", StringType(), True),
    StructField("created_date", StringType(), True)
])
df = spark.read.schema(schema).json(source_path)

Therefore, this prevents unexpected schema inference failures in production.

6. PySpark Data Transformation Techniques

Now we can perform transformations using PySpark.

For example, convert created_date from string to date.

from pyspark.sql.functions import to_date, col
df = df.withColumn(
    "created_date",
    to_date(col("created_date"), "yyyy-MM-dd")
)

We can also remove duplicate customers:

df = df.dropDuplicates(["customer_id"])

Filter invalid records:

df = df.filter(
    col("customer_id").isNotNull()
)

Rename columns if necessary:

df = df.withColumnRenamed(
    "customer_name",
    "name"
)

7. Handling Nested JSON

One of the biggest challenges when migrating JSON data is nested structures.

For example:

{
  "customer_id": 101,
  "name": "John",
  "address": {
    "city": "New York",
    "state": "NY",
    "country": "USA"
  }
}

Spark may infer:

customer_id: integer
name: string
address: struct
    city: string
    state: string
    country: string

We can flatten the structure:

from pyspark.sql.functions import col
df = df.select(
    "customer_id",
    "name",
    col("address.city").alias("city"),
    col("address.state").alias("state"),
    col("address.country").alias("country")
)

Now the result becomes:

customer_id | name | city      | state | country
--------------------------------------------------
101         | John | New York  | NY    | USA

As a result, this structure can be loaded more easily into a relational database like PostgreSQL.

Connect AWS Glue to PostgreSQL via JDBC

There are two important things required:

PostgreSQL JDBC Driver

AWS Glue needs the PostgreSQL JDBC driver uploaded to S3 and referenced in the job configuration. You can download the official driver from the PostgreSQL JDBC Driver download page.

The JDBC URL generally follows this format:

jdbc:postgresql://hostname:5432/database_name

For example:

jdbc:postgresql://my-postgres-server:5432/customerdb

Database Credentials

Do not hard-code credentials in your Glue job. AWS recommends using AWS Secrets Manager for secure credential storage.

Instead, use a secure mechanism such as:

• AWS Secrets Manager
• Glue Connection
• IAM-supported authentication where applicable

Instead, a production pipeline should avoid hardcoded credentials entirely.

username = "admin"
password = "password123"

9. Write Transformed Data to PostgreSQL via JDBC

Once the DataFrame is ready, we can write it using JDBC.

jdbc_url = "jdbc:postgresql://my-postgres-server:5432/customerdb"
properties = {
    "user": "myuser",
    "password": "mypassword",
    "driver": "org.postgresql.Driver"
}
df.write \
    .mode("append") \
    .jdbc(
        url=jdbc_url,
        table="customer",
        properties=properties
    )

The basic flow is:

S3 JSON
   ↓
Spark DataFrame
   ↓
Transformation
   ↓
Validation
   ↓
JDBC
   ↓
PostgreSQL

10. Append vs Overwrite

The write mode is extremely important.

Append

df.write \
    .mode("append") \
    .jdbc(...)

Existing data is retained and new records are inserted.

Use this when performing incremental loads.

Overwrite

df.write \
    .mode("overwrite") \
    .jdbc(...)

Existing target data can be replaced.

This can be useful for a full refresh, but it should be used carefully in production.

11. Full Migration

Suppose we have:

S3
 |
 +-- customer/
      |
      +-- customer_01.json
      +-- customer_02.json
      +-- customer_03.json

The full migration process is:

Step 1
Read all JSON files
        ↓
Step 2
Validate schema
        ↓
Step 3
Clean and transform data
        ↓
Step 4
Remove duplicates
        ↓
Step 5
Validate record counts
        ↓
Step 6
Load PostgreSQL
        ↓
Step 7
Validate target data

For example:

df = spark.read.schema(schema).json(
    "s3://my-data-bucket/customer/"
)
df = df.dropDuplicates(["customer_id"])
df = df.filter(
    col("customer_id").isNotNull()
)
df.write \
    .mode("append") \
    .jdbc(
        url=jdbc_url,
        table="customer",
        properties=properties
    )

12. Incremental Migration

In a real production environment, you typically do not want to migrate all JSON files on every run. AWS Glue supports job bookmarks to track which files have already been processed.

Instead:

New JSON Files
      ↓
     S3
      ↓
  AWS Glue
      ↓
Only New Records
      ↓
 PostgreSQL

For example:

Day 1
customer_01.json
customer_02.json
Day 2
customer_03.json
customer_04.json

On Day 2, we should ideally process only:

customer_03.json
customer_04.json

In other words, rather than processing all four files again, only the new files are processed.

Fortunately, AWS Glue can maintain job state/bookmarks to support incremental processing.

13. Handling Duplicate Records

Suppose the same customer appears in multiple JSON files.

File 1:
customer_id = 101
File 2:
customer_id = 101

We can remove duplicates within the incoming dataset using dropDuplicates before writing to PostgreSQL.

df = df.dropDuplicates(["customer_id"])

However, this does not by itself guarantee uniqueness in the target PostgreSQL table.

Therefore, for production migration, you may need a more sophisticated deduplication strategy.

Conceptually:

Incoming Data
      ↓
Check customer_id
      ↓
 ┌────┴─────┐
 ↓          ↓
Exists?    New?
 ↓          ↓
UPDATE     INSERT

PostgreSQL can support an upsert using INSERT ... ON CONFLICT.

For example:

INSERT INTO customer
    (customer_id, customer_name, email, country)
VALUES
    (101, 'John', 'john@example.com', 'USA')
ON CONFLICT (customer_id)
DO UPDATE SET
    customer_name = EXCLUDED.customer_name,
    email = EXCLUDED.email,
    country = EXCLUDED.country;

For large migrations, however, you should carefully design the staging and merge strategy rather than executing one SQL statement per Spark record.

14. Recommended Staging Approach

For larger migrations, a staging table is often a better design. First, AWS Glue loads data into the staging table. Next, you validate and merge the records into the final target.

             S3 JSON
                |
                v
          AWS Glue / PySpark
                |
                v
       PostgreSQL Staging
                |
                v
        Validation / Merge
                |
                v
       PostgreSQL Target

For example:

customer_staging
        |
        | MERGE / UPSERT
        ↓
customer

The staging table can be used for:

• Data validation
• Duplicate detection
• Reconciliation
• Error handling
• Upsert processing

Ready to build your own migration pipeline? Follow the steps in this guide, adapt them to your data volumes and schema complexity, and treat every migration as a production-grade data pipeline from day one.

15. Data Validation After JSON Migration

Never assume a successful AWS Glue job means perfectly clean data in PostgreSQL. Always validate.

You must validate the data after every migration run.

Source Count

source_count = df.count()
print("Source count:", source_count)

Target Count

Run:

SELECT COUNT(*)
FROM customer;

You can compare:

Source Records
      =
Target Records

For incremental migration, compare only the batch being migrated.

Other useful checks include:

✓ Record count
✓ Null values
✓ Duplicate records
✓ Primary keys
✓ Data types
✓ Date values
✓ Numeric values
✓ Business rules

16. Reconciliation Framework

A simple reconciliation table can be maintained:

+------------+--------------+--------------+----------+
| Batch ID   | Source Count | Target Count | Status   |
+------------+--------------+--------------+----------+
| BATCH_001  | 10000        | 10000        | SUCCESS  |
| BATCH_002  | 12500        | 12498        | FAILED   |
+------------+--------------+--------------+----------+

As a result, this makes production support much easier.

17. Handling Bad Records

Not every JSON record will be valid. Some records will have null required fields or incorrect data types.

Example:

{
  "customer_id": null,
  "customer_name": "John"
}

Instead of loading it directly into PostgreSQL, separate invalid records.

valid_df = df.filter(
    col("customer_id").isNotNull()
)
reject_df = df.filter(
    col("customer_id").isNull()
)

Then:

                 JSON
                   |
                   v
              AWS Glue
                   |
            +------+------+
            |             |
            v             v
        Valid Data    Invalid Data
            |             |
            v             v
      PostgreSQL       S3 Reject

The rejected records can be stored in:

s3://my-data-bucket/reject/customer/

18. Performance Optimization for Large JSON Migrations

When migrating large JSON datasets to PostgreSQL, performance and scalability become critical considerations.

Avoid Small Files

Thousands of tiny JSON files create unnecessary overhead. Combine small files before migration wherever possible.

Prefer reasonably sized files.

Control Spark Partitions

You can use:

df = df.repartition(8)

However, don’t blindly choose a partition number. It should depend on:

• Data size
• Glue worker configuration
• PostgreSQL capacity
• Network bandwidth
• Number of database connections

Be Careful With JDBC Parallelism

Spark can write JDBC data in parallel, but uncontrolled parallelism will overwhelm your PostgreSQL instance.

For example:

df.write \
    .option("numPartitions", "8") \
    .jdbc(...)

But more parallelism does not always mean better performance.

If PostgreSQL can comfortably handle only 4–8 simultaneous connections, limit Spark’s JDBC parallelism to match.

A good approach is:

Spark Parallelism
       ↓
Controlled JDBC Connections
       ↓
PostgreSQL

In other words, rather than simply increasing the number of partitions, match parallelism to what PostgreSQL can handle.

19. Network Configuration

If PostgreSQL runs inside a private VPC, you must configure AWS Glue to connect through that VPC.

Typical architecture:

AWS Glue
    |
    | VPC
    |
    +--------> PostgreSQL

You may need to configure:

• VPC
• Subnets
• Security Groups
• Route tables
• DNS
• NAT Gateway where required

In short, the AWS Glue job must be able to establish a TCP connection to the PostgreSQL host and port.

20. Production-Ready AWS Glue Architecture

In practice, a more realistic production architecture includes several additional supporting services.

                    Source System
                         |
                         v
                  JSON Files
                         |
                         v
                    Amazon S3
                         |
                         v
                 AWS Glue Workflow
                         |
                         v
                  AWS Glue PySpark
                    /          \
                   /            \
                  v              v
             Valid Data      Reject Data
                  |              |
                  v              v
          PostgreSQL Staging   S3 Reject
                  |
                  v
           Validation / Merge
                  |
                  v
          PostgreSQL Target
                  |
                  v
             Applications

Supporting services can include:

AWS Secrets Manager → Credentials
CloudWatch           → Monitoring
EventBridge          → Scheduling/Triggers
AWS Glue Catalog     → Metadata
SNS                  → Notifications

21. Common Challenges

During JSON-to-PostgreSQL migration, some common issues are:

1. JSON schema changes

Today’s JSON:

{
  "customer_id": 101,
  "name": "John"
}

Tomorrow:

{
  "customer_id": 101,
  "name": "John",
  "phone": "123456789"
}

Your pipeline should have a strategy for handling schema evolution.

2. Nested JSON

Complex nested structures need to be flattened or mapped into relational tables.

3. Data type mismatches

For example:

JSON:
customer_id = "101"
PostgreSQL:
customer_id INTEGER

You need an explicit conversion:

df = df.withColumn(
    "customer_id",
    col("customer_id").cast("int")
)

4. Duplicate records

Duplicates must be handled before loading the target.

5. Database performance

Sending too many parallel JDBC connections can overload PostgreSQL.

6. Bad records

Invalid records should be redirected to a reject location instead of causing the entire batch to fail.

22. End-to-End PySpark Example

Here is a simplified production-style example:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date
from pyspark.sql.types import (
    StructType,
    StructField,
    IntegerType,
    StringType
)
spark = SparkSession.builder \
    .appName("JSON_to_PostgreSQL") \
    .getOrCreate()
# Source
source_path = "s3://my-data-bucket/customer/"
# Schema
schema = StructType([
    StructField("customer_id", IntegerType(), True),
    StructField("customer_name", StringType(), True),
    StructField("email", StringType(), True),
    StructField("country", StringType(), True),
    StructField("created_date", StringType(), True)
])
# Read JSON
df = spark.read \
    .schema(schema) \
    .json(source_path)
# Transformation
df = df.withColumn(
    "created_date",
    to_date(col("created_date"), "yyyy-MM-dd")
)
# Valid records
valid_df = df.filter(
    col("customer_id").isNotNull()
)
# Remove duplicates
valid_df = valid_df.dropDuplicates(
    ["customer_id"]
)
# PostgreSQL connection
jdbc_url = "jdbc:postgresql://hostname:5432/customerdb"
properties = {
    "user": "username",
    "password": "password",
    "driver": "org.postgresql.Driver"
}
# Load PostgreSQL
valid_df.write \
    .mode("append") \
    .jdbc(
        url=jdbc_url,
        table="customer",
        properties=properties
    )
print("Migration completed successfully")

Note: In a real production implementation, always retrieve credentials from AWS Secrets Manager and never hardcode them.

Best Practices for AWS Glue JSON to PostgreSQL Migration

For a production-grade JSON → PostgreSQL migration, follow these practices:

1. Use explicit schemas where practical.
2. Do not hard-code credentials.
3. Validate source and target counts.
4. Implement reject/error handling.
5. Use incremental processing instead of full reloads when appropriate.
6. Handle duplicates explicitly.
7. Use staging tables for complex migrations.
8. Control JDBC parallelism.
9. Monitor Glue jobs using CloudWatch.
10. Maintain batch-level reconciliation information.
11. Plan for JSON schema evolution.
12. Test with production-like data volumes.

24. Key Interview Question

How would you migrate millions of JSON records from S3 to PostgreSQL?

A good answer would be:

“I would use AWS Glue with PySpark to read JSON files from S3, apply an explicit schema, perform transformations and data-quality checks, and write the valid records to a PostgreSQL staging table using JDBC. I would then perform validation and merge the staged data into the target tables. For incremental processing, I would use Glue job bookmarks or another reliable ingestion-control mechanism to avoid reprocessing files. I would also implement reject handling, source-to-target reconciliation, monitoring, and controlled JDBC parallelism to prevent PostgreSQL from being overloaded.”

Final Takeaway: Treat Your AWS Glue Migration as a Data Pipeline

The migration is not simply:

S3 → PostgreSQL

A production-grade solution is:

JSON
  ↓
Amazon S3
  ↓
AWS Glue
  ↓
PySpark
  ↓
Schema Validation
  ↓
Transformation
  ↓
Data Quality
  ↓
Reject Handling
  ↓
PostgreSQL Staging
  ↓
Validation / Upsert
  ↓
PostgreSQL Target
  ↓
Reconciliation & Monitoring

The key idea is to treat migration as a data pipeline, not just a file transfer. This approach makes your solution scalable, auditable, restartable, and easier to support in production. Ready to build yours? Start with the AWS Glue Developer Guide and apply the patterns from this post to your own data. Have questions? Drop them in the comments below.