Перейти к основному содержанию
Хотя автоматическое определение схемы можно использовать для построения начальной схемы для JSON-данных и выполнения запросов к JSON-файлам с данными напрямую, например в S3, вам следует стремиться к созданию оптимизированной версионируемой схемы для ваших данных. Ниже мы рассмотрим рекомендуемый подход к моделированию JSON-структур.

​
Статический и динамический JSON

Основная задача при определении схемы для JSON — подобрать подходящий тип для значения каждого ключа. Мы рекомендуем рекурсивно применять следующие правила к каждому ключу в иерархии JSON, чтобы определить для него подходящий тип.
  1. Примитивные типы - Если значение ключа имеет примитивный тип, независимо от того, находится ли оно во вложенном объекте или в корне, убедитесь, что выбираете его тип в соответствии с общими рекомендациями по проектированию схемы и правилами оптимизации типов. Массивы примитивов, такие как phone_numbers ниже, можно моделировать как Array(<type>), например Array(String).
  2. Статический или динамический - Если значение ключа представляет собой сложный объект, то есть объект или массив объектов, определите, может ли оно меняться. Объекты, в которых новые ключи появляются редко, а добавление нового ключа можно предвидеть и обработать изменением схемы через ALTER TABLE ADD COLUMN, можно считать статическими. Сюда относятся и объекты, в которых в некоторых JSON-документах может присутствовать только часть ключей. Объекты, в которые новые ключи добавляются часто и/или непредсказуемо, следует считать динамическими. Исключение составляют структуры с сотнями или тысячами вложенных ключей — для удобства их тоже можно считать динамическими.
Чтобы определить, является ли значение статическим или динамическим, см. соответствующие разделы Обработка статических объектов и Обработка динамических объектов ниже.

Важно: Приведенные выше правила следует применять рекурсивно. Если значение ключа определяется как динамическое, дальнейшая оценка не требуется, и можно следовать рекомендациям из раздела Обработка динамических объектов. Если объект статический, продолжайте оценивать вложенные ключи, пока не дойдете до примитивных значений или не встретите динамические ключи. Чтобы проиллюстрировать эти правила, используем следующий пример JSON, описывающий человека: 
{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": [
    "010-692-6593",
    "020-192-3333"
  ],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics",
    "labels": {
      "type": "database systems",
      "founded": "2021"
    }
  },
  "dob": "2007-03-31",
  "tags": {
    "hobby": "Databases",
    "holidays": [
      {
        "year": 2024,
        "location": "Azores, Portugal"
      }
    ],
    "car": {
      "model": "Tesla",
      "year": 2023
    }
  }
}
Применяя эти правила:
  • Корневые ключи name, username, email, website можно представить типом String. Столбец phone_numbers — это примитив Array типа Array(String), а dob и id имеют типы Date и UInt32 соответственно.
  • В объект address новые ключи добавляться не будут (только новые объекты адресов), поэтому его можно считать статическим. Если рекурсивно развернуть его, все подстолбцы можно считать примитивами (и типа String), кроме geo. Это также статическая структура с двумя столбцами типа Float32: lat и lon.
  • Столбец tags является динамическим. Мы предполагаем, что в этот объект можно добавлять новые произвольные теги любого типа и структуры.
  • Объект company является статическим и всегда будет содержать не более 3 указанных ключей. Вложенные ключи name и catchPhrase имеют тип String. Ключ labels является динамическим. Мы предполагаем, что в этот объект можно добавлять новые произвольные теги. Значения всегда будут представлять собой пары ключ-значение строкового типа.
Структуры с сотнями или тысячами статических ключей можно считать динамическими, поскольку на практике для них редко удается статически объявить столбцы. Однако там, где это возможно, пропускайте ненужные пути, чтобы сократить затраты как на хранение, так и на определение типов.

​
Обработка статических структур

