Compare a CSV and a JSON File by Key: Nested Fields, Types and Missing Values

An API returns JSON. The database export is a CSV. They should hold the same customers, and you need to find the ones that do not. The shapes do not match: JSON nests, CSV is flat, JSON has real types and null, CSV has only text. This guide shows how to compare the two by key and read the result correctly.

Open the Data Comparison

The mismatch to expect

// customers.json
[{"id": 1, "name": "Ada", "active": true, "address": {"city": "London"}, "phone": null}]

// customers.csv
id,name,active,address.city,phone
1,Ada,true,London,

These are the same record. A line diff says every line is different. A comparison by key says nothing changed.

How the two are lined up

  • **Nested objects become columns.** `address.city` in the JSON matches the `address.city` column in the CSV. If your CSV has `address_city` instead, columns that differ only by punctuation are matched, and the page lists the match.
  • **Types are compared by meaning.** `true` equals the text `true`, `42` equals `42.0`, and a date string equals the same date.
  • **Null equals blank.** A JSON `null` and an empty CSV cell are the same.
  • **Arrays stay whole.** A list inside a record is compared as one value, so reordering it counts as a change.

Step by step

  • Open Data Comparison. Load the JSON as A and the CSV as B (or the reverse).
  • Choose `id` as the key.
  • Read **only in A** and **only in B** first: these are the records missing on one side.
  • Then read the changed records. Each shows the columns that differ and the old and new value.
  • Download the result and hand it to whoever owns the source.

Common causes of real differences

  • A record exists in the API but not yet in the export, because of timing.
  • A field was renamed on one side.
  • A value was trimmed or upper-cased by one system. Try **ignore spaces** and **ignore case** to see whether that explains it.
  • A number was rounded. Set a number tolerance.
  • Duplicate ids. The page reports duplicate keys; add a second key column to separate them.

When this is the wrong tool

If you want to see which lines changed in a JSON configuration file, a text diff is the right tool. Comparing by key is for lists of records. For a JSON file against another JSON file, see compare two JSON files.

Open the Data Comparison

Related tool pages

  • Compare Two CSV Files: Compare two CSV or Excel files by a key column and see added, removed and changed rows. Free, runs in your browser.
  • Compare Two Excel Files: Compare two Excel (.xlsx) files by a key column and see every added, removed and changed row and cell. Free, and your files stay in your browser.
  • Compare Two Parquet Files: Compare two Parquet files by a key column and see every added, removed and changed row and cell. Free, runs in your browser, nothing is uploaded.
  • Compare Two JSON Files: Compare two JSON files by a key field and see which records were added, removed or changed, down to the nested field. Free, in your browser.
  • Compare Two Avro Files: Compare two Avro files by a key field and list the added, removed and changed records. Reads the embedded schema, runs in your browser. Free.
  • Compare CSV with Parquet: Check that a Parquet file matches the CSV it was made from: find rows and values that differ after a conversion or migration. Free, in your browser.

Also try

How to use the Data Comparison: step by step

  1. Load the JSON and the CSV.
  2. Choose the key.
  3. Review the records in only one file.
  4. Review changed values and download.

When to use the Data Comparison

  • Checking an API response against a database export
  • Finding records missing from one side
  • Comparing nested JSON with flat CSV

Example

Input: before.csv + after.csv, key = id

Output: Added 3 · Removed 2 · Changed 5

Frequently asked questions

How do I compare a JSON file with a CSV file?

Load both in Data Comparison, choose a key such as id and compare. Nested JSON fields are flattened into columns and values are compared by meaning.

Does it handle nested JSON?

Yes. Nested objects become columns like address.city. Arrays inside a record are compared as a whole value.

Does a JSON null match an empty CSV cell?

Yes. Null and blank are treated as the same value.

What if my CSV uses address_city instead of address.city?

Columns that differ only by case or punctuation are matched, and the page tells you which ones were matched.

Tip: Start with the records in only one file. Missing records are usually the cause of most of the rest.