Using JSON Formatters for Data Migration Projects

JSON formatters are useful in data migration projects for a reason that goes far beyond pretty-printing. They help you inspect, validate, normalize, and reshape source payloads before those records hit the target system. In practice, that is what separates a migration that merely loads from one that loads correctly.

Most migration failures are not caused by invalid JSON syntax. They come from data that is technically valid JSON but semantically wrong for the destination: IDs stored as strings, timestamps in mixed time zones, optional fields that switch between missing and `null`, or nested arrays that do not fit the target model. A formatter makes those problems visible early.

Where JSON Formatters Help Most

For migration work, a useful JSON formatting step should help you answer four questions quickly:

• Is the payload valid and complete? Confirm the source can actually be parsed and contains the fields the target requires.
• What needs normalization? Standardize key names, enums, timestamps, empty strings, and placeholder values before loading.
• What needs reshaping? Flatten nested objects, split arrays, and remove source-only metadata so the data matches the target contract.
• Which records should be rejected? Quarantine bad records instead of silently forcing them into the destination.

A Practical Migration Workflow

1. Define the target contract first

Start with the destination, not the source. Decide which fields are required, which values are allowed, how dates should be formatted, whether extra properties are permitted, and how `null` should behave. For JSON-native pipelines, JSON Schema Draft 2020-12 is the current general-use metaschema and a solid way to version those expectations.

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "required": ["id", "fullName", "email", "createdAt"],
  "properties": {
    "id": { "type": "integer" },
    "fullName": { "type": "string", "minLength": 1 },
    "email": { "type": "string", "format": "email" },
    "createdAt": { "type": "string", "format": "date-time" },
    "status": {
      "type": "string",
      "enum": ["active", "disabled"]
    },
    "city": { "type": ["string", "null"] }
  },
  "additionalProperties": false
}

2. Profile real source data, not just sample payloads

Inspect a representative slice of production-like records before you write mappings. This is where a JSON formatter earns its keep because you can expand nested objects, compare records side by side, and quickly spot inconsistent structure.

• Measure how often a field is missing versus explicitly set to `null`.
• Check whether IDs, amounts, or booleans arrive as strings in some records.
• Find legacy field names that need to map into one canonical key.
• Identify which nested arrays must become child rows or secondary load files.

3. Normalize before you reshape

Simple cleanup should happen before structural transformation. Trim strings, normalize casing, convert timestamps to one standard format, map status codes into destination enums, and be explicit about which values become `null`, empty strings, or hard failures.

type SourceUser = {
  user_id: string | number;
  user_name?: string | null;
  email?: string | null;
  status?: "A" | "D" | null;
  created_at?: string | null;
  address?: {
    city?: string | null;
  } | null;
};

type TargetUser = {
  id: number;
  fullName: string;
  email: string;
  status: "active" | "disabled";
  createdAt: string;
  city: string | null;
};

function transformUser(source: SourceUser): TargetUser | null {
  const id = Number(source.user_id);
  if (!Number.isInteger(id)) return null;

  const fullName = source.user_name?.trim();
  const email = source.email?.trim().toLowerCase();
  if (!fullName || !email) return null;

  const createdAtValue = source.created_at ? new Date(source.created_at) : null;
  if (!createdAtValue || Number.isNaN(createdAtValue.getTime())) return null;

  return {
    id,
    fullName,
    email,
    status: source.status === "D" ? "disabled" : "active",
    createdAt: createdAtValue.toISOString(),
    city: source.address?.city?.trim() || null,
  };
}

4. Restructure for the target system

Structural changes are where migration logic becomes easy to underestimate. A formatter helps you preview how a nested document will look after flattening and whether the destination needs one row, multiple child rows, or a raw landing column plus curated downstream models.

• Nested arrays like line items, addresses, or events often need separate child tables.
• Source-only metadata should be dropped unless you need it for audit or replay.
• Reference lookups are easier to resolve before the final load than after it.

5. Validate, batch, and reconcile

Run validation after transformation, process data in batches, and compare counts between source records, transformed output, quarantined rows, and successful loads. Without reconciliation, a migration can appear successful while still losing data silently.

• Keep record-level error logs with the source identifier and failure reason.
• Dry-run on a representative slice before the final cutover.
• Diff a sample of transformed records to catch accidental field loss.

Common Problems a Formatter Can Reveal

• Numeric precision issues: Large IDs and money values can change meaning if tooling silently treats them as floating-point numbers.
• `null` versus missing fields: {"phone": null} is not always equivalent to a missing `phone` key, especially when the target applies defaults.
• Duplicate keys: Some parsers keep only the last value, which can hide bad exports or broken upstream serializers.
• Date drift: Mixing local timestamps and UTC timestamps creates hard-to-debug cutover issues.
• Memory blowups: Large JSON arrays should usually be streamed or processed in chunks instead of parsed into one in-memory object.

Target-Specific Caveats

PostgreSQL `jsonb`: PostgreSQL's `jsonb` type does not preserve whitespace, object key order, or duplicate object keys, and SQL `NULL` is distinct from JSON `null`. If your migration depends on key ordering or repeated keys, fix that before load instead of assuming the database will preserve them.

Relational targets: Flexible source JSON usually becomes stricter on the way in. Arrays and nested objects often need separate tables, foreign keys, and load-order planning.

Warehouses and analytics layers: Keeping raw JSON can be useful for landing zones, but reporting still depends on stable field names and consistent types.

Choosing the Right Tooling

• Formatter or CLI tool: Best for inspection, quick corrections, and lightweight batch transforms.
• Scripted transforms: Best when mappings must be versioned, reviewed, tested, and rerun.
• ETL or orchestration layers: Best when the migration needs retries, lineage, scheduling, joins across systems, or idempotent reruns.

The usual answer is a combination: use a formatter to understand and spot-check the data, then move repeatable transformation logic into code or your migration pipeline.

Migration Checklist

• Freeze the target contract before writing transformation logic.
• Profile real records and document every known source variation.
• Normalize types, timestamps, and enums before structural mapping.
• Validate transformed output, not just raw source payloads.
• Batch the load and keep rejected rows with explicit failure reasons.
• Reconcile counts and sample diffs before sign-off.

Conclusion

JSON formatters are most valuable in migration work when they are treated as inspection and normalization tools, not just pretty-printers. Use them to define the contract, expose source irregularities, reshape data deliberately, and reject bad records early. That turns flexible JSON into a dependable migration input.