Мы рекомендуем обрабатывать статические структуры с помощью именованных кортежей, то есть Tuple. Массивы объектов можно хранить с помощью массивов кортежей, то есть Array(Tuple). В самих кортежах столбцы и соответствующие им типы следует определять по тем же правилам. В результате могут получаться вложенные Tuple для представления вложенных объектов, как показано ниже. Чтобы проиллюстрировать это, мы используем приведённый ранее JSON-пример с person, опуская динамические объекты: 
{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": [
    "010-692-6593",
    "020-192-3333"
  ],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics"
  },
  "dob": "2007-03-31"
}
Ниже показана схема этой таблицы: 
CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date
)
ENGINE = MergeTree
ORDER BY username
Обратите внимание, что столбец company имеет тип Tuple(catchPhrase String, name String). Для ключа address используется Array(Tuple) с вложенным Tuple для представления столбца geo. JSON можно вставлять в эту таблицу в её текущей структуре: 
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}
В приведённом выше примере данных немного, но, как показано ниже, мы можем обращаться в запросах к столбцам кортежа по их именам, разделённым точками. 
SELECT
 address.street,
 company.name
FROM people

┌─address.street────┬─company.name─┐
│ ['Victor Plains'] │ ClickHouse   │
└───────────────────┴──────────────┘
Обратите внимание, что столбец address.street возвращается как Array. Чтобы обратиться к конкретному объекту внутри массива по его позиции, после имени столбца нужно указать смещение в массиве. Например, чтобы получить улицу из первого адреса: 
SELECT address.street[1] AS street
FROM people

┌─street────────┐
│ Victor Plains │
└───────────────┘

1 строка в наборе. Elapsed: 0.001 sec.
Подстолбцы также можно использовать в ключах сортировки, начиная с 24.12: 
CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date
)
ENGINE = MergeTree
ORDER BY company.name

​
Обработка значений по умолчанию

Даже если объекты JSON имеют структуру, они часто бывают разреженными: в них передаётся лишь часть известных ключей. К счастью, тип Tuple не требует наличия всех столбцов в полезной нагрузке JSON. Если какие-то из них не указаны, будут использованы значения по умолчанию. Рассмотрим таблицу people из предыдущего примера и следующий разреженный JSON, в котором отсутствуют ключи suite, geo, phone_numbers и catchPhrase. 
{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771"
    }
  ],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse"
  },
  "dob": "2007-03-31"
}
Ниже видно, что эту строку можно успешно вставить: 
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","city":"Wisokyburgh","zipcode":"90566-7771"}],"website":"clickhouse.com","company":{"name":"ClickHouse"},"dob":"2007-03-31"}

Ok.

1 строка в наборе. Elapsed: 0.002 sec.
Из запроса к этой единственной строке видно, что для столбцов (включая вложенные объекты), которые не были указаны, используются значения по умолчанию: 
SELECT *
FROM people
FORMAT PrettyJSONEachRow

{
  "id": "1",
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "city": "Wisokyburgh",
      "geo": {
        "lat": 0,
        "lng": 0
      },
      "street": "Victor Plains",
      "suite": "",
      "zipcode": "90566-7771"
    }
  ],
  "phone_numbers": [],
  "website": "clickhouse.com",
  "company": {
    "catchPhrase": "",
    "name": "ClickHouse"
  },
  "dob": "2007-03-31"
}

1 строка в наборе. Elapsed: 0.001 sec.
Различие между пустым значением и nullЕсли вам нужно различать пустое значение и отсутствие значения, можно использовать тип Nullable. Однако этого следует избегать, если только это не является абсолютно необходимым, поскольку это отрицательно скажется на хранении и производительности запросов для этих столбцов.

​
Обработка новых столбцов

Хотя структурированный подход проще всего применять, когда ключи JSON статичны, его по-прежнему можно использовать, если изменения в схеме можно спланировать, то есть если новые ключи известны заранее и схему можно соответствующим образом изменить. Обратите внимание, что ClickHouse по умолчанию игнорирует ключи JSON, переданные в полезной нагрузке, если они отсутствуют в схеме. Рассмотрим следующий изменённый JSON с добавлением ключа nickname: 
{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "nickname": "Clicky",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": [
    "010-692-6593",
    "020-192-3333"
  ],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics"
  },
  "dob": "2007-03-31"
}
Этот JSON можно успешно вставить, проигнорировав ключ nickname: 
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

Ok.

1 строка в наборе. Elapsed: 0.002 sec.
Столбцы можно добавлять в схему с помощью команды ALTER TABLE ADD COLUMN. Значение по умолчанию можно задать с помощью конструкции DEFAULT; оно будет использоваться, если его не указать при последующих вставках. Для строк, в которых это значение отсутствует (поскольку они были вставлены до его создания), также будет возвращаться это значение по умолчанию. Если значение DEFAULT не указано, будет использоваться значение по умолчанию для этого типа. Например: 
-- вставка начальной строки (nickname будет проигнорирован)
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

