Tipo de dato JSON

Documentación sobre el tipo de dato JSON en ClickHouse, que ofrece compatibilidad nativa para trabajar con datos JSON

¿Buscas una guía?

Consulta nuestra guía de buenas prácticas para JSON para ver ejemplos, funciones avanzadas y aspectos a tener en cuenta al usar el tipo JSON.

El tipo JSON almacena documentos JavaScript Object Notation (JSON) en una única columna.

En ClickHouse Open-Source, el tipo de datos JSON se considera apto para producción a partir de la versión 25.3. No se recomienda usar este tipo en producción en versiones anteriores.

Para declarar una columna de tipo JSON, puedes usar la siguiente sintaxis:

<column_name> JSON
(
    max_dynamic_paths=N,
    max_dynamic_types=M,
    some.path TypeName,
    SKIP path.to.skip,
    SKIP REGEXP 'paths_regexp'
)

Los parámetros de la sintaxis anterior se definen así:

Parámetro	Descripción	Valor predeterminado
max_dynamic_paths	Un parámetro opcional que indica cuántas rutas pueden almacenarse por separado como subcolumnas en un mismo bloque de datos almacenado por separado (por ejemplo, en una misma parte de datos de una tabla MergeTree). Si se supera este límite, todas las demás rutas se almacenarán juntas en una única estructura llamada datos compartidos. También existen formas de cambiar el límite de rutas dinámicas sin modificar este parámetro.	1024
max_dynamic_types	Un parámetro opcional entre 1 y 255 que indica cuántos tipos de datos distintos pueden almacenarse por separado dentro de una misma columna de ruta de tipo Dynamic en un mismo bloque de datos almacenado por separado (por ejemplo, en una misma parte de datos de una tabla MergeTree). Si se supera este límite, todos los tipos nuevos se almacenarán juntos en una única estructura llamada shared variant.	32
some.path TypeName	Una indicación de tipo opcional para una ruta concreta del JSON. Estas rutas siempre se almacenarán como subcolumnas con el tipo especificado.
SKIP path.to.skip	Una indicación opcional para una ruta concreta que debe omitirse durante el análisis del JSON. Estas rutas nunca se almacenarán en la columna JSON. Si la ruta especificada es un objeto JSON anidado, se omitirá todo el objeto anidado.
SKIP REGEXP 'path_regexp'	Una indicación opcional con una expresión regular que se usa para omitir rutas durante el análisis del JSON. Todas las rutas que coincidan con esta expresión regular nunca se almacenarán en la columna 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

Si JSON es un documento opaco que no se analiza dentro de la base de datos y solo se almacena y se recupera, debe almacenarse como un campo String. Las ventajas del tipo JSON solo se materializan cuando necesitas consultar, filtrar o agregar de forma eficiente campos específicos dentro de estructuras JSON dinámicas.

También puedes combinar ambos enfoques: usa columnas estándar para campos de nivel superior predecibles y una columna JSON para las secciones dinámicas del payload.

​

Crear JSON

En esta sección veremos las distintas formas de crear JSON.

​

Uso de JSON en la definición de una columna de una tabla

Query (Example 1)

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response (Example 1)

┌─json────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"]}          │
│ {"f":"Hello, World!"}                       │
│ {"a":{"b":"43","e":"10"},"c":["4","5","6"]} │
└─────────────────────────────────────────────┘

Query (Example 2)

CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response (Example 2)

┌─json──────────────────────────────┐
│ {"a":{"b":42},"c":["1","2","3"]}  │
│ {"a":{"b":0},"f":"Hello, World!"} │
│ {"a":{"b":43},"c":["4","5","6"]}  │
└───────────────────────────────────┘

​

Uso de CAST con ::JSON

Es posible convertir varios tipos con la sintaxis especial ::JSON.

​

CAST de String a JSON

Query

SELECT '{"a" : {"b" : 42},"c" : [1, 2, 3], "d" : "Hello, World!"}'::JSON AS json;

Response

┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘

​

CAST de Tuple a JSON

Query

SET enable_named_columns_in_function_tuple = 1;
SELECT (tuple(42 AS b) AS a, [1, 2, 3] AS c, 'Hello, World!' AS d)::JSON AS json;

Response

┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘

​

CAST de Map a JSON

Query

SET use_variant_as_common_type=1;
SELECT map('a', map('b', 42), 'c', [1,2,3], 'd', 'Hello, World!')::JSON AS json;

Response

┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘

Las rutas JSON se almacenan de forma aplanada. Esto significa que, cuando se da formato a un objeto JSON a partir de una ruta como a.b.c, no es posible saber si el objeto debe construirse como { "a.b.c" : ... } o como { "a": { "b": { "c": ... } } }. Nuestra implementación siempre asumirá lo segundo.

Por ejemplo:

Consulta

SELECT CAST('{"a.b.c" : 42}', 'JSON') AS json

devolverá:

Respuesta

   ┌─json───────────────────┐
1. │ {"a":{"b":{"c":"42"}}} │
   └────────────────────────┘

y no:

   ┌─json───────────┐
1. │ {"a.b.c":"42"} │
   └────────────────┘

​

Lectura de rutas JSON como subcolumnas

El tipo JSON permite leer cada ruta como una subcolumna independiente. Si no se especifica el tipo de la ruta solicitada en la declaración del tipo JSON, la subcolumna de esa ruta siempre tendrá el tipo Dynamic.

Por ejemplo:

Query

CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42, "g" : 42.42}, "c" : [1, 2, 3], "d" : "2020-01-01"}'), ('{"f" : "Hello, World!", "d" : "2020-01-02"}'), ('{"a" : {"b" : 43, "e" : 10, "g" : 43.43}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response

┌─json────────────────────────────────────────────────────────┐
│ {"a":{"b":42,"g":42.42},"c":["1","2","3"],"d":"2020-01-01"} │
│ {"a":{"b":0},"d":"2020-01-02","f":"Hello, World!"}          │
│ {"a":{"b":43,"g":43.43},"c":["4","5","6"]}                  │
└─────────────────────────────────────────────────────────────┘

Query (Reading JSON paths as sub-columns)

SELECT json.a.b, json.a.g, json.c, json.d FROM test;

Response (Reading JSON paths as sub-columns)

┌─json.a.b─┬─json.a.g─┬─json.c──┬─json.d─────┐
│       42 │ 42.42    │ [1,2,3] │ 2020-01-01 │
│        0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ    │ 2020-01-02 │
│       43 │ 43.43    │ [4,5,6] │ ᴺᵁᴸᴸ       │
└──────────┴──────────┴─────────┴────────────┘

También puedes usar la función getSubcolumn para leer subcolumnas de tipo JSON:

Query

SELECT getSubcolumn(json, 'a.b'), getSubcolumn(json, 'a.g'), getSubcolumn(json, 'c'), getSubcolumn(json, 'd') FROM test;

Response

┌─getSubcolumn(json, 'a.b')─┬─getSubcolumn(json, 'a.g')─┬─getSubcolumn(json, 'c')─┬─getSubcolumn(json, 'd')─┐
│                        42 │ 42.42                     │ [1,2,3]                 │ 2020-01-01              │
│                         0 │ ᴺᵁᴸᴸ                      │ ᴺᵁᴸᴸ                    │ 2020-01-02              │
│                        43 │ 43.43                     │ [4,5,6]                 │ ᴺᵁᴸᴸ                    │
└───────────────────────────┴───────────────────────────┴─────────────────────────┴─────────────────────────┘

Si no se encuentra la ruta solicitada en los datos, se rellenará con valores NULL:

Query

SELECT json.non.existing.path FROM test;

Response

┌─json.non.existing.path─┐
│ ᴺᵁᴸᴸ                   │
│ ᴺᵁᴸᴸ                   │
│ ᴺᵁᴸᴸ                   │
└────────────────────────┘

Veamos los tipos de datos de las subcolumnas devueltas:

Query

SELECT toTypeName(json.a.b), toTypeName(json.a.g), toTypeName(json.c), toTypeName(json.d) FROM test;

Response

┌─toTypeName(json.a.b)─┬─toTypeName(json.a.g)─┬─toTypeName(json.c)─┬─toTypeName(json.d)─┐
│ UInt32               │ Dynamic              │ Dynamic            │ Dynamic            │
│ UInt32               │ Dynamic              │ Dynamic            │ Dynamic            │
│ UInt32               │ Dynamic              │ Dynamic            │ Dynamic            │
└──────────────────────┴──────────────────────┴────────────────────┴────────────────────┘

Como podemos ver, para a.b, el tipo es UInt32, tal como especificamos en la declaración del tipo JSON, y para todas las demás subcolumnas el tipo es Dynamic.

También es posible leer subcolumnas de un tipo Dynamic mediante la sintaxis especial json.some.path.:TypeName:

Query

SELECT
    json.a.g.:Float64,
    dynamicType(json.a.g),
    json.d.:Date,
    dynamicType(json.d)
FROM test

Response

┌─json.a.g.:`Float64`─┬─dynamicType(json.a.g)─┬─json.d.:`Date`─┬─dynamicType(json.d)─┐
│               42.42 │ Float64               │     2020-01-01 │ Date                │
│                ᴺᵁᴸᴸ │ None                  │     2020-01-02 │ Date                │
│               43.43 │ Float64               │           ᴺᵁᴸᴸ │ None                │
└─────────────────────┴───────────────────────┴────────────────┴─────────────────────┘

Las subcolumnas Dynamic pueden convertirse a cualquier tipo de dato. En este caso, se lanzará una excepción si el tipo interno de Dynamic no puede convertirse al tipo solicitado:

Query

SELECT json.a.g::UInt64 AS uint
FROM test;

Response

┌─uint─┐
│   42 │
│    0 │
│   43 │
└──────┘

Query

SELECT json.a.g::UUID AS float
FROM test;

Response

Received exception from server:
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception:
Conversion between numeric types and UUID is not supported.
Probably the passed UUID is unquoted:
while executing 'FUNCTION CAST(__table1.json.a.g :: 2, 'UUID'_String :: 1) -> CAST(__table1.json.a.g, 'UUID'_String) UUID : 0'.
(NOT_IMPLEMENTED)

Para leer subcolumnas de forma eficiente en partes Compact de MergeTree, asegúrese de que esté habilitada la configuración de MergeTree write_marks_for_substreams_in_compact_parts.

​

Lectura de subobjetos JSON como subcolumnas

El tipo JSON admite la lectura de objetos anidados como subcolumnas de tipo JSON mediante la sintaxis especial json.^some.path:

Query

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : {"c" : 42, "g" : 42.42}}, "c" : [1, 2, 3], "d" : {"e" : {"f" : {"g" : "Hello, World", "h" : [1, 2, 3]}}}}'), ('{"f" : "Hello, World!", "d" : {"e" : {"f" : {"h" : [4, 5, 6]}}}}'), ('{"a" : {"b" : {"c" : 43, "e" : 10, "g" : 43.43}}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response

┌─json──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":"42","g":42.42}},"c":["1","2","3"],"d":{"e":{"f":{"g":"Hello, World","h":["1","2","3"]}}}} │
│ {"d":{"e":{"f":{"h":["4","5","6"]}}},"f":"Hello, World!"}                                                 │
│ {"a":{"b":{"c":"43","e":"10","g":43.43}},"c":["4","5","6"]}                                               │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Query

SELECT json.^a.b, json.^d.e.f FROM test;

Response

┌─json.^`a`.b───────────────────┬─json.^`d`.e.f──────────────────────────┐
│ {"c":"42","g":42.42}          │ {"g":"Hello, World","h":["1","2","3"]} │
│ {}                            │ {"h":["4","5","6"]}                    │
│ {"c":"43","e":"10","g":43.43} │ {}                                     │
└───────────────────────────────┴────────────────────────────────────────┘

Cuando las rutas se almacenan en datos compartidos básicos (map), la lectura de subcolumnas de subobjetos puede ser ineficiente, ya que requiere recorrer toda la estructura de datos compartidos. Con la serialización de datos compartidos map_with_buckets o advanced, la lectura de subcolumnas desde datos compartidos está muy optimizada.

​

Lectura de subcolumnas combinadas de JSON

El tipo JSON permite leer una ruta como una subcolumna combinada mediante la sintaxis especial [e-mail]. Una subcolumna combinada para una ruta determinada devuelve:

• El valor literal almacenado en esa ruta como Dynamic, si la ruta tiene un valor literal.
• Un subobjeto JSON en esa ruta como Dynamic, si la ruta no tiene un valor literal pero sí tiene subrutas anidadas.
• NULL, si en esa ruta no existe ni un valor literal ni ninguna subruta.

Esto es útil cuando una ruta puede contener un valor escalar o un objeto anidado en distintas filas, y resulta más práctico que consultar por separado la subcolumna literal (json.a) y la subcolumna de subobjeto (json.^a).

El siguiente ejemplo compara los tres tipos de subcolumna para la ruta a:

Query

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : 42, "b" : {"c" : 1, "d" : "Hello"}}'), ('{"a" : {"x": 1, "y": 2}, "b" : {"c" : 1}}'), ('{"c" : "World"}');
SELECT json FROM test;

Response

┌─json────────────────────────────┐
│ {"a":42,"b":{"c":1,"d":"Hello"}}│
│ {"a":{"x":1,"y":2},"b":{"c":1}}│
│ {"c":"World"}                   │
└─────────────────────────────────┘

Query

SELECT
    json.a,
    dynamicType(json.a),
    json.^a,
    toTypeName(json.^a),
    json.@a,
    dynamicType(json.@a)
FROM test;

Response

┌─json.a─┬─dynamicType(json.a)─┬─json.^a───────┬─toTypeName(json.^a)─┬─json.@a───────┬─dynamicType(json.@a)─┐
│ 42     │ Int64               │ {}            │ JSON                │ 42            │ Int64                │
│ NULL   │ None                │ {"x":1,"y":2} │ JSON                │ {"x":1,"y":2} │ JSON                 │
│ NULL   │ None                │ {}            │ JSON                │ NULL          │ None                 │
└────────┴─────────────────────┴───────────────┴─────────────────────┴───────────────┴──────────────────────┘

• Fila 1: a contiene un literal 42. json.a lo devuelve como Dynamic(Int64), json.^a devuelve un subobjeto vacío {} (sin claves anidadas en a) y json.@a devuelve el literal 42.
• Fila 2: a contiene un objeto anidado. json.a devuelve NULL (no hay ningún literal en esa ruta), json.^a devuelve el subobjeto como JSON y json.@a también devuelve el subobjeto como Dynamic(JSON).
• Fila 3: a no está presente en absoluto. Tanto json.a como json.@a devuelven NULL, mientras que json.^a devuelve un {} vacío.

Cuando las rutas se almacenan en datos compartidos básicos (map) shared data, la lectura de subcolumnas combinadas puede ser ineficiente, ya que requiere recorrer toda la estructura de datos compartidos. Con la serialización de datos compartidos map_with_buckets o advanced, la lectura de subcolumnas desde los datos compartidos está muy optimizada.

