How to flatten nested JSON in ELT: step by step

JSON is the most common format for receiving data from APIs, applications and logs, but it rarely arrives ready for analysis. In an ELT approach you load the JSON exactly as it comes and only transform it later, already inside the data warehouse. SQL Server's OPENJSON function lets you flatten nested JSON and turn it into relational columns ready to query, with no external tools.

Prerequisites

• SQL Server 2016 or later, or Azure SQL Database (compatibility level 130 or higher).
• Permissions to create tables and run queries on a staging database.
• A sample JSON payload (we use a simple one throughout the guide).
• Basic T-SQL knowledge: SELECT, INSERT and CREATE TABLE.

Step 1: Load the raw JSON (the "L" in ELT)

In ELT, the first step is to land the data exactly as it arrives, without changing it. Create a staging table with an NVARCHAR(MAX) column to hold the whole JSON document, plus a load timestamp. This preserves the source and lets you reprocess later if the business rules change.

CREATE TABLE stg.PedidosRaw (
    id_carga     INT IDENTITY(1,1) PRIMARY KEY,
    payload      NVARCHAR(MAX) NOT NULL,
    carregado_em DATETIME2 DEFAULT SYSUTCDATETIME()
);

INSERT INTO stg.PedidosRaw (payload)
VALUES (N'{
  "pedido_id": 1024,
  "cliente": { "nome": "Ana Dias", "pais": "PT" },
  "linhas": [
    { "sku": "A-1", "qtd": 2, "preco": 9.90 },
    { "sku": "B-7", "qtd": 1, "preco": 19.50 }
  ]
}');

Step 2: Read top-level fields with OPENJSON

Before flattening, confirm the text is valid JSON with ISJSON and read the first-level fields. OPENJSON with a WITH clause maps JSON paths to typed columns — notice how $.cliente.nome reaches a nested value effortlessly.

SELECT j.pedido_id, j.cliente_nome, j.cliente_pais
FROM stg.PedidosRaw AS r
CROSS APPLY OPENJSON(r.payload)
WITH (
    pedido_id     INT           '$.pedido_id',
    cliente_nome  NVARCHAR(100) '$.cliente.nome',
    cliente_pais  NVARCHAR(2)   '$.cliente.pais'
) AS j
WHERE ISJSON(r.payload) = 1;

Step 3: Expand the nested array (one row per element)

The linhas array has several items. To get one row per item, apply OPENJSON a second time over the array. Chaining CROSS APPLY joins the order header to each line, flattening the parent-child relationship.

SELECT
    cab.pedido_id,
    cab.cliente_nome,
    linha.sku,
    linha.qtd,
    linha.preco
FROM stg.PedidosRaw AS r
CROSS APPLY OPENJSON(r.payload)
WITH (
    pedido_id    INT           '$.pedido_id',
    cliente_nome NVARCHAR(100) '$.cliente.nome',
    linhas       NVARCHAR(MAX) '$.linhas' AS JSON
) AS cab
CROSS APPLY OPENJSON(cab.linhas)
WITH (
    sku   NVARCHAR(20)  '$.sku',
    qtd   INT           '$.qtd',
    preco DECIMAL(10,2) '$.preco'
) AS linha;

The AS JSON keyword tells OPENJSON that linhas is a JSON fragment, allowing it to be opened by the second OPENJSON. Without it — and without the column being NVARCHAR(MAX) — you get an error or null values.

Step 4: Materialise the clean layer (the "T")

Finally, write the flattened result into a clean (silver) table, so downstream models query columns instead of JSON. A simple INSERT ... SELECT reuses the query from the previous step.

CREATE TABLE clean.PedidoLinhas (
    pedido_id    INT,
    cliente_nome NVARCHAR(100),
    sku          NVARCHAR(20),
    qtd          INT,
    preco        DECIMAL(10,2)
);

INSERT INTO clean.PedidoLinhas (pedido_id, cliente_nome, sku, qtd, preco)
SELECT cab.pedido_id, cab.cliente_nome, linha.sku, linha.qtd, linha.preco
FROM stg.PedidosRaw AS r
CROSS APPLY OPENJSON(r.payload)
WITH (
    pedido_id    INT           '$.pedido_id',
    cliente_nome NVARCHAR(100) '$.cliente.nome',
    linhas       NVARCHAR(MAX) '$.linhas' AS JSON
) AS cab
CROSS APPLY OPENJSON(cab.linhas)
WITH (
    sku   NVARCHAR(20)  '$.sku',
    qtd   INT           '$.qtd',
    preco DECIMAL(10,2) '$.preco'
) AS linha;

Check the result

Query the clean table to confirm the flatten.

SELECT * FROM clean.PedidoLinhas;

You should get two rows for order 1024 — A-1 (qty 2, price 9.90) and B-7 (qty 1, price 19.50) — with qtd and preco already as numbers, not text. If both rows appear with the correct types, the flatten worked. A common mistake is forgetting AS JSON in Step 3: without it, the array does not open and the column stays NULL.

Conclusion

With OPENJSON and CROSS APPLY you go from a nested JSON document to a clean relational table in just a few steps, keeping the ELT pattern: load first, transform later. As a next step, wrap this logic in a stored procedure and process only new loads to save time. What is the trickiest JSON you have ever had to flatten?