-- добавление столбца
ALTER TABLE people
 (ADD COLUMN `nickname` String DEFAULT 'no_nickname')

-- вставка новой строки (те же данные, другой id)
INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

-- выборка 2 строк
SELECT id, nickname FROM people

┌─id─┬─nickname────┐
│  2 │ Clicky      │
│  1 │ no_nickname │
└────┴─────────────┘

2 rows in set. Elapsed: 0.001 sec.

​
Обработка полуструктурированных/динамических структур

Если JSON-данные являются полуструктурированными, то есть ключи могут динамически добавляться и/или иметь несколько типов, рекомендуется тип JSON. Точнее, используйте тип JSON, если ваши данные:
  • Содержат непредсказуемые ключи, которые со временем могут меняться.
  • Содержат значения разных типов (например, путь иногда может содержать строку, а иногда число).
  • Требуют гибкости схемы, когда строгая типизация непрактична.
  • У вас есть сотни или даже тысячи путей, которые статичны, но явно объявлять их просто нереалистично. Это, как правило, встречается редко.
Рассмотрим предыдущий пример JSON с person, где объект company.labels был определён как динамический. Предположим, что company.labels содержит произвольные ключи. Кроме того, тип значения любого ключа в этой структуре может различаться от строки к строке. Например: 
{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": [
    "010-692-6593",
    "020-192-3333"
  ],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics",
    "labels": {
      "type": "database systems",
      "founded": "2021",
      "employees": 250
    }
  },
  "dob": "2007-03-31",
  "tags": {
    "hobby": "Databases",
    "holidays": [
      {
        "year": 2024,
        "location": "Azores, Portugal"
      }
    ],
    "car": {
      "model": "Tesla",
      "year": 2023
    }
  }
}

{
  "id": 2,
  "name": "Analytica Rowe",
  "username": "Analytica",
  "address": [
    {
      "street": "Maple Avenue",
      "suite": "Apt. 402",
      "city": "Dataford",
      "zipcode": "11223-4567",
      "geo": {
        "lat": 40.7128,
        "lng": -74.006
      }
    }
  ],
  "phone_numbers": [
    "123-456-7890",
    "555-867-5309"
  ],
  "website": "fastdata.io",
  "company": {
    "name": "FastData Inc.",
    "catchPhrase": "Streamlined analytics at scale",
    "labels": {
      "type": [
        "real-time processing"
      ],
      "founded": 2019,
      "dissolved": 2023,
      "employees": 10
    }
  },
  "dob": "1992-07-15",
  "tags": {
    "hobby": "Running simulations",
    "holidays": [
      {
        "year": 2023,
        "location": "Kyoto, Japan"
      }
    ],
    "car": {
      "model": "Audi e-tron",
      "year": 2022
    }
  }
}
Учитывая динамический характер столбца company.labels в разных объектах с точки зрения ключей и типов, у нас есть несколько вариантов моделирования этих данных:
  • Один JSON-столбец - представляет всю схему в виде одного столбца JSON, позволяя всем вложенным структурам оставаться динамическими.
  • Целевой JSON-столбец - использовать тип JSON только для столбца company.labels, сохраняя для всех остальных столбцов структурированную схему, описанную выше.
Хотя первый подход не соответствует предыдущей методологии, подход с одним JSON-столбцом полезен для прототипирования и задач дата-инжиниринга. Для production-развертываний ClickHouse в большом масштабе мы рекомендуем явно задавать структуру и по возможности использовать тип JSON для отдельных динамических подструктур. Строгая схема имеет ряд преимуществ:
  • Валидация данных – использование строгой схемы позволяет избежать риска разрастания столбцов за пределами явно заданных структур.
  • Позволяет избежать риска разрастания столбцов - Хотя тип JSON может масштабироваться до тысяч столбцов, где подстолбцы хранятся как отдельные столбцы, это может привести к лавинообразному росту числа файлов столбцов, что негативно сказывается на производительности. Чтобы уменьшить этот риск, базовый тип Dynamic, используемый в JSON, поддерживает параметр max_dynamic_paths, который ограничивает число уникальных путей, сохраняемых как отдельные файлы столбцов. После достижения этого порога дополнительные пути сохраняются в общем файле столбца в компактном закодированном формате, что позволяет сохранить производительность и эффективность хранения, одновременно поддерживая гибкую ингестию данных. Однако доступ к этому общему файлу столбца менее производителен. При этом JSON-столбец можно использовать с подсказками типов. Столбцы с подсказками обеспечивают ту же производительность, что и отдельные столбцы.
  • Более простая интроспекция путей и типов - Хотя тип JSON поддерживает функции интроспекции для определения выведенных типов и путей, статические структуры бывает проще исследовать, например с помощью DESCRIBE.