​

Inferencia de tipos para rutas

Durante el análisis de JSON, ClickHouse intenta detectar el tipo de datos más adecuado para cada ruta JSON. Funciona de forma similar a la inferencia automática del esquema a partir de los datos de entrada y se controla con los mismos ajustes:

• input_format_try_infer_dates
• input_format_try_infer_datetimes
• schema_inference_make_columns_nullable
• input_format_json_try_infer_numbers_from_strings
• input_format_json_infer_incomplete_types_as_strings
• input_format_json_read_numbers_as_strings
• input_format_json_read_bools_as_strings
• input_format_json_read_bools_as_numbers
• input_format_json_read_arrays_as_strings
• input_format_json_infer_array_of_dynamic_from_array_of_different_types

Veamos algunos ejemplos:

Query

SELECT JSONAllPathsWithTypes('{"a" : "2020-01-01", "b" : "2020-01-01 10:00:00"}'::JSON) AS paths_with_types settings input_format_try_infer_dates=1, input_format_try_infer_datetimes=1;

Response

┌─paths_with_types─────────────────┐
│ {'a':'Date','b':'DateTime64(9)'} │
└──────────────────────────────────┘

Query

SELECT JSONAllPathsWithTypes('{"a" : "2020-01-01", "b" : "2020-01-01 10:00:00"}'::JSON) AS paths_with_types settings input_format_try_infer_dates=0, input_format_try_infer_datetimes=0;

Response

┌─paths_with_types────────────┐
│ {'a':'String','b':'String'} │
└─────────────────────────────┘

Query

SELECT JSONAllPathsWithTypes('{"a" : [1, 2, 3]}'::JSON) AS paths_with_types settings schema_inference_make_columns_nullable=1;

Response

┌─paths_with_types───────────────┐
│ {'a':'Array(Nullable(Int64))'} │
└────────────────────────────────┘

Query

SELECT JSONAllPathsWithTypes('{"a" : [1, 2, 3]}'::JSON) AS paths_with_types settings schema_inference_make_columns_nullable=0;

Response

┌─paths_with_types─────┐
│ {'a':'Array(Int64)'} │
└──────────────────────┘

​

Manejo de arrays de objetos JSON

Las rutas JSON que contienen un array de objetos se analizan como el tipo Array(JSON) y se insertan en una columna Dynamic correspondiente a la ruta. Para leer un array de objetos, puedes extraerlo de la columna Dynamic como una subcolumna:

Query

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES
('{"a" : {"b" : [{"c" : 42, "d" : "Hello", "f" : [[{"g" : 42.42}]], "k" : {"j" : 1000}}, {"c" : 43}, {"e" : [1, 2, 3], "d" : "My", "f" : [[{"g" : 43.43, "h" : "2020-01-01"}]],  "k" : {"j" : 2000}}]}}'),
('{"a" : {"b" : [1, 2, 3]}}'),
('{"a" : {"b" : [{"c" : 44, "f" : [[{"h" : "2020-01-02"}]]}, {"e" : [4, 5, 6], "d" : "World", "f" : [[{"g" : 44.44}]],  "k" : {"j" : 3000}}]}}');
SELECT json FROM test;

Response

┌─json────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"a":{"b":[{"c":"42","d":"Hello","f":[[{"g":42.42}]],"k":{"j":"1000"}},{"c":"43"},{"d":"My","e":["1","2","3"],"f":[[{"g":43.43,"h":"2020-01-01"}]],"k":{"j":"2000"}}]}} │
│ {"a":{"b":["1","2","3"]}}                                                                                                                                               │
│ {"a":{"b":[{"c":"44","f":[[{"h":"2020-01-02"}]]},{"d":"World","e":["4","5","6"],"f":[[{"g":44.44}]],"k":{"j":"3000"}}]}}                                                │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Query

SELECT json.a.b, dynamicType(json.a.b) FROM test;

Response

┌─json.a.b──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─dynamicType(json.a.b)────────────────────────────────────┐
│ ['{"c":"42","d":"Hello","f":[[{"g":42.42}]],"k":{"j":"1000"}}','{"c":"43"}','{"d":"My","e":["1","2","3"],"f":[[{"g":43.43,"h":"2020-01-01"}]],"k":{"j":"2000"}}'] │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │
│ [1,2,3]                                                                                                                                                           │ Array(Nullable(Int64))                                   │
│ ['{"c":"44","f":[[{"h":"2020-01-02"}]]}','{"d":"World","e":["4","5","6"],"f":[[{"g":44.44}]],"k":{"j":"3000"}}']                                                  │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────┘

Como habrás notado, los parámetros max_dynamic_types/max_dynamic_paths del tipo JSON anidado se redujeron con respecto a los valores predeterminados. Esto es necesario para evitar que el número de subcolumnas crezca sin control en arrays anidados de objetos JSON.

Intentemos leer subcolumnas de una columna JSON anidada:

Query

SELECT json.a.b.:`Array(JSON)`.c, json.a.b.:`Array(JSON)`.f, json.a.b.:`Array(JSON)`.d FROM test;

Response

┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐
│ [42,43,NULL]              │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My']       │
│ []                        │ []                                                          │ []                        │
│ [44,NULL]                 │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]]                │ [NULL,'World']            │
└───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘

Podemos evitar escribir los nombres de las subcolumnas de Array(JSON) usando una sintaxis especial:

Query

SELECT json.a.b[].c, json.a.b[].f, json.a.b[].d FROM test;

Response

┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐
│ [42,43,NULL]              │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My']       │
│ []                        │ []                                                          │ []                        │
│ [44,NULL]                 │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]]                │ [NULL,'World']            │
└───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘

El número de [] después de la ruta indica el nivel del array. Por ejemplo, json.path[][] se transformará en json.path.:Array(Array(JSON))

Veamos las rutas y los tipos dentro de nuestro Array(JSON):

Query

SELECT DISTINCT arrayJoin(JSONAllPathsWithTypes(arrayJoin(json.a.b[]))) FROM test;

Response

┌─arrayJoin(JSONAllPathsWithTypes(arrayJoin(json.a.b.:`Array(JSON)`)))──┐
│ ('c','Int64')                                                         │
│ ('d','String')                                                        │
│ ('f','Array(Array(JSON(max_dynamic_types=8, max_dynamic_paths=64)))') │
│ ('k.j','Int64')                                                       │
│ ('e','Array(Nullable(Int64))')                                        │
└───────────────────────────────────────────────────────────────────────┘

Leamos las subcolumnas de una columna Array(JSON):

Query

SELECT json.a.b[].c.:Int64, json.a.b[].f[][].g.:Float64, json.a.b[].f[][].h.:Date FROM test;

Response

┌─json.a.b.:`Array(JSON)`.c.:`Int64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.g.:`Float64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.h.:`Date`─┐
│ [42,43,NULL]                       │ [[[42.42]],[],[[43.43]]]                                     │ [[[NULL]],[],[['2020-01-01']]]                            │
│ []                                 │ []                                                           │ []                                                        │
│ [44,NULL]                          │ [[[NULL]],[[44.44]]]                                         │ [[['2020-01-02']],[[NULL]]]                               │
└────────────────────────────────────┴──────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘

También se pueden leer subcolumnas de subobjetos de una columna JSON anidada:

Query

SELECT json.a.b[].^k FROM test

Response

