Expand an Array or JSON Array Into One Row Per Element

Turn a list of values into individual rows — using `UNNEST` for native arrays in PostgreSQL, `JSON_TABLE` in MySQL, `OPENJSON` in SQL Server, or `json_each` in SQLite.

Created Apr 26, 2026 Last updated Apr 26, 2026 0/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Expand a list of fruit names into one row each with a position number

Each engine expands the three-element list into one row per element. PostgreSQL's UNNEST(ARRAY[...]) WITH ORDINALITY AS t(elem, ordinality) generates the element value and a 1-based position in one step. MySQL's JSON_TABLE requires a JSON string rather than a native array; FOR ORDINALITY produces the position. SQL Server's OPENJSON returns a key column that is a 0-based string index — CAST([key] AS INT) + 1 converts it to a 1-based integer. SQLite's json_each returns an integer key starting at 0; adding 1 gives the 1-based position. To expand an array stored in a table column rather than a literal, replace the literal with the column reference in UNNEST, JSON_TABLE, OPENJSON, or json_each.

PostgreSQL uses UNNEST with native array syntax. MySQL uses JSON_TABLE with FOR ORDINALITY. SQL Server uses OPENJSON with 0-based key + 1. SQLite uses json_each with 0-based key + 1. All produce the same three rows.

Useful when

Where this command helps.

expanding a JSON array of tags stored in a single column into individual rows for frequency counting or joining
unpacking an ordered list from a function result or literal array to process each element independently

Explanation

What the command is doing.

Expanding an ordered list into rows is a common need: tag lists, multi-value fields, and JSON payloads that contain arrays all benefit from being unpacked into relational rows so they can be joined, filtered, and counted. PostgreSQL supports native array columns and the UNNEST(array) set-returning function; adding WITH ORDINALITY produces a position column starting at 1. MySQL 8.0+ and MariaDB use JSON_TABLE to expand a JSON array stored as text. SQL Server uses OPENJSON, which returns key (0-based index), value, and type columns. SQLite provides json_each(json), a table-valued function that returns each element with a 0-based key. Because the engines differ in whether they support typed arrays or JSON arrays, the syntax varies significantly, but the result pattern is the same: one row per element with an optional position.

Expand an Array or JSON Array Into One Row Per Element

Expand a list of fruit names into one row each with a position number

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.