De JSON para CSV quando a estrutura é aninhada: porque não há resposta certa

As mesmas duas encomendas, duas vezes

Esta é a entrada, e não vai mudar durante o artigo inteiro. Duas encomendas. A primeira tem id 1, um objeto customer com name Emma e city Paris, um array tags de duas strings, e um array lines de dois objetos, cada um com um sku e uma qty. A segunda tem id 2, um objeto customer com Liam e Berlin, um array tags de uma string, um array lines de um objeto, e uma chave extra que o primeiro registo não tem: note, com o valor urgent. Nada de exótico: é a forma de qualquer encomenda, fatura ou carga de evento que alguma vez saiu de uma API.

Passe-a pelo conversor CSV / JSON / YAML e obtém cinco colunas: id, customer, tags, lines, note. A célula customer da primeira linha contém os carateres {""name"":""Emma"",""city"":""Paris""} — o objeto reserializado em JSON e depois citado como campo CSV, o que duplica todas as aspas lá dentro. A célula lines leva o array inteiro do mesmo modo. Abra isso numa folha de cálculo e tem duas linhas, cinco colunas e três células que não consegue ordenar, filtrar nem somar.

Passe exatamente o mesmo JSON pelo conversor de JSON para CSV com os valores aninhados em achatar, e obtém dez colunas: id, customer.name, customer.city, tags.0, tags.1, lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty, note. A segunda encomenda tem uma etiqueta e uma linha, portanto tags.1, lines.1.sku e lines.1.qty ficam vazias nessa linha. Cada valor é agora um escalar na sua própria coluna. O número de linhas não mudou — continuam duas — e o ficheiro passa a ser moldado pelo registo maior e não pelo esquema.

Nenhuma das saídas é um erro. Serializar é o certo quando o CSV é um formato de transporte e algo vai analisar aquelas células mais tarde: a forma do registo é preservada tal e qual, e uma ida e volta entre máquinas devolve o que entrou. Achatar é o certo quando uma pessoa vai abrir o ficheiro: cada folha é ordenável, filtrável e somável. O que é um erro é fazer uma ou outra sem saber qual se fez, e descobrir três semanas depois que o analista anda a contar linhas num ficheiro cuja contagem de linhas responde a uma pergunta diferente da dele.

Caminhos com pontos, índices entre parênteses retos, e a chave que já contém um ponto

Uma vez decidido achatar, é preciso nomear as folhas. Duas grafias são muito usadas. Os caminhos com pontos escrevem um índice de array como qualquer outra chave: tags.0, lines.1.sku. Os índices entre parênteses retos distinguem os dois tipos de passo: tags[0], lines[1].sku. O achatador de JSON dedicado deste site oferece ambas, mais uma escolha de separador — ponto, underscore ou barra — porque um underscore sobrevive à passagem por sistemas que tratam o ponto como operador de caminho, e uma barra retoma a sintaxe de ponteiro já conhecida do JSON Pointer. O modo achatar do conversor de JSON para CSV usa sempre o ponto para ambos, a mais compacta das duas grafias e a que as folhas de cálculo têm menos probabilidade de estragar.

Há uma falha real escondida na grafia com pontos, e vale a pena dizê-lo às claras porque custa dados. Um caminho com pontos é ambíguo: a coluna customer.name tanto pode significar a chave name dentro do objeto customer como uma chave de primeiro nível cujo nome literal é customer.name. O JSON permite as duas, no mesmo objeto. Dê ao modo achatar um registo que contenha uma chave literal a.b com o valor 1 ao lado de um objeto aninhado a cuja chave b vale 2, e a saída tem uma só coluna, a.b, contendo 2. O primeiro valor desapareceu, sem aviso e sem segunda coluna. É raro, mas não é hipotético: chaves com pontos aparecem em campos de registo, nomes de eventos de analítica e em tudo o que derive de um identificador com espaço de nomes.

A defesa é a escolha do separador. Achate com um underscore ou uma barra em vez do ponto e a colisão exige uma chave que contenha literalmente esse carácter, o que é bem menos provável. Se não puder escolher o separador — e no modo achatar do conversor de CSV não pode — verifique se as suas chaves têm pontos antes de achatar, não depois.

Os arrays: uma coluna por elemento, uma só célula, ou uma linha por elemento

Um array de escalares tem três respostas sensatas. Serializá-lo: a célula tags passa a conter os carateres ["vip","eu"]. Dar uma coluna a cada elemento: tags.0 e tags.1. Ou juntar os elementos com um separador que não apareça neles, de modo que a célula tags diga vip|eu e uma fórmula de folha de cálculo a possa voltar a partir. Os conversores aqui fazem as duas primeiras e nenhum faz a terceira: se quiser uma string unida, tem de a produzir antes de converter. A forma unida é a mais legível e a única cujo número de colunas não muda quando os dados mudam, e é por isso que tantas exportações a usam apesar de ser a pior definida.