┌─json.a.b.:`Array(JSON)`.^`k`─────────┐
│ ['{"j":"1000"}','{}','{"j":"2000"}'] │
│ []                                   │
│ ['{}','{"j":"3000"}']                │
└──────────────────────────────────────┘

​

Manejo de claves JSON con NULL

En nuestra implementación de JSON, null y la ausencia de un valor se consideran equivalentes:

Query

SELECT '{}'::JSON AS json1, '{"a" : null}'::JSON AS json2, json1 = json2

Response

┌─json1─┬─json2─┬─equals(json1, json2)─┐
│ {}    │ {}    │                    1 │
└───────┴───────┴──────────────────────┘

Significa que es imposible determinar si los datos JSON originales contenían alguna ruta con el valor NULL o si no la contenían en absoluto.

​

Gestión de claves JSON con puntos

Internamente, la columna JSON almacena todas las rutas y los valores de forma aplanada. Esto significa que, de forma predeterminada, estos 2 objetos se consideran iguales:

{"a" : {"b" : 42}}
{"a.b" : 42}

Ambos se almacenarán internamente como un par de ruta a.b y valor 42. Durante el formateo de JSON, siempre formamos objetos anidados a partir de las partes de la ruta separadas por puntos:

Query

SELECT '{"a" : {"b" : 42}}'::JSON AS json1, '{"a.b" : 42}'::JSON AS json2, JSONAllPaths(json1), JSONAllPaths(json2);

Response

┌─json1────────────┬─json2────────────┬─JSONAllPaths(json1)─┬─JSONAllPaths(json2)─┐
│ {"a":{"b":"42"}} │ {"a":{"b":"42"}} │ ['a.b']             │ ['a.b']             │
└──────────────────┴──────────────────┴─────────────────────┴─────────────────────┘

Como puedes ver, el JSON inicial {"a.b" : 42} ahora aparece con el formato {"a" : {"b" : 42}}.

Esta limitación también hace que falle el análisis de objetos JSON válidos como este:

Query

SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json;

Response

Code: 117. DB::Exception: Cannot insert data into JSON column: Duplicate path found during parsing JSON object: a.b. You can enable setting type_json_skip_duplicated_paths to skip duplicated paths during insert: In scope SELECT CAST('{"a.b" : 42, "a" : {"b" : "Hello, World"}}', 'JSON') AS json. (INCORRECT_DATA)

Si desea conservar las claves con puntos y evitar tratarlas como objetos anidados, puede habilitar la configuración json_type_escape_dots_in_keys (disponible a partir de la versión 25.8). En este caso, durante el análisis, todos los puntos de las claves JSON se escaparán como %2E y se restaurarán durante el formateo.

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a" : {"b" : 42}}'::JSON AS json1, '{"a.b" : 42}'::JSON AS json2, JSONAllPaths(json1), JSONAllPaths(json2);

Response

┌─json1────────────┬─json2────────┬─JSONAllPaths(json1)─┬─JSONAllPaths(json2)─┐
│ {"a":{"b":"42"}} │ {"a.b":"42"} │ ['a.b']             │ ['a%2Eb']           │
└──────────────────┴──────────────┴─────────────────────┴─────────────────────┘

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, JSONAllPaths(json);

Response

┌─json──────────────────────────────────┬─JSONAllPaths(json)─┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ ['a%2Eb','a.b']    │
└───────────────────────────────────────┴────────────────────┘

Para leer una clave con un punto escapado como subcolumna, debes usar ese punto escapado en el nombre de la subcolumna:

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, json.`a%2Eb`, json.a.b;

Response

┌─json──────────────────────────────────┬─json.a%2Eb─┬─json.a.b─────┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ 42         │ Hello World! │
└───────────────────────────────────────┴────────────┴──────────────┘

Nota: debido a las limitaciones del parser de identificadores y de analyzer, la subcolumna json.`a.b` es equivalente a la subcolumna json.a.b y no leerá la ruta con el punto escapado:

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, json.`a%2Eb`, json.`a.b`, json.a.b;

Response

┌─json──────────────────────────────────┬─json.a%2Eb─┬─json.a.b─────┬─json.a.b─────┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ 42         │ Hello World! │ Hello World! │
└───────────────────────────────────────┴────────────┴──────────────┴──────────────┘

Además, si quieres especificar un hint para una ruta JSON que contenga claves con puntos (o usarlo en las secciones SKIP/SKIP REGEX), tienes que usar puntos escapados en el hint:

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON(`a%2Eb` UInt8) as json, json.`a%2Eb`, toTypeName(json.`a%2Eb`);

Response

┌─json────────────────────────────────┬─json.a%2Eb─┬─toTypeName(json.a%2Eb)─┐
│ {"a.b":42,"a":{"b":"Hello World!"}} │         42 │ UInt8                  │
└─────────────────────────────────────┴────────────┴────────────────────────┘

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON(SKIP `a%2Eb`) as json, json.`a%2Eb`;

Response

┌─json───────────────────────┬─json.a%2Eb─┐
│ {"a":{"b":"Hello World!"}} │ ᴺᵁᴸᴸ       │
└────────────────────────────┴────────────┘

​

Lectura del tipo JSON a partir de datos

Todos los formatos de texto (JSONEachRow, TSV, CSV, CustomSeparated, Values, etc.) permiten leer el tipo JSON.

Ejemplos:

Query

