Use JSON quando apropriado - ClickHouse Documentation

O ClickHouse agora oferece um tipo de coluna JSON nativo, projetado para dados semiestruturados e dinâmicos. É importante esclarecer que isso é um tipo de coluna, não um formato de dados — você pode inserir JSON no ClickHouse como uma string ou por meio de formatos compatíveis, como JSONEachRow, mas isso não significa que você esteja usando o tipo de coluna JSON. Você só deve usar o tipo JSON quando a estrutura dos seus dados for dinâmica, não quando estiver apenas armazenando JSON.

Quando usar o tipo `JSON`

O tipo JSON foi projetado para consultar, filtrar e agregar campos específicos em objetos JSON com estruturas dinâmicas ou imprevisíveis. Ele faz isso dividindo objetos JSON em subcolunas separadas, o que reduz drasticamente a quantidade de dados lidos e acelera as consultas em campos selecionados em comparação com alternativas como Map ou o parsing de strings. No entanto, isso envolve trade-offs importantes:

INSERTs mais lentos - Dividir JSON em subcolunas, realizar inferência de tipos e gerenciar estruturas de armazenamento flexíveis torna os inserts mais lentos em comparação com armazenar JSON como uma simples coluna String.
Mais lento ao ler objetos inteiros - Se você precisa recuperar documentos JSON completos (em vez de campos específicos), o tipo JSON é mais lento do que ler de uma coluna String. A sobrecarga de reconstruir objetos a partir de subcolunas separadas não traz nenhum benefício quando você não está fazendo consultas no nível de campo.
Sobrecarga de armazenamento - Manter subcolunas separadas acrescenta uma sobrecarga estrutural em comparação com armazenar JSON como um único valor em string.

Use o tipo `JSON` quando:

Seus dados têm uma estrutura dinâmica ou imprevisível, com chaves que variam entre documentos
Os tipos de campo ou os esquemas mudam ao longo do tempo ou variam entre registros
Você precisa consultar, filtrar ou agregar dados em caminhos específicos dentro de objetos JSON cuja estrutura não pode ser prevista antecipadamente
Seu caso de uso envolve dados semiestruturados, como logs, eventos ou conteúdo gerado por usuários, com esquemas inconsistentes

Use uma coluna `String` (ou tipos estruturados) quando:

A estrutura dos seus dados é conhecida e consistente — nesse caso, use colunas normais ou os tipos Tuple, Array, Dynamic ou Variant
Documentos JSON são tratados como blobs opacos, apenas armazenados e recuperados integralmente, sem análise em nível de campo
Você não precisa consultar nem filtrar campos individuais do JSON dentro do banco de dados
O JSON é simplesmente um formato de transporte/armazenamento e não é analisado no ClickHouse

Se o JSON for um documento opaco que não é analisado dentro do banco de dados e é apenas armazenado e recuperado, ele deve ser armazenado como um campo String. Os benefícios do tipo JSON só se materializam quando você precisa consultar, filtrar ou agregar com eficiência campos específicos em estruturas JSON dinâmicas.Você também pode combinar abordagens — use colunas padrão para campos previsíveis de nível superior e uma coluna JSON para seções dinâmicas do payload.

Considerações e dicas para usar JSON

O tipo JSON permite armazenamento colunar eficiente ao achatar caminhos em subcolunas. Mas flexibilidade também exige responsabilidade. Para usá-lo com eficácia:

Especifique os tipos dos caminhos usando hints na definição da coluna para definir os tipos de subcolunas conhecidas e evitar inferência de tipos desnecessária.
Ignore caminhos se você não precisar dos valores, com SKIP e SKIP REGEXP para reduzir o armazenamento e melhorar o desempenho.
Evite definir max_dynamic_paths como um valor muito alto — valores altos aumentam o consumo de recursos e reduzem a eficiência. Como regra prática, mantenha-o abaixo de 10.000.

Indicações de tipoIndicações de tipo oferecem mais do que apenas uma forma de evitar inferência de tipos desnecessária — elas eliminam por completo a indireção de armazenamento e processamento. Caminhos JSON com indicações de tipo são sempre armazenados como colunas tradicionais, sem a necessidade de colunas discriminadoras ou resolução dinâmica no momento da consulta. Isso significa que, com indicações de tipo bem definidas, campos JSON aninhados alcançam o mesmo desempenho e eficiência como se tivessem sido modelados como campos de nível superior desde o início. Como resultado, para conjuntos de dados que são em sua maioria consistentes, mas ainda se beneficiam da flexibilidade do JSON, indicações de tipo oferecem uma forma prática de preservar o desempenho sem precisar reestruturar seu esquema ou pipeline de ingestão.

Recursos avançados

