JSON Functions - Clickhouse

Types of JSON functions {#types-of-functions}

There are two sets of functions to parse JSON:

simpleJSON* (visitParam*) which is made for parsing a limited subset of JSON extremely fast.
JSONExtract* which is made for parsing ordinary JSON.

simpleJSON (visitParam) functions {#simplejson-visitparam-functions}

ClickHouse has special functions for working with simplified JSON. All these JSON functions are based on strong assumptions about what the JSON can be. They try to do as little as possible to get the job done as quickly as possible.

The following assumptions are made:

The field name (function argument) must be a constant.
The field name is somehow canonically encoded in JSON. For example: simpleJSONHas('{"abc":"def"}', 'abc') = 1, but simpleJSONHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
Fields are searched for on any nesting level, indiscriminately. If there are multiple matching fields, the first occurrence is used.
The JSON does not have space characters outside of string literals.

JSONExtract functions {#jsonextract-functions}

These functions are based on simdjson, and designed for more complex JSON parsing requirements.

Case-Insensitive JSONExtract Functions {#case-insensitive-jsonextract-functions}

These functions perform ASCII case-insensitive key matching when extracting values from JSON objects. They work identically to their case-sensitive counterparts, except that object keys are matched without regard to case. When multiple keys match with different cases, the first match is returned.

:::note These functions may be less performant than their case-sensitive counterparts, so use the regular JSONExtract functions if possible. :::