​
Один JSON-столбец

Этот подход полезен для прототипирования и задач дата-инжиниринга. Для продакшена старайтесь использовать JSON только для динамических вложенных структур там, где это действительно необходимо.
Соображения по производительностиОдин JSON-столбец можно оптимизировать, пропуская (не сохраняя) JSON-пути, которые не нужны, и используя подсказки типов. Подсказки типов позволяют пользователю явно задавать тип для подстолбца, тем самым избегая вывода типов и дополнительной косвенной обработки во время выполнения запроса. Это позволяет добиться той же производительности, как если бы использовалась явная схема. Подробнее см. в разделе “Использование подсказок типов и пропуск путей”.
Схема для одного JSON-столбца здесь проста: 
SET enable_json_type = 1;

CREATE TABLE people
(
    `json` JSON(username String)
)
ENGINE = MergeTree
ORDER BY json.username;
Мы указываем подсказку типа для столбца username в определении JSON, поскольку используем его в ключе сортировки/первичном ключе. Это помогает ClickHouse понять, что этот столбец не будет содержать NULL, и определить, какой подстолбец username следует использовать (для каждого типа их может быть несколько, поэтому без этого возникает неоднозначность).
Вставка строк в указанную выше таблицу может выполняться с использованием формата JSONAsObject: 
INSERT INTO people FORMAT JSONAsObject 
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}

1 row in set. Elapsed: 0.028 sec.

INSERT INTO people FORMAT JSONAsObject
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}

1 row in set. Elapsed: 0.004 sec.

SELECT *
FROM people
FORMAT Vertical

Row 1:
──────
json: {"address":[{"city":"Dataford","geo":{"lat":40.7128,"lng":-74.006},"street":"Maple Avenue","suite":"Apt. 402","zipcode":"11223-4567"}],"company":{"catchPhrase":"Streamlined analytics at scale","labels":{"dissolved":"2023","employees":"10","founded":"2019","type":["real-time processing"]},"name":"FastData Inc."},"dob":"1992-07-15","id":"2","name":"Analytica Rowe","phone_numbers":["123-456-7890","555-867-5309"],"tags":{"car":{"model":"Audi e-tron","year":"2022"},"hobby":"Running simulations","holidays":[{"location":"Kyoto, Japan","year":"2023"}]},"username":"Analytica","website":"fastdata.io"}

Row 2:
──────
json: {"address":[{"city":"Wisokyburgh","geo":{"lat":-43.9509,"lng":-34.4618},"street":"Victor Plains","suite":"Suite 879","zipcode":"90566-7771"}],"company":{"catchPhrase":"The real-time data warehouse for analytics","labels":{"employees":"250","founded":"2021","type":"database systems"},"name":"ClickHouse"},"dob":"2007-03-31","email":"clicky@clickhouse.com","id":"1","name":"Clicky McCliickHouse","phone_numbers":["010-692-6593","020-192-3333"],"tags":{"car":{"model":"Tesla","year":"2023"},"hobby":"Databases","holidays":[{"location":"Azores, Portugal","year":"2024"}]},"username":"Clicky","website":"clickhouse.com"}

2 rows in set. Elapsed: 0.005 sec.
Мы можем определить автоматически выявленные подстолбцы и их типы с помощью функций интроспекции. Например: 
SELECT JSONDynamicPathsWithTypes(json) AS paths
FROM people
FORMAT PrettyJsonEachRow