Colunas JSON podem ser usadas em chaves primárias como quaisquer outras colunas. Não é possível especificar codecs para uma subcoluna.
Elas oferecem introspecção por meio de funções como JSONAllPathsWithTypes() e JSONDynamicPaths().
Você pode ler sub-objetos aninhados usando a sintaxe .^.
A sintaxe de consulta pode diferir do SQL padrão e pode exigir conversões de tipo ou operadores especiais para campos aninhados.

Para mais orientações, consulte a documentação JSON do ClickHouse ou veja nossa postagem no blog A New Powerful JSON Data Type for ClickHouse.

Exemplos

Considere o exemplo de JSON a seguir, que representa uma linha do dataset Python PyPI:

{
  "date": "2022-11-15",
  "country_code": "ES",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "pip",
  "python_minor": "3.9",
  "system": "Linux",
  "version": "0.3.0"
}

Vamos supor que esse esquema seja estático e que os tipos possam ser bem definidos. Mesmo que os dados estejam no formato NDJSON (um objeto JSON por linha), não há necessidade de usar o tipo JSON para esse esquema. Basta definir o esquema com tipos clássicos.

CREATE TABLE pypi (
  `date` Date,
  `country_code` String,
  `project` String,
  `type` String,
  `installer` String,
  `python_minor` String,
  `system` String,
  `version` String
)
ENGINE = MergeTree
ORDER BY (project, date)

e insira linhas em JSON:

INSERT INTO pypi FORMAT JSONEachRow
{"date":"2022-11-15","country_code":"ES","project":"clickhouse-connect","type":"bdist_wheel","installer":"pip","python_minor":"3.9","system":"Linux","version":"0.3.0"}

Considere o conjunto de dados do arXiv, que contém 2,5 milhões de artigos científicos. Cada linha desse conjunto de dados, distribuído em NDJSON, representa um artigo científico publicado. Abaixo, mostramos uma linha de exemplo:

{
  "id": "2101.11408",
  "submitter": "Daniel Lemire",
  "authors": "Daniel Lemire",
  "title": "Number Parsing at a Gigabyte per Second",
  "comments": "Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/",
  "journal-ref": "Software: Practice and Experience 51 (8), 2021",
  "doi": "10.1002/spe.2984",
  "report-no": null,
  "categories": "cs.DS cs.MS",
  "license": "http://creativecommons.org/licenses/by/4.0/",
  "abstract": "With disks and networks providing gigabytes per second ....\n",
  "versions": [
    {
      "created": "Mon, 11 Jan 2021 20:31:27 GMT",
      "version": "v1"
    },
    {
      "created": "Sat, 30 Jan 2021 23:57:29 GMT",
      "version": "v2"
    }
  ],
  "update_date": "2022-11-07",
  "authors_parsed": [
    [
      "Lemire",
      "Daniel",
      ""
    ]
  ]
}

Embora o JSON aqui seja complexo, com estruturas aninhadas, ele é previsível. A quantidade e o tipo dos campos não vão mudar. Embora fosse possível usar o tipo JSON neste exemplo, também podemos simplesmente definir a estrutura explicitamente usando os tipos Tuples e Nested:

