SmartQueryTools

Coalesce Columns in JSON Files Online

Fill null values in JSON files from other columns — return the first non-null value across up to 8 columns in priority order. Add the result as a new column. Runs entirely in your browser.

How to coalesce Columns in JSON files

  1. Drop your file onto the upload area. The first two columns are placed in slots 1 and 2 as a starting point, and the first 200 rows are shown.
  2. Set the columns in priority order. Slot 1 is checked first, then slot 2, and so on. Use "+ Add column" for up to 8 columns, or the cross to remove one.
  3. Name the output column. The default is "coalesced".
  4. Click Coalesce Columns. The new column is added at the end and every original column is kept.
  5. Check the preview and download the file in the same format you uploaded.

Your file is processed locally in your browser and is never uploaded. The free limit is 50 MB per file; larger files work if your device has the memory for them.

Worked example

A sports club membership list has three phone columns filled in unevenly. The coach needs one phone number per member for a text alert, preferring mobile, then work, then home.

Input (JSON)

[
  {
    "member_id": "M-01",
    "mobile": "021 555 0142",
    "work_phone": null,
    "home_phone": "09 555 7710"
  },
  {
    "member_id": "M-02",
    "mobile": null,
    "work_phone": "04 555 3301",
    "home_phone": "04 555 9120"
  },
  {
    "member_id": "M-03",
    "mobile": null,
    "work_phone": null,
    "home_phone": "03 555 6604"
  },
  {
    "member_id": "M-04",
    "mobile": null,
    "work_phone": null,
    "home_phone": null
  }
]

Settings

  • Columns (in priority order): 1. mobile, 2. work_phone, 3. home_phone
  • Output column name: contact_phone

Result

member_idmobilework_phonehome_phonecontact_phone
M-01021 555 0142NULL09 555 7710021 555 0142
M-02NULL04 555 330104 555 912004 555 3301
M-03NULLNULL03 555 660403 555 6604
M-04NULLNULLNULLNULL

Each row takes the first phone number that is present, checking mobile, then work_phone, then home_phone. M-01 has a home number too, but mobile wins because it is first. M-02 has no mobile, so the work number is used. M-04 has no numbers at all, so contact_phone is empty. The three source columns are left as they were.

Working with JSON files

In JSON there are two ways for a value to be missing: the key is absent, or it is present with null. Both load as NULL and both are skipped. An empty string "" is different: it is a real value and will be chosen over later keys. Clean empty strings first if your API uses them for missing data. An empty array [] or empty object {} is also a value, not a missing one. JSON keys can also change type from one record to the next, so check the type shown beside each key in the slot pickers before running.

Only top-level keys appear in the slot pickers. To coalesce contact.mobile with contact.work, flatten the JSON so each nested field is its own column. Numbers and strings cannot be mixed in one chain, so an id sent as 123 in one key and "123" in another needs a cast first. The output array adds the new key to the end of every object, with null where no key had a value.

Frequently Asked Questions

Does coalesce treat a missing JSON key the same as null?

Yes. A key that is absent from an object and a key set to null both load as NULL, and both are skipped.

Are empty strings in JSON skipped?

No. "" is a value, so it is returned if it comes first in the chain. Replace empty strings with null first if they mean missing.

How many columns can I coalesce at once?

Between 2 and 8. The button stays disabled until at least two slots have a column picked.

Does coalesce change the original columns?

No. It adds one new column at the end and leaves every original column untouched. Drop the source columns afterwards with Manage Columns if you no longer need them.

Can I put a default value at the end of the chain?

Not in this tool, which only accepts columns. Run Fill Empty Values on the coalesced column afterwards to replace the remaining empty cells with a fixed value such as "unknown".

Related Tools

Fill Empty Values in JSON Files Online

Fill empty and null values in JSON files with a custom replacement value, directly in your browser.

Manage Columns in JSON Files Online

Drop or select specific columns from JSON files directly in your browser. No upload required.

Validate JSON Files Online

Validate JSON file structure in your browser. Check null counts, distinct values, and data types for every column — no upload required.

Coalesce Columns in CSV Files Online

Fill null values in CSV files from other columns — return the first non-null value across up to 8 columns in priority order. Add the result as a new column. Runs entirely in your browser.

Coalesce Columns in Excel Files Online

Fill null values in Excel files from other columns — return the first non-null value across up to 8 columns in priority order. Add the result as a new column. Runs entirely in your browser.

Coalesce Columns in Parquet Files Online

Fill null values in Parquet files from other columns — return the first non-null value across up to 8 columns in priority order. Add the result as a new column. Runs entirely in your browser.

JSON Viewer Online

View and inspect JSON files directly in your browser. Browse rows, check column names and data types — no upload required, your data stays on your device.

Convert JSON to Parquet Online

Convert JSON files to Parquet format directly in your browser. No upload required — your data never leaves your device.