{
    "paths": {
        "address": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
        "company.catchPhrase": "String",
        "company.labels.employees": "Int64",
        "company.labels.founded": "String",
        "company.labels.type": "String",
        "company.name": "String",
        "dob": "Date",
        "email": "String",
        "id": "Int64",
        "name": "String",
        "phone_numbers": "Array(Nullable(String))",
        "tags.car.model": "String",
        "tags.car.year": "Int64",
        "tags.hobby": "String",
        "tags.holidays": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
        "website": "String"
 }
}
{
    "paths": {
        "address": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
        "company.catchPhrase": "String",
        "company.labels.dissolved": "Int64",
        "company.labels.employees": "Int64",
        "company.labels.founded": "Int64",
        "company.labels.type": "Array(Nullable(String))",
        "company.name": "String",
        "dob": "Date",
        "id": "Int64",
        "name": "String",
        "phone_numbers": "Array(Nullable(String))",
        "tags.car.model": "String",
        "tags.car.year": "Int64",
        "tags.hobby": "String",
        "tags.holidays": "Array(JSON(max_dynamic_types=16, max_dynamic_paths=256))",
        "website": "String"
 }
}

2 rows in set. Elapsed: 0.009 sec.
Полный список функций интроспекции см. в “Функции интроспекции” К вложенным путям можно обращаться с помощью нотации .; например. 
SELECT json.name, json.email FROM people

┌─json.name────────────┬─json.email────────────┐
│ Analytica Rowe       │ ᴺᵁᴸᴸ                  │
│ Clicky McCliickHouse │ clicky@clickhouse.com │
└──────────────────────┴───────────────────────┘

2 rows in set. Elapsed: 0.006 sec.
Обратите внимание: столбцы, которых нет в строках, возвращаются со значением NULL. Кроме того, для путей с одним и тем же типом создаётся отдельный подстолбец. Например, для company.labels.type существует подстолбец как типа String, так и типа Array(Nullable(String)). По возможности будут возвращаться оба, но с помощью синтаксиса .: можно обращаться к конкретным подстолбцам: 
SELECT json.company.labels.type
FROM people

┌─json.company.labels.type─┐
│ database systems         │
│ ['real-time processing'] │
└──────────────────────────┘

2 rows in set. Elapsed: 0.007 sec.

SELECT json.company.labels.type.:String
FROM people

┌─json.company⋯e.:`String`─┐
│ ᴺᵁᴸᴸ                     │
│ database systems         │
└──────────────────────────┘

2 rows in set. Elapsed: 0.009 sec.
Чтобы возвращались вложенные объекты, требуется символ ^. Это сделано намеренно, чтобы избежать чтения большого количества столбцов без явного запроса. При обращении к объектам без ^ будет возвращаться NULL, как показано ниже: 
-- вложенные объекты не возвращаются по умолчанию
SELECT json.company.labels
FROM people

┌─json.company.labels─┐
│ ᴺᵁᴸᴸ                │
│ ᴺᵁᴸᴸ                │
└─────────────────────┘

2 rows in set. Elapsed: 0.002 sec.

-- возвращаем вложенные объекты с помощью нотации ^
SELECT json.^company.labels
FROM people

┌─json.^`company`.labels─────────────────────────────────────────────────────────────────┐
│ {"employees":"250","founded":"2021","type":"database systems"}                         │
│ {"dissolved":"2023","employees":"10","founded":"2019","type":["real-time processing"]} │
└────────────────────────────────────────────────────────────────────────────────────────┘

2 rows in set. Elapsed: 0.004 sec.

​
Целевой JSON-столбец

Хотя это удобно при прототипировании и решении задач по инженерии данных, в продакшн-среде мы рекомендуем по возможности использовать явную схему. Предыдущий пример можно смоделировать, используя единственный столбец типа JSON для столбца company.labels. 
CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String, labels JSON),
    `dob` Date,
    `tags` String
)
ENGINE = MergeTree
ORDER BY username
Вставку данных в эту таблицу можно выполнить в формате JSONEachRow: 
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}

1 row in set. Elapsed: 0.450 sec.

INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}

1 row in set. Elapsed: 0.440 sec.

SELECT *
FROM people
FORMAT Vertical