CREATE TABLE arxiv
(
  `id` String,
  `submitter` String,
  `authors` String,
  `title` String,
  `comments` String,
  `journal-ref` String,
  `doi` String,
  `report-no` String,
  `categories` String,
  `license` String,
  `abstract` String,
  `versions` Array(Tuple(created String, version String)),
  `update_date` Date,
  `authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date

Novamente, podemos inserir os dados em JSON:

INSERT INTO arxiv FORMAT JSONEachRow 
{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/","journal-ref":"Software: Practice and Experience 51 (8), 2021","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"With disks and networks providing gigabytes per second ....\n","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]]}

Suponha que outra coluna chamada tags seja adicionada. Se fosse apenas uma lista de strings, poderíamos modelá-la como Array(String), mas vamos supor que seja possível adicionar estruturas arbitrárias para as tags com tipos mistos (observe que score pode ser uma string ou um inteiro). Nosso documento JSON modificado:

{
 "id": "2101.11408",
 "submitter": "Daniel Lemire",
 "authors": "Daniel Lemire",
 "title": "Number Parsing at a Gigabyte per Second",
 "comments": "Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/",
 "journal-ref": "Software: Practice and Experience 51 (8), 2021",
 "doi": "10.1002/spe.2984",
 "report-no": null,
 "categories": "cs.DS cs.MS",
 "license": "http://creativecommons.org/licenses/by/4.0/",
 "abstract": "With disks and networks providing gigabytes per second ....\n",
 "versions": [
 {
   "created": "Mon, 11 Jan 2021 20:31:27 GMT",
   "version": "v1"
 },
 {
   "created": "Sat, 30 Jan 2021 23:57:29 GMT",
   "version": "v2"
 }
 ],
 "update_date": "2022-11-07",
 "authors_parsed": [
 [
   "Lemire",
   "Daniel",
   ""
 ]
 ],
 "tags": {
   "tag_1": {
     "name": "ClickHouse user",
     "score": "A+",
     "comment": "A good read, applicable to ClickHouse"
   },
   "28_03_2025": {
     "name": "professor X",
     "score": 10,
     "comment": "Didn't learn much",
     "updates": [
       {
         "name": "professor X",
         "comment": "Wolverine found more interesting"
       }
     ]
   }
 }
}

Neste caso, poderíamos modelar os documentos do arXiv inteiramente em JSON ou simplesmente adicionar uma coluna JSON tags. Fornecemos os dois exemplos abaixo:

CREATE TABLE arxiv
(
  `doc` JSON(update_date Date)
)
ENGINE = MergeTree
ORDER BY doc.update_date

Fornecemos uma indicação de tipo para a coluna update_date na definição de JSON, pois a usamos na chave de ordenação/chave primária. Isso ajuda o ClickHouse a saber que essa coluna não terá valor NULL e garante que ele saiba qual subcoluna update_date usar (pode haver várias para cada tipo, portanto, sem isso, haveria ambiguidade).

Podemos inserir dados nesta tabela e, em seguida, visualizar o esquema inferido usando a função JSONAllPathsWithTypes e o formato de saída PrettyJSONEachRow:

INSERT INTO arxiv FORMAT JSONAsObject 
{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/","journal-ref":"Software: Practice and Experience 51 (8), 2021","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"With disks and networks providing gigabytes per second ....\n","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]],"tags":{"tag_1":{"name":"ClickHouse user","score":"A+","comment":"A good read, applicable to ClickHouse"},"28_03_2025":{"name":"professor X","score":10,"comment":"Didn't learn much","updates":[{"name":"professor X","comment":"Wolverine found more interesting"}]}}}

SELECT JSONAllPathsWithTypes(doc)
FROM arxiv
FORMAT PrettyJSONEachRow

{
  "JSONAllPathsWithTypes(doc)": {
    "abstract": "String",
    "authors": "String",
    "authors_parsed": "Array(Array(Nullable(String)))",
    "categories": "String",
    "comments": "String",
    "doi": "String",
    "id": "String",
    "journal-ref": "String",
    "license": "String",
    "submitter": "String",
    "tags.28_03_2025.comment": "String",
    "tags.28_03_2025.name": "String",
    "tags.28_03_2025.score": "Int64",
    "tags.28_03_2025.updates": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
    "tags.tag_1.comment": "String",
    "tags.tag_1.name": "String",
    "tags.tag_1.score": "String",
    "title": "String",
    "update_date": "Date",
    "versions": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))"
  }
}

1 row in set. Elapsed: 0.003 sec.

Como alternativa, poderíamos modelar isso usando nosso esquema anterior e uma coluna JSON tags. Em geral, essa é a opção preferida, pois minimiza a inferência que o ClickHouse precisa fazer:

CREATE TABLE arxiv
(
    `id` String,
    `submitter` String,
    `authors` String,
    `title` String,
    `comments` String,
    `journal-ref` String,
    `doi` String,
    `report-no` String,
    `categories` String,
    `license` String,
    `abstract` String,
    `versions` Array(Tuple(created String, version String)),
    `update_date` Date,
    `authors_parsed` Array(Array(String)),
    `tags` JSON()
)
ENGINE = MergeTree
ORDER BY update_date

INSERT INTO arxiv FORMAT JSONEachRow 
{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/","journal-ref":"Software: Practice and Experience 51 (8), 2021","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"With disks and networks providing gigabytes per second ....\n","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]],"tags":{"tag_1":{"name":"ClickHouse user","score":"A+","comment":"A good read, applicable to ClickHouse"},"28_03_2025":{"name":"professor X","score":10,"comment":"Didn't learn much","updates":[{"name":"professor X","comment":"Wolverine found more interesting"}]}}}

Agora podemos inferir os tipos da subcoluna tags.

SELECT JSONAllPathsWithTypes(tags)
FROM arxiv
FORMAT PrettyJSONEachRow

{
  "JSONAllPathsWithTypes(tags)": {
    "28_03_2025.comment": "String",
    "28_03_2025.name": "String",
    "28_03_2025.score": "Int64",
    "28_03_2025.updates": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
    "tag_1.comment": "String",
    "tag_1.name": "String",
    "tag_1.score": "String"
  }
}

1 linha no conjunto. Elapsed: 0.002 sec.

​Quando usar o tipo JSON

​Use o tipo JSON quando:

​Use uma coluna String (ou tipos estruturados) quando:

​Considerações e dicas para usar JSON

​Recursos avançados

​Exemplos

Quando usar o tipo `JSON`

Use o tipo `JSON` quando:

Use uma coluna `String` (ou tipos estruturados) quando:

Considerações e dicas para usar JSON

Recursos avançados

Exemplos