SELECT json FROM format(JSONEachRow, 'json JSON(a.b.c UInt32, SKIP a.b.d, SKIP d.e, SKIP REGEXP \'b.*\')', '
{"json" : {"a" : {"b" : {"c" : 1, "d" : [0, 1]}}, "b" : "2020-01-01", "c" : 42, "d" : {"e" : {"f" : ["s1", "s2"]}, "i" : [1, 2, 3]}}}
{"json" : {"a" : {"b" : {"c" : 2, "d" : [2, 3]}}, "b" : [1, 2, 3], "c" : null, "d" : {"e" : {"g" : 43}, "i" : [4, 5, 6]}}}
{"json" : {"a" : {"b" : {"c" : 3, "d" : [4, 5]}}, "b" : {"c" : 10}, "e" : "Hello, World!"}}
{"json" : {"a" : {"b" : {"c" : 4, "d" : [6, 7]}}, "c" : 43}}
{"json" : {"a" : {"b" : {"c" : 5, "d" : [8, 9]}}, "b" : {"c" : 11, "j" : [1, 2, 3]}, "d" : {"e" : {"f" : ["s3", "s4"], "g" : 44}, "h" : "2020-02-02 10:00:00"}}}
')

Response

┌─json──────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":1}},"c":"42","d":{"i":["1","2","3"]}}          │
│ {"a":{"b":{"c":2}},"d":{"i":["4","5","6"]}}                   │
│ {"a":{"b":{"c":3}},"e":"Hello, World!"}                       │
│ {"a":{"b":{"c":4}},"c":"43"}                                  │
│ {"a":{"b":{"c":5}},"d":{"h":"2020-02-02 10:00:00.000000000"}} │
└───────────────────────────────────────────────────────────────┘

En formatos de texto como CSV/TSV/etc., JSON se analiza a partir de una cadena que contiene el objeto JSON:

Query

SELECT json FROM format(TSV, 'json JSON(a.b.c UInt32, SKIP a.b.d, SKIP REGEXP \'b.*\')',
'{"a" : {"b" : {"c" : 1, "d" : [0, 1]}}, "b" : "2020-01-01", "c" : 42, "d" : {"e" : {"f" : ["s1", "s2"]}, "i" : [1, 2, 3]}}
{"a" : {"b" : {"c" : 2, "d" : [2, 3]}}, "b" : [1, 2, 3], "c" : null, "d" : {"e" : {"g" : 43}, "i" : [4, 5, 6]}}
{"a" : {"b" : {"c" : 3, "d" : [4, 5]}}, "b" : {"c" : 10}, "e" : "Hello, World!"}
{"a" : {"b" : {"c" : 4, "d" : [6, 7]}}, "c" : 43}
{"a" : {"b" : {"c" : 5, "d" : [8, 9]}}, "b" : {"c" : 11, "j" : [1, 2, 3]}, "d" : {"e" : {"f" : ["s3", "s4"], "g" : 44}, "h" : "2020-02-02 10:00:00"}}')

Response

┌─json──────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":1}},"c":"42","d":{"i":["1","2","3"]}}          │
│ {"a":{"b":{"c":2}},"d":{"i":["4","5","6"]}}                   │
│ {"a":{"b":{"c":3}},"e":"Hello, World!"}                       │
│ {"a":{"b":{"c":4}},"c":"43"}                                  │
│ {"a":{"b":{"c":5}},"d":{"h":"2020-02-02 10:00:00.000000000"}} │
└───────────────────────────────────────────────────────────────┘

​

Alcanzar el límite de las rutas dinámicas dentro de JSON

El tipo de dato JSON solo puede almacenar internamente un número limitado de rutas como subcolumnas independientes. De forma predeterminada, este límite es 1024, pero puedes cambiarlo en la declaración del tipo mediante el parámetro max_dynamic_paths.

Cuando se alcanza el límite, todas las rutas nuevas insertadas en una columna JSON se almacenan en una única estructura de datos compartida. Sigue siendo posible leer esas rutas como subcolumnas, pero puede ser menos eficiente (consulta la sección sobre datos compartidos). Este límite es necesario para evitar una cantidad enorme de subcolumnas distintas que pueda hacer que la tabla quede inutilizable.

Veamos qué ocurre cuando se alcanza el límite en varios escenarios distintos.

​

Al alcanzar el límite durante el análisis de datos

Durante el análisis de objetos JSON en los datos, cuando se alcanza el límite para el bloque de datos actual, todas las rutas nuevas se almacenarán en una estructura de datos compartida. Podemos usar las dos funciones de introspección siguientes: JSONDynamicPaths, JSONSharedDataPaths:

Query

SELECT json, JSONDynamicPaths(json), JSONSharedDataPaths(json) FROM format(JSONEachRow, 'json JSON(max_dynamic_paths=3)', '
{"json" : {"a" : {"b" : 42}, "c" : [1, 2, 3]}}
{"json" : {"a" : {"b" : 43}, "d" : "2020-01-01"}}
{"json" : {"a" : {"b" : 44}, "c" : [4, 5, 6]}}
{"json" : {"a" : {"b" : 43}, "d" : "2020-01-02", "e" : "Hello", "f" : {"g" : 42.42}}}
{"json" : {"a" : {"b" : 43}, "c" : [7, 8, 9], "f" : {"g" : 43.43}, "h" : "World"}}
')

Response

┌─json───────────────────────────────────────────────────────────┬─JSONDynamicPaths(json)─┬─JSONSharedDataPaths(json)─┐
│ {"a":{"b":"42"},"c":["1","2","3"]}                             │ ['a.b','c','d']        │ []                        │
│ {"a":{"b":"43"},"d":"2020-01-01"}                              │ ['a.b','c','d']        │ []                        │
│ {"a":{"b":"44"},"c":["4","5","6"]}                             │ ['a.b','c','d']        │ []                        │
│ {"a":{"b":"43"},"d":"2020-01-02","e":"Hello","f":{"g":42.42}}  │ ['a.b','c','d']        │ ['e','f.g']               │
│ {"a":{"b":"43"},"c":["7","8","9"],"f":{"g":43.43},"h":"World"} │ ['a.b','c','d']        │ ['f.g','h']               │
└────────────────────────────────────────────────────────────────┴────────────────────────┴───────────────────────────┘

Como podemos ver, después de insertar las rutas e y f.g, se alcanzó el límite y se insertaron en una estructura de datos compartida.

​

Durante las fusiones de partes de datos en motores de tabla MergeTree

Durante la fusión de varias partes de datos en una tabla MergeTree, la columna JSON de la parte de datos resultante puede alcanzar el límite de rutas dinámicas y no ser capaz de almacenar todas las rutas de las partes de origen como subcolumnas. En este caso, ClickHouse decide qué rutas permanecerán como subcolumnas después de la fusión y cuáles se almacenarán en la estructura de datos compartida. En la mayoría de los casos, ClickHouse intenta conservar las rutas que contienen el mayor número de valores no nulos y mover las rutas menos frecuentes a la estructura de datos compartida. Sin embargo, esto depende de la implementación.

Veamos un ejemplo de este tipo de fusión. Primero, creemos una tabla con una columna JSON, establezcamos el límite de rutas dinámicas en 3 y luego insertemos valores con 5 rutas diferentes:

Query

CREATE TABLE test (id UInt64, json JSON(max_dynamic_paths=3)) ENGINE=MergeTree ORDER BY id;
SYSTEM STOP MERGES test;
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as a) FROM numbers(5);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as b) FROM numbers(4);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as c) FROM numbers(3);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as d) FROM numbers(2);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as e) FROM numbers(1);

Cada inserción creará una parte de datos independiente con la columna JSON que contiene una única ruta:

Query

SELECT
    count(),
    groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
    groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
    _part
FROM test
GROUP BY _part
ORDER BY _part ASC

Response

┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│       5 │ ['a']         │ []                │ all_1_1_0 │
│       4 │ ['b']         │ []                │ all_2_2_0 │
│       3 │ ['c']         │ []                │ all_3_3_0 │
│       2 │ ['d']         │ []                │ all_4_4_0 │
│       1 │ ['e']         │ []                │ all_5_5_0 │
└─────────┴───────────────┴───────────────────┴───────────┘

Ahora, fusionemos todas las partes en una sola y veamos qué pasa:

Query

SELECT
    count(),
    groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
    groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
    _part
FROM test
GROUP BY _part
ORDER BY _part ASC

Response

┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│      15 │ ['a','b','c'] │ ['d','e']         │ all_1_5_2 │
└─────────┴───────────────┴───────────────────┴───────────┘

Como podemos ver, ClickHouse conservó las rutas más frecuentes a, b y c y pasó las rutas d y e a una estructura de datos compartida.

​

Estructura de datos compartida

Como se describió en la sección anterior, cuando se alcanza el límite de max_dynamic_paths, todas las rutas nuevas se almacenan en una única estructura de datos compartida. En esta sección analizaremos en detalle la estructura de datos compartida y cómo leemos de ella las subcolumnas de las rutas.

Consulte la sección “funciones de introspección” para obtener más información sobre las funciones que se utilizan para inspeccionar el contenido de una columna JSON.

​

Estructura de datos compartida en memoria

En memoria, la estructura de datos compartida no es más que una subcolumna de tipo Map(String, String) que almacena la correspondencia entre una ruta de JSON aplanado y un valor codificado en binario. Para extraer de ella la subcolumna correspondiente a una ruta, simplemente iteramos por todas las filas de esta columna Map e intentamos encontrar la ruta solicitada y sus valores.

​

Estructura de datos compartida en las partes de MergeTree

