Migrar do MongoDB para PostgreSQL sem perder os dados aninhados

Documentos com arrays e objetos embutidos não viram tabela por conversão automática. O critério para decidir o que vira tabela e o que vira jsonb.

Contexto: o que estava rodando

Uma aplicação com MongoDB, na estrutura que todo projeto Node acaba tendo: uma coleção orders onde cada pedido carrega dentro de si o endereço de entrega (objeto) e a lista de itens (array de objetos).

um documento de orders

{
  "_id": { "$oid": "66f1a2b3c4d5e6f7a8b9c0d1" },
  "customerId": { "$oid": "66f19f01c4d5e6f7a8b9c0aa" },
  "status": "paid",
  "total": 249.9,
  "shipping": { "street": "Rua X, 100", "city": "Recife", "zip": "50000-000" },
  "items": [
    { "sku": "CAM-001", "qty": 2, "price": 89.9 },
    { "sku": "MEI-114", "qty": 1, "price": 70.1 }
  ],
  "createdAt": { "$date": "2026-03-14T18:22:41.115Z" }
}

O motivo da migração não foi ideológico. Foi relatório: toda pergunta do tipo “quanto cada SKU vendeu por mês” virava um pipeline de agregação que ninguém do time conseguia ler ou manter.

O erro

A primeira tentativa foi exportar e importar direto. Não funciona:

psql $DIRECT_URLexit 1

ERROR:  invalid input syntax for type uuid: "{""$oid"":""66f1a2b3c4d5e6f7a8b9c0d1""}"
CONTEXT:  COPY orders, line 1, column id

E, quando o id passava, o próximo obstáculo era o array:

psql $DIRECT_URLexit 1

ERROR:  column "items" is of type integer but expression is of type jsonb
LINE 3:   doc->'items',
          ^
HINT:  You will need to rewrite or cast the expression.

Diagnóstico

O `_id` não é um UUID

ObjectId tem 12 bytes; UUID tem 16. Não existe conversão que preserve significado, e o mongoexport ainda envolve o valor em Extended JSON — {"$oid": "..."} em vez da string crua. O mesmo vale para datas, que saem como {"$date": "..."}.

Tentar transformar o _id na chave primária nova é o erro de partida. A chave primária do Postgres deve ser nova; o id antigo vira uma coluna de rastreamento. Isso não é apego ao passado: é o que permite comparar os dois bancos depois e rodar os dois em paralelo durante o corte.

Nem todo campo aninhado quer ser tabela

Este é o ponto que decide a qualidade do esquema final, e a regra é simples:

O que é	Vira	Por quê
Array que você consulta, filtra ou soma por conta própria	Tabela filha com chave estrangeira	`items` precisa responder “quanto o SKU X vendeu” sem abrir todo pedido
Objeto sempre lido junto com o pai e nunca consultado sozinho	Colunas na própria tabela, ou `jsonb`	`shipping` só existe para imprimir a etiqueta
Campo de formato variável entre documentos	`jsonb`	Metadados, respostas de gateway, payload de integração

Aplicando ao exemplo: items vira tabela. shipping vira colunas, porque os campos são estáveis e você vai querer filtrar por cidade em algum momento.

Transformar em Node é mais lento e mais frágil

A segunda tentativa foi um script Node que lia o JSON, montava objetos e inseria com o ORM, linha a linha. Levava horas e morria no meio por qualquer documento fora do padrão — obrigando a recomeçar sem saber de onde.

O caminho melhor é em duas fases: carregar os documentos crus numa tabela de apoio e fazer toda a transformação em SQL. Fica mais rápido, é reexecutável, e cada regra de conversão vira uma consulta que você consegue inspecionar antes de aplicar.

A solução

Exportar em NDJSON, uma linha por documento.
```
mongoexport \
  --uri="mongodb+srv://usuario:senha@cluster/loja" \
  --collection=orders \
  --out=orders.ndjson
```
Sem --jsonArray: o formato de uma linha por documento é o que o Postgres consegue ingerir direto.
Carregar cru numa tabela de apoio.

O COPY de texto do Postgres interpreta barra invertida e tabulação, o que corrompe JSON. Trocar o delimitador e a aspa por bytes que não aparecem no conteúdo evita isso:
migracao/01-staging.sql
```
create table staging_orders (doc jsonb);
```
```
psql "$DIRECT_URL" -c "\copy staging_orders(doc) from 'orders.ndjson' \
  csv quote e'\x01' delimiter e'\x02'"
```
Um milhão de documentos entram em segundos, porque nada é interpretado ainda.

Criar o esquema de destino com o id antigo preservado.

migracao/02-esquema.sql

create table orders (
  id            bigint generated always as identity primary key,
  -- Ponte com o MongoDB: permite conferir e reexecutar sem duplicar.
  legacy_id     text not null unique,
  customer_id   bigint not null references customers(id),
  status        text not null,
  total_cents   integer not null check (total_cents >= 0),
  ship_street   text,
  ship_city     text,
  ship_zip      text,
  created_at    timestamptz not null
);