Um array de objetos é onde as ferramentas e as bases de dados se separam. Com lines de duas entradas, o modo achatar alarga a tabela: lines.0.sku, lines.0.qty, lines.1.sku, lines.1.qty. Quem vem das bases esperaria o contrário — uma linha de saída por artigo, com os campos da encomenda repetidos no bloco, que é o que uma junção produz e o que uma tabela dinâmica quer. Nenhum dos conversores o faz, e a diferença não é cosmética. Alargar mantém uma linha por encomenda: uma contagem de linhas é uma contagem de encomendas. Rebentar dá uma linha por artigo: uma contagem de linhas é uma contagem de artigos e os campos da encomenda ficam duplicados. Ambos se usam na prática; só um responde à pergunta quantas encomendas expedimos.

Vale a pena antecipar uma consequência do alargamento: a disposição das colunas é fixada pelo maior array de todo o ficheiro, e muda quando os dados mudam. Dois registos cujos arrays de etiquetas têm um e três elementos produzem as colunas t.0, t.1 e t.2, com duas células vazias no registo curto. Exporte amanhã a mesma consulta com um registo de quatro etiquetas e o ficheiro ganha uma coluna, em silêncio. Tudo o que a jusante leia colunas por posição e não por nome parte-se nesse dia, e a exportação que o partiu é igualzinha à anterior.

Registos que não concordam sobre as suas chaves

O JSON não tem esquema, portanto um array de objetos não é uma tabela até que a faça. Os dois conversores tomam a união de todas as chaves que veem e deixam um buraco onde um registo não a tem. Três registos com {id, a}, {id, b} e {id, a, c} dão quatro colunas — id, a, b, c — com células vazias onde cada um se cala. É a única resposta que não perde nada, e é por isso que um CSV exportado de uma base documental costuma ser bem mais largo do que qualquer um dos seus documentos.

A ordem das colunas não é ordenada nem estável entre exportações. Os dois conversores usam a ordem de primeira aparição: as chaves surgem na ordem em que o primeiro registo que as contém as apresenta. Dois registos {b, a} e {a, b} produzem as colunas b e depois a, porque o primeiro foi lido primeiro. Mude a ordenação da sua consulta e a ordem das colunas muda com ela, embora os dados sejam idênticos. Se algo a jusante depender da ordem das colunas, ordene as chaves por si mesmo antes de exportar.

Algo se perde qualquer que seja a estratégia, e a culpa é do CSV e não do conversor. Uma chave com valor null, uma chave com a string vazia e uma chave simplesmente ausente tornam-se todas a mesma célula vazia. Releia esse CSV e todas voltam como string vazia. Se a distinção importa — e numa atualização parcial ou numa coluna anulável importa sempre — o CSV não é o formato certo para esse campo, e nenhuma opção de achatamento o salvará.

O que o lado CSV deteta e o que não deteta

Comece pelo sentido que envergonhava este conversor. Dê ao conversor CSV / JSON / YAML o JSON [1,2,3] e ele devolve value / 1 / 2 / 3: uma única coluna sintetizada, porque um número não tem chave própria para servir de nome de coluna e value é a única coisa honesta a chamar-lhe. Um escalar de primeiro nível, 42, dá value / 42. Um array misto, [1,{"a":2}], dá o cabeçalho value,a e duas linhas — 1 seguido de uma célula vazia, depois uma célula vazia seguida de 2 —: o escalar na coluna inventada e o objeto na sua. É a mesma resposta do conversor irmão JSON para CSV, pelo que as duas ferramentas do site já concordam no caso que mais vezes chega de uma API que devolve uma simples lista de identificadores.

Na leitura, o conversor agora fareja o seu delimitador em vez de assumir a vírgula: conta vírgulas contra pontos e vírgulas no primeiro registo, ignorando o que está entre aspas, portanto name;city sobre Emma;Paris chega como {"name":"Emma","city":"Paris"} — o que conta, porque o Excel escreve CSV com ponto e vírgula por omissão em cinco dos seis mercados deste site. Os nomes de coluna repetidos são renomeados em vez de descartados: name,name,name sobre a,b,c devolve name, name_2 e name_3. Uma linha que passa do cabeçalho guarda a célula a mais sob um nome inventado: a,b sobre 1,2,3 devolve a, b e column3. O que continua a não fazer é pontuar uma tabulação ou uma barra vertical. A ferramenta dedicada CSV para JSON pesa quatro candidatos e deixa-o impor um; este conversor pesa dois e não tem qualquer controlo de delimitador na interface, pelo que um ficheiro separado por tabulações continua a chegar como uma única coluna cuja chave é toda a linha de cabeçalho. E entre o que entrar, o CSV que ele escreve de volta é separado por vírgulas.