En las tablas MergeTree, almacenamos los datos en partes de datos que guardan todo en disco (local o remoto). Los datos en disco pueden almacenarse de forma diferente a como se almacenan en memoria. Actualmente, hay 3 serializaciones distintas de la estructura de datos compartida en las partes de datos de MergeTree: map, map_with_buckets y advanced.

La versión de serialización se controla mediante los ajustes de MergeTree object_shared_data_serialization_version y object_shared_data_serialization_version_for_zero_level_parts (la parte de nivel cero es la que se crea al insertar datos en la tabla; durante las fusiones, las partes tienen un nivel superior).

Nota: cambiar la serialización de la estructura de datos compartida solo se admite para la versión de serialización de objetos v3

​

Map

En la versión de serialización map, los datos compartidos se serializan como una única columna de tipo Map(String, String), igual que se almacenan en memoria. Para leer una subcolumna de ruta de este tipo de serialización, ClickHouse lee toda la columna Map y extrae en memoria la ruta solicitada.

Esta serialización es eficiente para escribir datos y leer toda la columna JSON, pero no lo es para leer subcolumnas de ruta.

​

Map con buckets

En la versión de serialización map_with_buckets, los datos compartidos se serializan como N columnas (“buckets”) de tipo Map(String, String). Cada uno de esos buckets contiene solo un subconjunto de rutas. Para leer una sub-columna de ruta de este tipo de serialización, ClickHouse lee toda la columna Map de un único bucket y extrae en memoria la ruta solicitada.

Esta serialización es menos eficiente para escribir datos y leer toda la columna JSON, pero es más eficiente para leer sub-columnas de rutas porque solo lee datos de los buckets necesarios.

La cantidad de buckets N se controla mediante las configuraciones de MergeTree object_shared_data_buckets_for_compact_part (8 de forma predeterminada) y object_shared_data_buckets_for_wide_part (32 de forma predeterminada). El valor máximo permitido para ambas configuraciones es 256.

​

Avanzado

En la versión de serialización advanced, los datos compartidos se serializan en una estructura de datos especial que maximiza el rendimiento de la lectura de las subcolumnas de rutas al almacenar información adicional que permite leer solo los datos de las rutas solicitadas. Esta serialización también admite buckets, por lo que cada bucket contiene solo un subconjunto de rutas.

Esta serialización es bastante ineficiente para la escritura de datos (por lo que no se recomienda usarla para partes de nivel cero); leer toda la columna JSON es ligeramente menos eficiente en comparación con la serialización map, pero resulta muy eficiente para leer las subcolumnas de rutas.

Nota: debido a que almacena información adicional dentro de la estructura de datos, el tamaño de almacenamiento en disco es mayor con esta serialización en comparación con las serializaciones map y map_with_buckets.

Para obtener una descripción más detallada de las nuevas serializaciones de datos compartidos y de sus detalles de implementación, consulta la entrada del blog.

​

Control del número de rutas dinámicas dentro de JSON en partes de MergeTree

La forma principal de establecer un límite para las rutas dinámicas en JSON es usar el parámetro max_dynamic_paths dentro de la declaración del tipo JSON. Pero cambiar max_dynamic_paths para columnas existentes requiere ejecutar ALTER TABLE <table> MODIFY COLUMN <column> JSON(max_dynamic_paths=K), lo que iniciará una mutación en segundo plano que reescribirá todas las partes existentes. Esa mutación puede ser bastante costosa y puede afectar al rendimiento del servidor hasta que finalice. Para evitarlo, puede usar estas 3 configuraciones que pueden ayudarle a cambiar el límite de rutas dinámicas en tablas MergeTree para las nuevas partes de datos:

• merge_max_dynamic_subcolumns_in_wide_part - una configuración de MergeTree que limita el número de subcolumnas dinámicas para cada columna JSON durante la fusión en una parte de datos Wide.
• merge_max_dynamic_subcolumns_in_compact_part - una configuración de MergeTree que limita el número de subcolumnas dinámicas para cada columna JSON durante la fusión en una parte de datos Compact.
• max_dynamic_subcolumns_in_json_type_parsing - una configuración de sesión que limita el número de subcolumnas dinámicas para cada columna JSON durante el análisis de datos JSON en una columna JSON.

Nota: el límite de rutas dinámicas no puede superar el valor especificado en el parámetro max_dynamic_paths, incluso si los valores de las configuraciones descritas son mayores.

​

Funciones de introspección

Hay varias funciones que pueden ayudar a inspeccionar el contenido de la columna JSON:

• JSONAllPaths
• JSONAllPathsWithTypes
• JSONAllValues
• JSONDynamicPaths
• JSONDynamicPathsWithTypes
• JSONSharedDataPaths
• JSONSharedDataPathsWithTypes
• distinctDynamicTypes
• distinctJSONPaths y distinctJSONPathsAndTypes

Ejemplos

Veamos el contenido del conjunto de datos GH Archive para la fecha 2020-01-01:

Query