create table order_items (
  id            bigint generated always as identity primary key,
  order_id      bigint not null references orders(id) on delete cascade,
  sku           text not null,
  quantity      integer not null check (quantity > 0),
  unit_cents    integer not null check (unit_cents >= 0)
);

create index on order_items (sku);

Valores monetários viram inteiro em centavos. O total: 249.9 do Mongo é um ponto flutuante binário, e somar float é como se perde centavo em relatório.

Transformar em SQL, com o pai antes do filho.

migracao/03-orders.sql

insert into orders (
  legacy_id, customer_id, status, total_cents,
  ship_street, ship_city, ship_zip, created_at
)
select
  doc->'_id'->>'$oid',
  c.id,
  doc->>'status',
  round((doc->>'total')::numeric * 100)::int,
  doc->'shipping'->>'street',
  doc->'shipping'->>'city',
  doc->'shipping'->>'zip',
  (doc->'createdAt'->>'$date')::timestamptz
from staging_orders s
join customers c on c.legacy_id = s.doc->'customerId'->>'$oid'
on conflict (legacy_id) do nothing;

O on conflict do nothing é o que torna o script reexecutável: rodar duas vezes não duplica, e uma interrupção no meio se resolve rodando de novo.

Para o array, jsonb_array_elements com cross join lateral abre cada item como uma linha:

migracao/04-items.sql

insert into order_items (order_id, sku, quantity, unit_cents)
select
  o.id,
  item->>'sku',
  (item->>'qty')::int,
  round((item->>'price')::numeric * 100)::int
from staging_orders s
join orders o on o.legacy_id = s.doc->'_id'->>'$oid'
cross join lateral jsonb_array_elements(s.doc->'items') as item;

Um insert resolve todos os itens de todos os pedidos. Sem laço, sem ORM.

Achar os documentos fora do padrão antes de confiar no resultado.

Coleção sem esquema sempre tem exceção. Estas consultas listam quem vai dar problema, e devem rodar antes da carga:

migracao/00-auditoria.sql

-- Documentos sem itens, ou com itens que não são array
select count(*) from staging_orders
where jsonb_typeof(doc->'items') is distinct from 'array';

-- Campos que existem em uns documentos e não em outros
select key, count(*)
from staging_orders, jsonb_object_keys(doc) as key
group by key
order by count desc;

-- Preços que não são número
select doc->'_id'->>'$oid'
from staging_orders
cross join lateral jsonb_array_elements(doc->'items') as item
where jsonb_typeof(item->'price') <> 'number'
limit 20;

A segunda consulta é a mais reveladora: ela mostra a diferença entre o esquema que você acha que tem e o que está gravado.

Como confirmar que resolveu

Contagem casa dos dois lados.

select
  (select count(*) from staging_orders)                        as documentos,
  (select count(*) from orders)                                as pedidos,
  (select count(*) from staging_orders
   cross join lateral jsonb_array_elements(doc->'items'))      as itens_origem,
  (select count(*) from order_items)                           as itens_destino;

Os pares têm que ser idênticos. Diferença significa documento descartado por join que não encontrou par — normalmente um customerId órfão.

Soma financeira bate.

select
  (select sum(round((doc->>'total')::numeric * 100)) from staging_orders) as origem,
  (select sum(total_cents) from orders)                                   as destino;

Se divergir em poucos centavos, é arredondamento de float — vale investigar documento a documento antes de aceitar.

O relatório que motivou tudo agora é legível:

select sku, sum(quantity) as unidades, sum(quantity * unit_cents) / 100.0 as receita
from order_items i
join orders o on o.id = i.order_id
where o.created_at >= date_trunc('month', now())
group by sku
order by receita desc;

Armadilhas que sobram depois disso

Campo ausente e campo nulo não são a mesma coisa. No Mongo, um documento antigo simplesmente não tem a chave. doc->>'campo' devolve NULL nos dois casos, então uma coluna not null derruba a carga. Descubra antes com a consulta de chaves.

Datas gravadas como texto. Documentos criados por versões antigas do código podem ter createdAt como string comum, não como {"$date": ...}. O doc->'createdAt'->>'$date' devolve NULL neles e o not null reclama. Trate com coalesce cobrindo as duas formas.

Não apague a tabela de apoio no dia do corte. Enquanto a aplicação estiver sendo validada, staging_orders é a única forma de responder “de onde veio esta linha”. Mantenha por algumas semanas.

Os índices vêm depois da carga. Criar índice antes torna cada insert mais lento. Carregue tudo, depois crie os índices e rode analyze para as estatísticas do planejador ficarem corretas — sem isso, a primeira consulta pesada escolhe um plano ruim.