Perguntas frequentes

Serializo os valores aninhados ou acho-os?

Pergunte quem abre o ficheiro. Se a resposta for um programa que o vai analisar outra vez, serialize: a forma do registo é preservada tal e qual e a ida e volta devolve o que entrou. Se a resposta for uma pessoa numa folha de cálculo, achate: ela precisa de ordenar por customer.city e somar lines.0.qty, e não consegue fazer nem uma coisa nem outra com um bloco JSON numa célula. Se a resposta for ambas, produza dois ficheiros em vez de um meio-termo, porque o meio-termo — achatar um nível e serializar o resto — é a versão que ninguém saberá interpretar seis meses depois. E se o ficheiro for um arquivo e não um relatório, serialize: achatar cristaliza os comprimentos de array de hoje na disposição das colunas, e a exportação do mês que vem não vai encaixar.

Posso obter uma linha por artigo em vez de colunas extra?

De nenhum dos conversores: ambos alargam a tabela e nenhum rebenta um array em linhas. A razão é que rebentar não é uma escolha de formatação mas uma mudança de grão: o ficheiro resultante responde a outra pergunta, e o conversor teria de decidir qual array rebentar quando um registo contém dois. Faça-o antes da conversão, no que produz o JSON: emita um objeto por artigo, cada um com os campos da encomenda de que precisa. A questão do achatamento desaparece então, porque o array já não existe. Se só tem o JSON, um script curto que associe cada encomenda aos seus artigos e concatene os resultados são cinco linhas e deixa a decisão visível no seu próprio código em vez de enterrada nas omissões de uma ferramenta.

Porque é que o meu ficheiro ganhou uma coluna entre duas exportações da mesma consulta?

Porque a disposição das colunas de uma exportação achatada é uma propriedade dos dados e não da consulta. As colunas são a união de todos os caminhos presentes, e os caminhos de array são numerados até ao comprimento do array mais longo do resultado. Um registo com quatro etiquetas, onde da vez anterior havia no máximo três, acrescenta tags.3 a todas as linhas. O mesmo mecanismo acrescenta uma coluna quando um só registo contém uma chave opcional que ninguém tinha usado antes. Duas defesas: ler as colunas por nome e não por posição em tudo o que vier a seguir e, se uma disposição estável importar mesmo, definir explicitamente a lista de colunas e projetar sobre ela, em vez de deixar o exportador deduzi-la do que calhou estar no conjunto de resultados.

Posso voltar a transformar o CSV achatado no JSON original?

Em parte, e as lacunas são previsíveis. O achatador de JSON tem um modo reconstruir que refaz o aninhamento a partir das chaves-caminho: um segmento que é apenas um número constrói um array, qualquer outro constrói um objeto, portanto customer.name e tags.0 voltam como objeto e array. Três coisas não voltam. Os tipos foram-se, porque cada célula de um CSV é texto — um número escrito 1 volta como a string "1" a não ser que o converta. A distinção entre null, string vazia e chave ausente foi-se, como se viu acima. E um array vazio ou um objeto vazio não deixa caminho algum num achatamento com pontos, portanto não pode ser reconstruído; o achatador escreve um [] ou um {} visível precisamente por isso, mas só se tiver achatado com essa ferramenta. Ir e voltar pela estratégia de serialização não perde nada disto, e é esse todo o seu argumento.

O meu CSV usa ponto e vírgula. Tenho de o converter primeiro?

Não. O conversor CSV / JSON / YAML conta vírgulas contra pontos e vírgulas no primeiro registo, fora dos campos entre aspas, e fica com o vencedor: uma exportação de Excel francesa, alemã, espanhola, italiana ou portuguesa lê-se bem sem mexer em nada. O caso que continua a falhar é a tabulação ou a barra vertical: nenhuma das duas está entre os candidatos que pontua, portanto um ficheiro separado por tabulações dá um só campo por linha, cuja chave é toda a sua linha de cabeçalho. O sintoma é inconfundível assim que o conhece: uma única chave com tabulações no nome. Duas saídas. Converta primeiro o delimitador com o conversor de delimitadores, que analisa como deve ser e volta a pôr aspas onde é preciso. Ou use a ferramenta dedicada CSV para JSON, que pesa quatro candidatos e o deixa impor um. Note também que, entre o que entrar, o CSV que este conversor escreve de volta é separado por vírgulas.