SELECT arrayJoin(distinctJSONPaths(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)

Response

┌─arrayJoin(distinctJSONPaths(json))─────────────────────────┐
│ actor.avatar_url                                           │
│ actor.display_login                                        │
│ actor.gravatar_id                                          │
│ actor.id                                                   │
│ actor.login                                                │
│ actor.url                                                  │
│ created_at                                                 │
│ id                                                         │
│ org.avatar_url                                             │
│ org.gravatar_id                                            │
│ org.id                                                     │
│ org.login                                                  │
│ org.url                                                    │
│ payload.action                                             │
│ payload.before                                             │
│ payload.comment._links.html.href                           │
│ payload.comment._links.pull_request.href                   │
│ payload.comment._links.self.href                           │
│ payload.comment.author_association                         │
│ payload.comment.body                                       │
│ payload.comment.commit_id                                  │
│ payload.comment.created_at                                 │
│ payload.comment.diff_hunk                                  │
│ payload.comment.html_url                                   │
│ payload.comment.id                                         │
│ payload.comment.in_reply_to_id                             │
│ payload.comment.issue_url                                  │
│ payload.comment.line                                       │
│ payload.comment.node_id                                    │
│ payload.comment.original_commit_id                         │
│ payload.comment.original_position                          │
│ payload.comment.path                                       │
│ payload.comment.position                                   │
│ payload.comment.pull_request_review_id                     │
...
│ payload.release.node_id                                    │
│ payload.release.prerelease                                 │
│ payload.release.published_at                               │
│ payload.release.tag_name                                   │
│ payload.release.tarball_url                                │
│ payload.release.target_commitish                           │
│ payload.release.upload_url                                 │
│ payload.release.url                                        │
│ payload.release.zipball_url                                │
│ payload.size                                               │
│ public                                                     │
│ repo.id                                                    │
│ repo.name                                                  │
│ repo.url                                                   │
│ type                                                       │
└─arrayJoin(distinctJSONPaths(json))─────────────────────────┘

Query

SELECT arrayJoin(distinctJSONPathsAndTypes(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', JSONAsObject)
SETTINGS date_time_input_format = 'best_effort'

Response

┌─arrayJoin(distinctJSONPathsAndTypes(json))──────────────────┐
│ ('actor.avatar_url',['String'])                             │
│ ('actor.display_login',['String'])                          │
│ ('actor.gravatar_id',['String'])                            │
│ ('actor.id',['Int64'])                                      │
│ ('actor.login',['String'])                                  │
│ ('actor.url',['String'])                                    │
│ ('created_at',['DateTime'])                                 │
│ ('id',['String'])                                           │
│ ('org.avatar_url',['String'])                               │
│ ('org.gravatar_id',['String'])                              │
│ ('org.id',['Int64'])                                        │
│ ('org.login',['String'])                                    │
│ ('org.url',['String'])                                      │
│ ('payload.action',['String'])                               │
│ ('payload.before',['String'])                               │
│ ('payload.comment._links.html.href',['String'])             │
│ ('payload.comment._links.pull_request.href',['String'])     │
│ ('payload.comment._links.self.href',['String'])             │
│ ('payload.comment.author_association',['String'])           │
│ ('payload.comment.body',['String'])                         │
│ ('payload.comment.commit_id',['String'])                    │
│ ('payload.comment.created_at',['DateTime'])                 │
│ ('payload.comment.diff_hunk',['String'])                    │
│ ('payload.comment.html_url',['String'])                     │
│ ('payload.comment.id',['Int64'])                            │
│ ('payload.comment.in_reply_to_id',['Int64'])                │
│ ('payload.comment.issue_url',['String'])                    │
│ ('payload.comment.line',['Int64'])                          │
│ ('payload.comment.node_id',['String'])                      │
│ ('payload.comment.original_commit_id',['String'])           │
│ ('payload.comment.original_position',['Int64'])             │
│ ('payload.comment.path',['String'])                         │
│ ('payload.comment.position',['Int64'])                      │
│ ('payload.comment.pull_request_review_id',['Int64'])        │
...
│ ('payload.release.node_id',['String'])                      │
│ ('payload.release.prerelease',['Bool'])                     │
│ ('payload.release.published_at',['DateTime'])               │
│ ('payload.release.tag_name',['String'])                     │
│ ('payload.release.tarball_url',['String'])                  │
│ ('payload.release.target_commitish',['String'])             │
│ ('payload.release.upload_url',['String'])                   │
│ ('payload.release.url',['String'])                          │
│ ('payload.release.zipball_url',['String'])                  │
│ ('payload.size',['Int64'])                                  │
│ ('public',['Bool'])                                         │
│ ('repo.id',['Int64'])                                       │
│ ('repo.name',['String'])                                    │
│ ('repo.url',['String'])                                     │
│ ('type',['String'])                                         │
└─arrayJoin(distinctJSONPathsAndTypes(json))──────────────────┘

​

ALTER MODIFY COLUMN al tipo JSON

Es posible modificar una tabla existente y cambiar el tipo de la columna al nuevo tipo JSON. Actualmente, solo se admite ALTER desde el tipo String.

Ejemplo

Query

CREATE TABLE test (json String) ENGINE=MergeTree ORDER BY tuple();
INSERT INTO test VALUES ('{"a" : 42}'), ('{"a" : 43, "b" : "Hello"}'), ('{"a" : 44, "b" : [1, 2, 3]}'), ('{"c" : "2020-01-01"}');
ALTER TABLE test MODIFY COLUMN json JSON;
SELECT json, json.a, json.b, json.c FROM test;

Response

┌─json─────────────────────────┬─json.a─┬─json.b──┬─json.c─────┐
│ {"a":"42"}                   │ 42     │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ       │
│ {"a":"43","b":"Hello"}       │ 43     │ Hello   │ ᴺᵁᴸᴸ       │
│ {"a":"44","b":["1","2","3"]} │ 44     │ [1,2,3] │ ᴺᵁᴸᴸ       │
│ {"c":"2020-01-01"}           │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ    │ 2020-01-01 │
└──────────────────────────────┴────────┴─────────┴────────────┘

​

Indicaciones de tipo diferidas (Experimental)

Esta funcionalidad es experimental y requiere que la configuración allow_experimental_json_lazy_type_hints esté habilitada.

Cuando agregas o modificas indicaciones de tipo en una columna JSON con ALTER TABLE ... MODIFY COLUMN, ClickHouse normalmente reescribe todas las partes de datos para materializar las nuevas indicaciones de tipo. En tablas con grandes volúmenes de datos históricos (cientos de terabytes), esto puede resultar extremadamente costoso.

Las indicaciones de tipo diferidas permiten añadir indicaciones de tipo como una operación de solo metadatos, sin reescribir los datos existentes:

• Partes antiguas: las indicaciones de tipo se aplican en tiempo de consulta convirtiendo de Dynamic al tipo indicado
• Partes nuevas: las indicaciones de tipo se materializan durante las operaciones INSERT
• Fusiones: las indicaciones de tipo se materializan cuando se fusionan las partes

Esto significa que puedes añadir indicaciones de tipo al instante, y los datos se convertirán gradualmente a medida que se produzcan las fusiones normales en segundo plano.

​

Habilitación de indicación de tipo diferida

SET allow_experimental_json_lazy_type_hints = 1;

​

Ejemplo

Query

-- Crear una tabla e insertar datos
CREATE TABLE test_lazy (json JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO test_lazy VALUES ('{"user_id": "123", "score": "95.5"}');

-- Habilitar la configuración experimental
SET allow_experimental_json_lazy_type_hints = 1;

-- Agregar indicaciones de tipo - esto se completa de forma instantánea sin mutación
ALTER TABLE test_lazy MODIFY COLUMN json JSON(user_id UInt64, score Float64);

-- Consultar los datos - las indicaciones de tipo se aplican en el momento de la lectura
SELECT json.user_id, toTypeName(json.user_id), json.score, toTypeName(json.score) FROM test_lazy;

Response

┌─json.user_id─┬─toTypeName(json.user_id)─┬─json.score─┬─toTypeName(json.score)─┐
│          123 │ UInt64                   │       95.5 │ Float64                │
└──────────────┴──────────────────────────┴────────────┴────────────────────────┘

​

Verificar que no se haya producido ninguna mutación

Puede comprobar que el ALTER se completó sin ninguna mutación consultando la tabla system.mutations:

SELECT * FROM system.mutations WHERE table = 'test_lazy' AND NOT is_done;

Con indicación de tipo diferida habilitada, esta consulta no devuelve ninguna fila, lo que confirma que la operación solo modificó los metadatos.

​

Materialización de indicaciones de tipo

Para materializar las indicaciones de tipo en los datos existentes, puedes optar por una de estas opciones:

1. Esperar a las fusiones en segundo plano: ClickHouse materializará automáticamente las indicaciones de tipo cuando se fusionen las partes
2. Forzar la fusión: Usa OPTIMIZE TABLE test_lazy FINAL para fusionar todas las partes de inmediato
3. Reescribir las partes: Usa ALTER TABLE test_lazy REWRITE PARTS para reescribir las partes con los metadatos nuevos

​

Limitaciones

• Esta funcionalidad es experimental y puede cambiar en futuras versiones
• La conversión de tipos en tiempo de consulta puede suponer una sobrecarga de rendimiento significativa en comparación con los tipos materializados previamente, especialmente en objetos JSON grandes
• Esta funcionalidad solo se aplica al modificar typed_paths (indicaciones de tipo); otros parámetros de JSON, como max_dynamic_paths, SKIP o SKIP REGEXP, siguen requiriendo mutaciones

​

Comparación entre valores del tipo JSON

Los objetos JSON se comparan de forma similar a los de tipo Map.

Por ejemplo:

Query

CREATE TABLE test (json1 JSON, json2 JSON) ENGINE=Memory;
INSERT INTO test FORMAT JSONEachRow
{"json1" : {}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : [1, 2, 3]}}
{"json1" : {"a" : 42}, "json2" : {"a" : "Hello"}}
{"json1" : {"a" : 42}, "json2" : {"b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42, "b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41, "b" : 42}}

SELECT json1, json2, json1 < json2, json1 = json2, json1 > json2 FROM test;

Response

┌─json1──────┬─json2───────────────┬─less(json1, json2)─┬─equals(json1, json2)─┬─greater(json1, json2)─┐
│ {}         │ {}                  │                  0 │                    1 │                     0 │
│ {"a":"42"} │ {}                  │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"41"}          │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"42"}          │                  0 │                    1 │                     0 │
│ {"a":"42"} │ {"a":["1","2","3"]} │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"Hello"}       │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"b":"42"}          │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"a":"42","b":"42"} │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"a":"41","b":"42"} │                  0 │                    0 │                     1 │
└────────────┴─────────────────────┴────────────────────┴──────────────────────┴───────────────────────┘

