JSON Flatten

What Is JSON Flatten?

JSON Flatten is a tool that takes a nested JSON object, with its multiple levels of objects and arrays, and converts it into a single-level structure where every value is addressable by a flat key path. Instead of {"user": {"address": {"city": "London"}}}, you get {"user.address.city": "London"}. The entire hierarchy is preserved in the key names themselves; the values all sit at one level.

Nested JSON is the natural output of relational or document data modelled in modern APIs and databases such as MongoDB, PostgreSQL's JSONB columns, and Elasticsearch. Its hierarchical structure is ideal for expressing relationships between entities, but it creates a direct problem for any system that expects flat, tabular data: spreadsheets, CSV files, relational database tables, and many analytics and business intelligence tools. JSON flattening is the bridge between those two worlds.

The concept is straightforward. Given a separator character, typically a dot (.) or an underscore (_), the flattening algorithm walks every key in the input, building up a path string as it descends. When it reaches a primitive value (string, number, boolean, or null), it writes the full path as the key and the value as the value. Arrays are handled by appending the index to the path, so {"items": ["a", "b"]} becomes {"items.0": "a", "items.1": "b"}.

The MDN Web Docs reference on JavaScript object access explains the dot notation that underpins most key-path conventions. For Python users, the pandas function json_normalize() implements the same concept as documented in the pandas documentation, making it the standard method for flattening JSON before loading it into a DataFrame.

How to Use the JSON Flatten Tool

1. Paste your JSON into the input panel. This can be any valid JSON: an API response, a database export, a configuration file, or a sample payload. Multi-level nesting is handled automatically regardless of depth.
2. Choose your separator. The default is a dot (.), which produces paths like user.address.city. If your downstream system uses a different convention, such as underscore (_) for variable-safe keys or slash (/) for URL-style paths, select the appropriate separator before running the tool.
3. Click "Flatten" to run the operation. The tool processes the input and returns the flat key-value object in the output panel.
4. Review the output. Verify that the key paths are correct and the values match your expectations. Check array entries to confirm indices are appended correctly (e.g., items.0, items.1).
5. Copy the flattened JSON directly, or use your browser's download option if available. The output is valid JSON and can be passed directly to a CSV converter, loaded into a spreadsheet, or used as input to another tool.
6. Unflatten if needed. Many JSON flatten tools, including this one, support the reverse operation: taking a flat key-path object and restoring the original nested structure. This is useful when you need to reconstruct a JSON payload from flat data.

Why Use This Tool

The core problem JSON Flatten solves is incompatibility between hierarchical and tabular data models. Every team that works with both JSON APIs and flat data consumers encounters this problem regularly, and solving it by hand is tedious and error-prone.

Data engineers loading JSON into data warehouses such as BigQuery, Redshift, or Snowflake need flat key-value pairs before they can map fields to table columns. Analysts who receive JSON exports from SaaS products and need to open them in Excel or Google Sheets face the same requirement: spreadsheets have no concept of nested keys. Business intelligence tools such as Tableau and Looker connect to flat tables, not nested objects.

Developers also use flattening as a debugging and inspection technique. A flat representation makes it immediately obvious what keys are present, what their full paths are, and what values they hold, without needing to expand a tree view. If two JSON objects need to be compared field by field, comparing two flat maps is far simpler than visually diffing two nested structures.

Python users building data pipelines with pandas will recognise json_normalize() as their standard tool for this transformation. The JSON Flatten tool provides the same result in a browser, with no code required, making it accessible to analysts and non-developers who work with JSON data regularly.

Real-World Use Cases

Data analyst loading API exports into a spreadsheet

A marketing analyst at a SaaS company regularly downloads campaign performance data from a third-party advertising platform whose export format is a nested JSON file. The file contains campaign objects with nested ad group objects, each containing nested metric objects with impressions, clicks, and spend. To load this into a Google Sheet for weekly reporting, the analyst pastes the JSON into the JSON Flatten tool, uses underscore as the separator to produce variable-safe column names, copies the flattened output, and passes it to a JSON-to-CSV converter. The entire process takes under two minutes and requires no Python or SQL.

Back-end developer mapping API response fields to a database schema

A developer integrating a third-party logistics provider's API needs to map the deeply nested shipment response object to a flat PostgreSQL table. Rather than counting nesting levels by eye in the raw JSON, the developer flattens a sample response in the tool to get a clear list of all field paths and their values. This list becomes the reference for writing the SQL column mapping, and any fields with unexpected nesting depth are caught immediately rather than surfacing as bugs in production.

Data engineer validating a Pandas transformation pipeline

An analytics engineer at an e-commerce company uses Python's pandas.json_normalize() to flatten Shopify order exports before loading them into a Redshift data warehouse. Before writing the pipeline script, she uses the JSON Flatten tool to visually verify what the flat output should look like for a representative order. This confirms the expected column names and helps her identify which nested arrays will produce multiple rows per order, informing the pipeline's join logic before a single line of code is written.

QA engineer comparing API response structures across environments

A QA engineer needs to confirm that the staging and production API responses for a user profile endpoint have identical structure. By flattening both responses and comparing the resulting flat key lists side by side, she can spot added, removed, or renamed fields instantly. This technique is faster and more reliable than visually comparing two nested JSON trees, and it produces a diff that can be committed to a test repository as a contract snapshot.

Common Mistakes and Troubleshooting

1. Choosing the wrong separator for your downstream system

The dot separator produces paths like user.address.city, which is standard but conflicts with any downstream system that interprets dots in key names as nested access (such as Elasticsearch, which uses dot notation for nested field paths). In those cases, use an underscore or a custom separator that does not appear in any of your existing key names. Check the output for any unintended conflicts before passing flattened data to a production system.

2. Key name collisions after flattening

If your JSON has a key at the top level named user_name and also a nested key at user.name, flattening with underscore as the separator will produce two entries both named user_name, with the second silently overwriting the first. This is a data loss scenario. To detect it, compare the number of keys in the flattened output against the expected count. If they do not match, switch to a longer or less common separator.

3. Arrays with mixed element types

Standard JSON flattening assigns numeric indices to array elements: items.0, items.1, and so on. If the array contains a mix of objects and primitive values, the flattened keys will have inconsistent structures (some paths will end at a leaf value, others will continue with further nested keys). This is valid behaviour, but many CSV or table-based consumers cannot accommodate it. Consider pre-processing arrays of objects into separate tables before flattening if your destination requires a strictly uniform schema.

4. Deeply nested objects causing extremely long key paths

JSON structures returned by Salesforce, HubSpot, or SAP APIs can be nested six or more levels deep, producing flattened keys that are 80 or more characters long. While the flattening itself is correct, extremely long column names may be truncated or rejected by certain databases (PostgreSQL has a 63-byte limit on identifier length) and spreadsheet applications. If long key paths are a problem, use a partial-flatten approach that stops at a defined maximum depth.

5. Expecting null values to disappear

By default, most JSON flatten implementations, including this tool, preserve null values and include them in the output with their full key path. A key that holds null is still a key. If your downstream system cannot handle null values in certain columns, filter them out after flattening rather than before, so you retain visibility of which fields exist but hold no value.

6. Treating flattening as lossless when arrays are involved

Flattening and then unflattening a JSON object that contains arrays is lossless only if the unflattener correctly reconstructs the array from indexed keys. However, if the flattened data is subsequently exported to CSV and imported from CSV, the index information embedded in key names (such as items.0.name) will be treated as literal strings, not as path instructions. Roundtripping JSON through CSV is lossy for arrays and should only be used when the destination system will consume the flat structure directly without attempting to reconstruct the original hierarchy.