Row 1:
──────
id:            2
name:          Analytica Rowe
username:      Analytica
email:
address:       [('Dataford',(40.7128,-74.006),'Maple Avenue','Apt. 402','11223-4567')]
phone_numbers: ['123-456-7890','555-867-5309']
website:       fastdata.io
company:       ('Streamlined analytics at scale','FastData Inc.','{"dissolved":"2023","employees":"10","founded":"2019","type":["real-time processing"]}')
dob:           1992-07-15
tags:          {"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}

Row 2:
──────
id:            1
name:          Clicky McCliickHouse
username:      Clicky
email:         clicky@clickhouse.com
address:       [('Wisokyburgh',(-43.9509,-34.4618),'Victor Plains','Suite 879','90566-7771')]
phone_numbers: ['010-692-6593','020-192-3333']
website:       clickhouse.com
company:       ('The real-time data warehouse for analytics','ClickHouse','{"employees":"250","founded":"2021","type":"database systems"}')
dob:           2007-03-31
tags:          {"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}

2 строки в наборе. Прошло: 0.005 сек.
Функции интроспекции можно использовать для определения выведенных путей и типов для столбца company.labels. 
SELECT JSONDynamicPathsWithTypes(company.labels) AS paths
FROM people
FORMAT PrettyJsonEachRow

{
    "paths": {
        "dissolved": "Int64",
        "employees": "Int64",
        "founded": "Int64",
        "type": "Array(Nullable(String))"
 }
}
{
    "paths": {
        "employees": "Int64",
        "founded": "String",
        "type": "String"
 }
}

2 строки в наборе. Прошло: 0.003 сек.

​
Использование подсказок типов и пропускаемых путей

Подсказки типов позволяют задавать тип для пути и его подстолбца, избегая ненужного вывода типов. Рассмотрим следующий пример, в котором мы задаём типы для JSON-ключей dissolved, employees и founded в JSON-столбце company.labels 
CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(
        city String,
        geo Tuple(
            lat Float32,
            lng Float32),
        street String,
        suite String,
        zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(
        catchPhrase String,
        name String,
        labels JSON(dissolved UInt16, employees UInt16, founded UInt16)),
    `dob` Date,
    `tags` String
)
ENGINE = MergeTree
ORDER BY username

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}

1 row in set. Elapsed: 0.450 sec.

INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}

1 row in set. Elapsed: 0.440 sec.
Обратите внимание, что у этих столбцов теперь явно заданы типы: 
SELECT JSONAllPathsWithTypes(company.labels) AS paths
FROM people
FORMAT PrettyJsonEachRow

{
    "paths": {
        "dissolved": "UInt16",
        "employees": "UInt16",
        "founded": "UInt16",
        "type": "String"
 }
}
{
    "paths": {
        "dissolved": "UInt16",
        "employees": "UInt16",
        "founded": "UInt16",
        "type": "Array(Nullable(String))"
 }
}

2 rows in set. Elapsed: 0.003 sec.
Кроме того, мы можем пропускать пути в JSON, которые не нужно хранить, с помощью параметров SKIP and SKIP REGEXP, чтобы сократить объём хранилища и избежать лишнего вывода типов для ненужных путей. Например, предположим, что для приведённых выше данных используется один JSON-столбец. Мы можем пропустить пути address и company:” 
CREATE TABLE people
(
    `json` JSON(username String, SKIP address, SKIP company)
)
ENGINE = MergeTree
ORDER BY json.username

INSERT INTO people FORMAT JSONAsObject
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021","employees":250}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}

1 row in set. Elapsed: 0.450 sec.

INSERT INTO people FORMAT JSONAsObject
{"id":2,"name":"Analytica Rowe","username":"Analytica","address":[{"street":"Maple Avenue","suite":"Apt. 402","city":"Dataford","zipcode":"11223-4567","geo":{"lat":40.7128,"lng":-74.006}}],"phone_numbers":["123-456-7890","555-867-5309"],"website":"fastdata.io","company":{"name":"FastData Inc.","catchPhrase":"Streamlined analytics at scale","labels":{"type":["real-time processing"],"founded":2019,"dissolved":2023,"employees":10}},"dob":"1992-07-15","tags":{"hobby":"Running simulations","holidays":[{"year":2023,"location":"Kyoto, Japan"}],"car":{"model":"Audi e-tron","year":2022}}}

1 row in set. Elapsed: 0.440 sec.
Обратите внимание, что эти столбцы исключены из данных: 
SELECT *
FROM people
FORMAT PrettyJSONEachRow