Nota: cuando 2 rutas contienen valores de distintos tipos de datos, se comparan según la regla de comparación del tipo de dato Variant.

​

Índices de omisión de datos para JSON

Los índices de omisión de datos se pueden usar con columnas JSON de tres maneras:

1. Índices en subcolumnas específicas — crea un índice de omisión estándar en una ruta JSON conocida, igual que en una columna normal. Esto indexa los valores de esa ruta.
2. Índices basados en rutas con JSONAllPaths — indexa el conjunto de rutas presentes en cada gránulo para omitir los gránulos que no puedan contener la ruta consultada.
3. Índices basados en valores con JSONAllValues — indexa todos los valores de todas las rutas JSON mediante un índice de texto para acelerar la búsqueda de texto completo en cualquier subcolumna JSON con un solo índice.

​

Índices sobre subcolumnas específicas

Puede crear un índice de omisión sobre cualquier subcolumna JSON con la misma sintaxis que para las columnas normales. Cualquier tipo de índice compatible funciona (minmax, set, bloom_filter, tokenbf_v1, ngrambf_v1, etc.).

Hay dos formas de hacer referencia a una subcolumna JSON en una expresión de índice:

• Ruta tipada declarada en la indicación de tipo JSON: acceda directamente por nombre: json.a.
• Ruta dinámica con conversión explícita: use la sintaxis de conversión ::: json.b::String.

También puede usar expresiones que combinen varias subcolumnas, por ejemplo json.a || json.b::String.

​

Ejemplo

Query

CREATE TABLE sensor_data
(
    data JSON(sensor_id UInt32),
    INDEX idx_sensor data.sensor_id TYPE minmax GRANULARITY 1,
    INDEX idx_location data.location::String TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;

INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);

El índice minmax en la subcolumna tipada data.sensor_id acota el escaneo a los gránulos coincidentes:

Query

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id < 2;

Response

...
    Índices:
      Skip
        Nombre: idx_sensor
        Descripción: minmax GRANULARITY 1
        Partes: 1/2
        Granulares: 2/8

El índice bloom_filter sobre la subcolumna convertida con CAST data.location::String también funciona:

Query

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';

Response

...
    Indexes:
      Skip
        Name: idx_location
        Description: bloom_filter GRANULARITY 1
        Parts: 1/2
        Granules: 1/8

​

Índices basados en rutas con JSONAllPaths

También se pueden crear índices de omisión de datos en columnas JSON con la función JSONAllPaths. Esto funciona de forma similar a crear índices de omisión en columnas Map con mapKeys: el índice almacena el conjunto de rutas JSON presentes en cada gránulo y lo utiliza para omitir los gránulos que no pueden contener la ruta consultada.

​

Tipos de índice compatibles

JSONAllPaths se puede usar con los siguientes tipos de índices de omisión:

• bloom_filter — admite equals, in e IS NOT NULL.
• tokenbf_v1 — admite equals e IS NOT NULL.
• ngrambf_v1 — admite equals e IS NOT NULL.
• text (índice invertido) — admite equals, in e IS NOT NULL.

​

Ejemplo

Query

CREATE TABLE events
(
    data JSON,
    INDEX idx JSONAllPaths(data) TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');

Puede usar EXPLAIN indexes = 1 para comprobar que se está utilizando el índice de omisión de datos. Cuando una ruta existe solo en una parte, el índice omite la otra parte:

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';

Response

...
    Indexes:
      Skip
        Name: idx
        Description: bloom_filter GRANULARITY 1
        Parts: 1/2
        Granules: 1/2

Si una ruta no existe en ninguna parte, se omiten todas las partes y todos los gránulos:

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;

Response

...
    Índices:
      Omitir
        Nombre: idx
        Descripción: bloom_filter GRANULARITY 1
        Partes: 0/2
        Granulares: 0/2

IS NOT NULL también usa el índice: omite los gránulos donde la ruta no existe (ya que el valor sería NULL):

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;

Response

...
    Índices:
      Omitir
        Nombre: idx
        Descripción: bloom_filter GRANULARITY 1
        Partes: 1/2
        Gránulos: 1/2

​

Cómo funciona

La expresión JSONAllPaths(json_column) produce un Array(String) que contiene todas las rutas presentes en un valor JSON. El índice de omisión almacena estas cadenas de ruta en su estructura de datos (bloom filter o índice invertido). Cuando una consulta filtra por json.some.path, el índice comprueba si la cadena "some.path" está presente en el índice de cada gránulo y omite los gránulos en los que no está presente.

​

Seguridad con rutas ausentes

Cuando una ruta JSON no está presente en un gránulo, la subcolumna se evalúa como:

• NULL para el tipo Dynamic (p. ej., json.path) y las subcolumnas de tipo Nullable (p. ej., json.path.:Int64) — las comparaciones con NULL siempre devuelven false, por lo que omitir bloques es seguro.
• El valor predeterminado del tipo para expresiones CAST no Nullable (p. ej., json.path::Int64 produce 0 cuando falta la ruta) — omitir bloques es seguro solo cuando el valor comparado difiere del valor predeterminado. El índice gestiona esta distinción automáticamente.

​

Búsqueda de texto completo con JSONAllValues

Los índices de texto pueden usarse para acelerar la búsqueda de texto completo en columnas JSON mediante la función JSONAllValues. JSONAllValues devuelve todos los valores de una columna JSON como Array(String), que puede indexarse con un índice de texto. Un solo índice sobre JSONAllValues(json_column) cubre todas las rutas JSON, lo que permite realizar búsquedas de texto completo en cualquier subcolumna sin crear índices independientes para cada ruta.

Consulta Índices basados en valores con JSONAllValues en la documentación de índices de texto para obtener más detalles y ejemplos.

​

Consejos para aprovechar mejor el tipo JSON

Antes de crear una columna JSON y cargar datos en ella, tenga en cuenta los siguientes consejos:

• Analice sus datos y especifique tantas rutas con tipos como le sea posible. Esto hará que el almacenamiento y la lectura sean mucho más eficientes.
• Piense qué rutas necesitará y cuáles no necesitará nunca. Especifique en la sección SKIP las rutas que no vaya a necesitar y, si hace falta, en la sección SKIP REGEXP. Esto mejorará el almacenamiento.
• No establezca el parámetro max_dynamic_paths en valores demasiado altos, ya que esto puede hacer que el almacenamiento y la lectura sean menos eficientes. Aunque depende en gran medida de parámetros del sistema como la memoria, la CPU, etc., como regla general no debería establecer max_dynamic_paths por encima de 10 000 para el almacenamiento en el sistema de archivos local ni por encima de 1024 para el almacenamiento en el sistema de archivos remoto.

​

Más lecturas

• Cómo creamos un nuevo y potente tipo de datos JSON para ClickHouse
• El desafío JSON de los mil millones de documentos: ClickHouse vs. MongoDB, Elasticsearch y más