Use JSON cuando sea apropiado

ClickHouse ahora ofrece un tipo de columna JSON nativo diseñado para datos semiestructurados y dinámicos. Es importante aclarar que se trata de un tipo de columna, no de un formato de datos: puedes insertar JSON en ClickHouse como una cadena o mediante formatos compatibles como JSONEachRow, pero eso no significa que estés usando el tipo de columna JSON. Solo debes usar el tipo JSON cuando la estructura de tus datos sea dinámica, no simplemente porque almacenes JSON.

Cuándo usar el tipo `JSON`

El tipo JSON está diseñado para consultar, filtrar y agregar campos específicos dentro de objetos JSON con estructuras dinámicas o impredecibles. Lo consigue dividiendo los objetos JSON en sub-columnas independientes, lo que reduce drásticamente la cantidad de datos leídos y acelera las consultas sobre los campos seleccionados en comparación con alternativas como Map o analizar cadenas.

Sin embargo, esto conlleva desventajas importantes:

INSERTs más lentos - Dividir el JSON en sub-columnas, realizar la inferencia de tipos y gestionar estructuras de almacenamiento flexibles hace que las inserciones sean más lentas que almacenar el JSON como una simple columna String.
Más lento al leer objetos completos - Si necesita recuperar documentos JSON completos (en lugar de campos específicos), el tipo JSON es más lento que leer desde una columna String. La sobrecarga de reconstruir objetos a partir de sub-columnas independientes no aporta ningún beneficio cuando no se realizan consultas a nivel de campo.
Sobrecarga de almacenamiento - Mantener sub-columnas independientes añade sobrecarga estructural en comparación con almacenar JSON como un único valor de cadena.

Usa el tipo `JSON` cuando:

Tus datos tienen una estructura dinámica o impredecible, con claves que varían entre documentos
Los tipos de los campos o los esquemas cambian con el tiempo o varían entre registros
Necesitas consultar, filtrar o agregar sobre rutas específicas dentro de objetos JSON cuya estructura no puedes predecir de antemano
Tu caso de uso incluye datos semiestructurados, como logs, eventos o contenido generado por los usuarios, con esquemas inconsistentes

Usa una columna `String` (o tipos estructurados) cuando:

La estructura de tus datos es conocida y consistente; en este caso, usa columnas normales o tipos Tuple, Array, Dynamic o Variant
Los documentos JSON se tratan como blobs opacos que solo se almacenan y recuperan completos, sin análisis a nivel de campo
No necesitas consultar ni filtrar campos individuales de JSON dentro de la base de datos
JSON es simplemente un formato de transporte/almacenamiento y no se analiza dentro de ClickHouse

Consideraciones y consejos para usar JSON

El tipo JSON permite un almacenamiento columnar eficiente al aplanar las rutas en subcolumnas. Pero la flexibilidad también conlleva responsabilidad. Para usarlo de forma eficaz:

Especifique los tipos de las rutas mediante indicaciones en la definición de la columna para los subcolumnas conocidas y así evitar inferencias de tipo innecesarias.
Omita rutas si no necesita sus valores, con SKIP y SKIP REGEXP, para reducir el almacenamiento y mejorar el rendimiento.
Evite configurar max_dynamic_paths con un valor demasiado alto: los valores grandes aumentan el consumo de recursos y reducen la eficiencia. Como regla general, manténgalo por debajo de 10.000.

Info - Indicaciones de tipo

Las indicaciones de tipo ofrecen más que una forma de evitar inferencias de tipo innecesarias: eliminan por completo la indirección de almacenamiento y procesamiento. Las rutas JSON con indicaciones de tipo siempre se almacenan igual que las columnas tradicionales, sin necesidad de columnas discriminadoras ni de resolución dinámica en tiempo de consulta. Esto significa que, con indicaciones de tipo bien definidas, los campos JSON anidados alcanzan el mismo rendimiento y la misma eficiencia que si se hubieran modelado como campos de nivel superior desde el principio. Como resultado, para datasets que son en su mayoría consistentes pero que aun así se benefician de la flexibilidad de JSON, las indicaciones de tipo ofrecen una forma práctica de preservar el rendimiento sin necesidad de reestructurar el esquema ni el pipeline para ingestar datos.

Funciones avanzadas

Las columnas JSON pueden usarse en claves primarias como cualquier otra columna. No se pueden especificar codecs para una subcolumna.
Admiten introspección mediante funciones como JSONAllPathsWithTypes() y JSONDynamicPaths().
Puede leer subobjetos anidados con la sintaxis .^.
La sintaxis de consulta puede diferir del SQL estándar y puede requerir conversiones de tipo especiales u operadores para los campos anidados.

Para obtener más información, consulte la documentación de JSON de ClickHouse o lea nuestra entrada del blog A New Powerful JSON Data Type for ClickHouse.

Ejemplos

Considere el siguiente ejemplo en JSON, que representa una fila del Python PyPI dataset:

{
  "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"
}

Supongamos que este esquema es estático y que los tipos pueden definirse con claridad. Aunque los datos estén en format NDJSON (una fila JSON por línea), no es necesario usar el tipo JSON para un esquema así. Simplemente defina el esquema con tipos clásicos.

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 insertar filas en 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 el conjunto de datos de arXiv, que contiene 2,5 millones de artículos académicos. Cada fila de este conjunto de datos, distribuido en formato NDJSON, representa un artículo académico publicado. A continuación se muestra una fila de ejemplo:

{
  "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",
      ""
    ]
  ]
}

Aunque el JSON aquí es complejo y tiene estructuras anidadas, es predecible. La cantidad y el tipo de los campos no cambiarán. Aunque podríamos usar el tipo JSON en este ejemplo, también podemos definir la estructura explícitamente mediante los tipos Tuples y 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

De nuevo, podemos insertar los datos en formato 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",""]]}

Supongamos que se añade otra columna llamada tags. Si fuera simplemente una lista de cadenas, podríamos modelarla como un Array(String), pero supongamos que se pueden añadir estructuras de etiquetas arbitrarias con tipos mixtos (obsérvese que score puede ser una cadena o un entero). Nuestro 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"
       }
     ]
   }
 }
}

En este caso, podríamos modelar los documentos de arXiv íntegramente en JSON o simplemente añadir una columna JSON tags. A continuación, proporcionamos ambos ejemplos:

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

Podemos insertar datos en esta tabla y ver el esquema inferido a continuación usando la función JSONAllPathsWithTypes y el formato de salida 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, podríamos modelar esto utilizando nuestro esquema anterior y una columna JSON tags. En general, esta opción es preferible, ya que minimiza la inferencia que debe realizar ClickHouse:

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"}]}}}

Ahora podemos inferir los tipos de la subcolumna 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 row in set. Elapsed: 0.002 sec.