{
    "json": {
        "dob" : "1992-07-15",
        "id" : "2",
        "name" : "Analytica Rowe",
        "phone_numbers" : [
            "123-456-7890",
            "555-867-5309"
        ],
        "tags" : {
            "car" : {
                "model" : "Audi e-tron",
                "year" : "2022"
            },
            "hobby" : "Running simulations",
            "holidays" : [
                {
                    "location" : "Kyoto, Japan",
                    "year" : "2023"
                }
            ]
        },
        "username" : "Analytica",
        "website" : "fastdata.io"
    }
}
{
    "json": {
        "dob" : "2007-03-31",
        "email" : "clicky@clickhouse.com",
        "id" : "1",
        "name" : "Clicky McCliickHouse",
        "phone_numbers" : [
            "010-692-6593",
            "020-192-3333"
        ],
        "tags" : {
            "car" : {
                "model" : "Tesla",
                "year" : "2023"
            },
            "hobby" : "Databases",
            "holidays" : [
                {
                    "location" : "Azores, Portugal",
                    "year" : "2024"
                }
            ]
        },
        "username" : "Clicky",
        "website" : "clickhouse.com"
    }
}

2 rows in set. Elapsed: 0.004 sec.

​
Оптимизация производительности с помощью подсказок типов

Подсказки типов — это не просто способ избежать ненужного вывода типов: они полностью устраняют дополнительный уровень косвенности при хранении и обработке, а также позволяют задавать оптимальные примитивные типы. JSON-пути с подсказками типов всегда хранятся так же, как и обычные столбцы, поэтому не требуются столбцы-дискриминаторы или динамическое разрешение на этапе выполнения запроса. Это означает, что при наличии чётко заданных подсказок типов вложенные ключи JSON дают ту же производительность и эффективность, как если бы они с самого начала были смоделированы как столбцы верхнего уровня. В результате для наборов данных, которые в целом однородны, но при этом выигрывают от гибкости JSON, подсказки типов дают удобный способ сохранить производительность без необходимости перестраивать схему или конвейер приёма.

​
Настройка динамических путей

ClickHouse хранит каждый JSON-путь как подстолбец в по-настоящему столбцовой структуре, обеспечивая те же преимущества по производительности, что и у традиционных столбцов, — такие как сжатие, обработка с ускорением SIMD и минимальный дисковый I/O. Каждая уникальная комбинация пути и типа в ваших JSON-данных может стать отдельным файлом столбца на диске. Например, когда в два JSON-пути вставляются значения разных типов, ClickHouse хранит значения каждого конкретного типа в отдельных подстолбцах. К этим подстолбцам можно обращаться независимо, что сводит к минимуму лишний I/O. Обратите внимание, что при запросе столбца с несколькими типами его значения по-прежнему возвращаются как единый столбцовый ответ. Кроме того, используя смещения, ClickHouse гарантирует, что эти подстолбцы остаются плотными, без хранения значений по умолчанию для отсутствующих JSON-путей. Такой подход максимизирует сжатие и дополнительно сокращает I/O. Однако в сценариях с JSON-структурами высокой кардинальности или сильно изменяющейся структурой — например, в конвейерах телеметрии, журналах или хранилищах признаков для машинного обучения — такое поведение может привести к взрывному росту числа файлов столбцов. Каждый новый уникальный JSON-путь приводит к созданию нового файла столбца, а каждый вариант типа в рамках этого пути — к созданию дополнительного файла столбца. Хотя это оптимально для производительности чтения, возникают эксплуатационные сложности: исчерпание дескрипторов файлов, повышенное использование памяти и более медленные слияния из-за большого количества мелких файлов. Чтобы смягчить эту проблему, ClickHouse вводит понятие overflow-подстолбца: как только число различных JSON-путей превышает порог, дополнительные пути сохраняются в одном общем файле в компактном кодированном формате. Этот файл по-прежнему можно запрашивать, но он не обладает теми же характеристиками производительности, что и выделенные подстолбцы. Этот порог задаётся параметром max_dynamic_paths в объявлении типа JSON. 
CREATE TABLE logs
(
    payload JSON(max_dynamic_paths = 500)
)
ENGINE = MergeTree
ORDER BY tuple();
Не задавайте этому параметру слишком большое значение — большие значения увеличивают потребление ресурсов и снижают эффективность. Как правило, значение должно быть меньше 10 000. Для рабочих нагрузок с сильно меняющейся структурой используйте подсказки типов и параметры SKIP, чтобы ограничить объём сохраняемых данных. Пользователям, которым интересна реализация этого нового типа столбца, рекомендуем прочитать нашу подробную статью в блоге “A New Powerful JSON Data Type for ClickHouse”.
Последнее изменение 10 июня 2026 г.