How to Flatten JSON in SQL — Postgres jsonb, MySQL JSON, and SQL Server OPENJSON

If nested JSON is sitting in a database column, flatten it in SQL — Postgres has jsonb_each and jsonb_path_query, MySQL has JSON_TABLE, SQL Server has OPENJSON, Oracle has JSON_TABLE. Running those beats pulling the data out and processing it elsewhere. This post shows the idiomatic pattern for each database, and where our browser flattener fits — mostly for design and debugging, not for production ETL.

Postgres — jsonb_each, jsonb_path_query, Arrow Operators

Postgres has the richest JSON support of any open-source database. For simple path access:

SELECT
  data->>'id' AS id,
  data->'user'->>'name' AS user_name,
  data->'user'->'address'->>'city' AS user_city
FROM events;

The -> operator returns jsonb; ->> returns text. For recursive flattening (every leaf in a column), jsonb_path_query helps:

SELECT jsonb_path_query(data, '$.** ? (@.type() == "string" || @.type() == "number")')
FROM events;

For exploding an array into rows:

SELECT id, elem
FROM events, jsonb_array_elements(data->'items') AS elem;

Postgres also supports dot notation in jsonb_path_query ($.user.address.city), which matches the conventions used by our browser flattener.

MySQL — JSON_TABLE and JSON_EXTRACT

MySQL 8.0+ has JSON_TABLE, the closest thing to a one-shot flatten-with-projection:

SELECT t.*
FROM events,
JSON_TABLE(data, '$' COLUMNS (
    id INT PATH '$.id',
    user_name VARCHAR(200) PATH '$.user.name',
    user_city VARCHAR(200) PATH '$.user.address.city'
)) AS t;

For arrays:

SELECT t.*
FROM events,
JSON_TABLE(data, '$.items[*]' COLUMNS (
    item_name VARCHAR(200) PATH '$.name',
    item_price DECIMAL(10,2) PATH '$.price'
)) AS t;

For field-level access without JSON_TABLE:

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(data, '$.user.name')) AS user_name,
  JSON_UNQUOTE(data->'$.user.address.city') AS user_city
FROM events;

MySQL 5.7 has JSON_EXTRACT but not JSON_TABLE — upgrade to 8.0 if you are doing serious JSON work.

Sell Custom Apparel — We Handle Printing & Free Shipping

SQL Server — OPENJSON and JSON_VALUE

SQL Server 2016+ has OPENJSON, similar in spirit to MySQL JSON_TABLE:

SELECT id, user_name, user_city
FROM events
CROSS APPLY OPENJSON(data) WITH (
    id INT '$.id',
    user_name NVARCHAR(200) '$.user.name',
    user_city NVARCHAR(200) '$.user.address.city'
);

For unknown-shape JSON (every top-level key becomes a row):

SELECT [key], value
FROM OPENJSON(data);

For arrays, no special syntax — OPENJSON iterates array elements when the JSON is an array:

SELECT item_name, item_price
FROM events
CROSS APPLY OPENJSON(data, '$.items') WITH (
    item_name NVARCHAR(200) '$.name',
    item_price DECIMAL(10,2) '$.price'
);

SQL Server also supports JSON_VALUE for single-value extraction, similar to Postgres ->>.

Redshift and BigQuery — Similar Patterns

Redshift (PostgreSQL-based): Supports JSON_EXTRACT_PATH_TEXT for field access. SUPER type (Redshift's JSON type) supports dot notation: data.user.name directly in SELECT.

SELECT
  data.user.name::VARCHAR AS user_name,
  data.user.address.city::VARCHAR AS user_city
FROM events;

For arrays, use UNNEST or PartiQL.

BigQuery: Supports STRUCT navigation with dot notation, plus JSON_EXTRACT for string JSON. If the column is native STRUCT type, dot notation works directly:

SELECT user.name, user.address.city FROM `project.dataset.events`;

For stringified JSON, use JSON_EXTRACT_SCALAR and JSON_EXTRACT_ARRAY. See our warehouse flatten guide for more.

Where the Browser Tool Fits

The browser tool is not a replacement for SQL JSON functions. Use it for:

Designing your column projection. Flatten a sample row in the browser to see every path. Then write your SELECT with the right path expressions. Saves iterating through EXPLAIN plans.

Inspecting a failing row. Production row breaks your ETL? Extract the raw JSON (SELECT data::TEXT), paste into the browser, flatten, find the unexpected field or null. Faster than running ad-hoc SELECT queries.

Pre-ETL sample exploration. Before the data is in your warehouse, you often receive a sample file. Flatten it to understand shape, then design your ingestion schema.

Non-DBA collaborators. Analysts who cannot run arbitrary SQL against prod can still paste a JSON sample into a browser tool.

For in-database flattening of millions of rows, always use the database function. Its query planner is optimized for the workload; a browser tool is not.

Frequently Asked Questions

Should I flatten JSON in SQL or in application code?

Flatten in SQL when the data lives in a database table and you need it for reporting or ETL. Flatten in application code when the JSON arrives from an API and you are transforming it before storing. Flatten in the browser when you are debugging a single payload.

Does Postgres jsonb store JSON in a flat form?

No — jsonb stores the parsed structure in a binary tree format for fast access. The structure is still nested; the storage format is just more efficient than plain text. When you flatten, you are transforming the logical structure, not the storage.

Why is MySQL JSON_TABLE faster than application-side flattening?

JSON_TABLE runs in the database process, close to the data. Application-side flattening requires network transfer of the raw JSON, then CPU work in the app. For millions of rows, the network and serialization costs dominate.

How do I handle deeply unknown JSON in SQL?

Use jsonb_each_recursive in Postgres or JSON_KEYS recursively in MySQL to discover paths at runtime. Or pull a sample into a browser flattener to see the paths, then write targeted SQL. The runtime-discovery approach is rarely performant at scale.