Como achatar JSON aninhado em ELT: passo a passo

O JSON é o formato mais comum para receber dados de APIs, aplicações e logs, mas raramente chega pronto para análise. Numa abordagem ELT carrega-se o JSON tal como vem e só depois se transforma, já dentro do data warehouse. A função OPENJSON do SQL Server permite achatar JSON aninhado e convertê-lo em colunas relacionais prontas a consultar, sem ferramentas externas.

Pré-requisitos

• SQL Server 2016 ou superior, ou Azure SQL Database (com nível de compatibilidade 130 ou superior).
• Permissões para criar tabelas e executar consultas numa base de dados de staging.
• Um exemplo de payload JSON (usamos um simples ao longo do guia).
• Noções básicas de T-SQL: SELECT, INSERT e CREATE TABLE.

Passo 1: Carregar o JSON em bruto (o "L" do ELT)

No ELT, a primeira etapa é aterrar os dados tal como chegam, sem os alterar. Cria uma tabela de staging com uma coluna NVARCHAR(MAX) para guardar o documento JSON inteiro, mais uma data de carga. Assim preservas a origem e podes reprocessar mais tarde, se as regras de negócio mudarem.

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 }
  ]
}');

Passo 2: Ler os campos de topo com OPENJSON

Antes de achatar, confirma que o texto é JSON válido com ISJSON e lê os campos de primeiro nível. A função OPENJSON com uma cláusula WITH mapeia caminhos JSON para colunas com tipo definido — repara como $.cliente.nome chega a um valor aninhado sem esforço.

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;

Passo 3: Expandir o array aninhado (uma linha por elemento)

O array linhas tem vários itens. Para obter uma linha por item, aplica OPENJSON uma segunda vez sobre o array. Encadear CROSS APPLY junta o cabeçalho do pedido a cada linha, achatando a relação pai-filho.

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;

A palavra-chave AS JSON diz ao OPENJSON que linhas é um fragmento JSON, permitindo abri-lo no segundo OPENJSON. Sem ela — e sem a coluna ser NVARCHAR(MAX) — obténs um erro ou valores nulos.

Passo 4: Materializar a camada limpa (o "T")

Por fim, grava o resultado achatado numa tabela limpa (silver), para que os modelos seguintes consultem colunas em vez de JSON. Um simples INSERT ... SELECT reutiliza a consulta do passo anterior.

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;

Verificar o resultado

Consulta a tabela limpa para confirmar o flatten.

SELECT * FROM clean.PedidoLinhas;

Deves obter duas linhas para o pedido 1024 — A-1 (qtd 2, preço 9.90) e B-7 (qtd 1, preço 19.50) — com qtd e preco já como números, não texto. Se as duas linhas aparecerem com os tipos corretos, o flatten funcionou. Um erro comum é esquecer o AS JSON no Passo 3: sem ele, o array não abre e a coluna fica a NULL.

Conclusão

Com OPENJSON e CROSS APPLY passas de um documento JSON aninhado para uma tabela relacional limpa em poucos passos, mantendo o padrão ELT: carregar primeiro, transformar depois. Como próximo passo, coloca esta lógica num procedimento armazenado e processa só as cargas novas para poupar tempo. E tu, qual foi o JSON mais complicado que já tiveste de achatar?