Designed for parsing a limited subset of JSON extremely fast. These functions make strong assumptions about the JSON structure to optimize performance.

​
ClickHouse function reference

​
simpleJSONHas

Checks whether a field with the specified name exists in a JSON string.

Syntax

simpleJSONHas(json, field_name)

Arguments

  • json (String): JSON string to parse.
  • field_name (String): Name of the field to check for.

Returns

  • Returns 1 if the field exists, 0 otherwise. (UInt8)

Example

SELECT simpleJSONHas('{"taco":"carnitas","salsa":"verde"}', 'salsa');

Result:

| simpleJSONHas('{"taco":"carnitas","salsa":"verde"}', 'salsa') |
|---------------------------------------------------------------|
| 1                                                             |

This function checks if the ‘salsa’ field exists in the JSON string. It returns 1, confirming the field’s presence.

This function:

  • Searches for fields at any nesting level.
  • Uses the first occurrence if multiple matching fields exist.

The JSON string should not contain space characters outside of string literals.

This function is designed for parsing a limited subset of JSON extremely fast, based on certain assumptions about the JSON structure.

​
simpleJSONExtractUInt

Parses a UInt64 value from a JSON string field. If the field is a string, it attempts to parse a number from the beginning of the string. If the field doesn’t exist or doesn’t contain a valid number, it returns 0.

Syntax

simpleJSONExtractUInt(json, field_name)

Arguments

  • json (String): JSON string to parse.
  • field_name (String): Name of the field to extract.

Returns

  • Extracted unsigned 64-bit integer value. (UInt64)

Example

SELECT
	simpleJSONExtractUInt('{"taco_count":42, "salsa_level":"5", "guacamole":2.3}', 'taco_count') AS tacos,
	simpleJSONExtractUInt('{"taco_count":42, "salsa_level":"5", "guacamole":2.3}', 'salsa_level') AS salsa,
	simpleJSONExtractUInt('{"taco_count":42, "salsa_level":"5", "guacamole":2.3}', 'guacamole') AS guac,
	simpleJSONExtractUInt('{"taco_count":42, "salsa_level":"5", "guacamole":2.3}', 'tortilla') AS tortilla;

Result:

| tacos | salsa | guac | tortilla |
|-------|-------|------|----------|
| 42    | 5     | 2    | 0        |

In this example:

  • tacos extracts the integer value directly.
  • salsa parses the number from the beginning of the string “5”.
  • guac returns 0 because the field doesn’t exist in the JSON.
  • tortilla returns 0 because the field doesn’t exist in the JSON.

The JSON string should not contain space characters outside of string literals.

This function is designed for simple JSON parsing and makes strong assumptions about the JSON structure. It searches for fields at any nesting level and uses the first occurrence if multiple fields match. For more complex JSON handling, consider using JSONExtract functions.

​
simpleJSONExtractInt

Parses an Int64 value from a JSON string field. If the field is a string, it attempts to parse a number from the beginning of the string. If the field doesn’t exist or doesn’t contain a valid number, it returns 0.

Syntax

simpleJSONExtractInt(json, field_name)

Alias:

  • visitParamExtractInt

Arguments

  • json (String): JSON string to parse.
  • field_name (String): Name of the field to extract.

Returns

  • Returns the parsed Int64 number if the field exists and contains a valid number, 0 otherwise. [Int64]

Example

SELECT
	simpleJSONExtractInt('{"taco_price":"-4.99", "burrito_price":"5.99", "nacho_price":"not_a_number"}', 'taco_price') AS taco_price,
	simpleJSONExtractInt('{"taco_price":"-4.99", "burrito_price":"5.99", "nacho_price":"not_a_number"}', 'burrito_price') AS burrito_price,
	simpleJSONExtractInt('{"taco_price":"-4.99", "burrito_price":"5.99", "nacho_price":"not_a_number"}', 'nacho_price') AS nacho_price,
	simpleJSONExtractInt('{"taco_price":"-4.99", "burrito_price":"5.99", "nacho_price":"not_a_number"}', 'quesadilla_price') AS quesadilla_price;

Result:

| taco_price | burrito_price | nacho_price | quesadilla_price |
|------------|---------------|-------------|------------------|
| -4         | 5             | 0           | 0                |

In this example:

  • taco_price returns -4 (parsed from “-4.99”)
  • burrito_price returns 5 (parsed from 5.99)
  • nacho_price returns 0 (cannot parse “not_a_number”)
  • quesadilla_price returns 0 (field doesn’t exist)

Note that the function truncates floating-point numbers to integers and only parses the integer part of string values.

The JSON string should not contain space characters outside of string literals.

​
simpleJSONExtractFloat

Parses a Float64 value from a JSON string.

Syntax

simpleJSONExtractFloat(json, field_name)

Alias:

  • visitParamExtractFloat

Arguments

  • json (String): JSON string to parse.
  • field_name (String): Name of the field to extract.

Returns

  • Returns the Float64 value parsed from the specified field if it exists and contains a number. Returns 0 if the field doesn’t exist or contains a non-numeric value. [Float64]

Example

SELECT
	simpleJSONExtractFloat('{"taco_price":4.99, "guacamole":"extra"}', 'taco_price') AS price;

Result:

| price |
|-------|
| 4.99  |

This function attempts to parse a floating-point number from the specified field in the JSON string. If the field contains a string representation of a number (e.g., “4.99”), it will be converted to a Float64. For non-numeric fields or missing fields, the function returns 0.

This function is designed for simple JSON parsing and makes certain assumptions about the JSON structure. For more complex JSON handling, consider using the JSONExtract family of functions.

The JSON string should not contain space characters outside of string literals.

​
simpleJSONExtractBool

Parses a boolean value from a JSON string. Returns 1 if the value is true, 0 otherwise.

Syntax

simpleJSONExtractBool(json, field_name)

Arguments

  • json (String): JSON string to parse.
  • field_name (String): Name of the field to extract.

Returns

  • Returns 1 if the field exists and its value is true, 0 otherwise. [UInt8]

Example

SELECT
	simpleJSONExtractBool('{"has_guacamole":true,"spicy_level":3}', 'has_guacamole') AS guac_status;

Result:

| guac_status |
|-------------|
| 1           |

This function returns 0 in the following cases:

  • If the field doesn’t exist
  • If the field contains “true” as a string (e.g., {"field":"true"})
  • If the field contains 1 as a numerical value

This function is designed for simple JSON parsing and makes several assumptions about the JSON structure. For more complex JSON handling, consider using the JSONExtract functions.

The JSON string should not contain space characters outside of string literals.

​
simpleJSONExtractRaw

Extracts a JSON value as a raw string, including separators.

Syntax:

simpleJSONExtractRaw(json, field_name)

Alias:

  • visitParamExtractRaw

Arguments:

  • json (String): JSON string to parse.
  • field_name (String): Name of the field to extract.

Returns:

  • Returns the value of the field as a raw string, including separators if the field exists, or an empty string otherwise. (String)

Example:

SELECT
	simpleJSONExtractRaw('{"taco_filling":"carnitas","toppings":["salsa","guacamole"]}', 'toppings') AS taco_toppings;

Result:

| taco_toppings          |
|------------------------|
| ["salsa","guacamole"]  |

In this example, simpleJSONExtractRaw extracts the ‘toppings’ array from the JSON string, preserving its structure and separators.

This function is designed for fast parsing of a simplified subset of JSON. It searches for fields at any nesting level and returns the first occurrence. The JSON must not contain spaces outside of string literals.

The JSON string should not contain space characters outside of string literals.

​
simpleJSONExtractString

Parses a JSON string and extracts a string value from the specified field.

Syntax:

simpleJSONExtractString(json, field_name)

Alias:

  • visitParamExtractString

Arguments:

  • json (String): JSON string to parse.
  • field_name (String): Name of the field to extract.

Returns:

  • Returns the unescaped string value of the specified field if it exists and contains a string. Returns an empty string if the field doesn’t exist, contains a non-string value, or if unescaping fails. [String]

Example:

SELECT
	simpleJSONExtractString('{"taco":"al pastor","salsa":"verde"}', 'taco') AS filling;

Result:

| filling   |
|-----------|
| al pastor |

This function is designed for parsing a limited subset of JSON extremely fast. It makes the following assumptions:

  1. The field name must be a constant.
  2. The field name is canonically encoded in JSON.
  3. Fields are searched for on any nesting level, indiscriminately. If there are multiple matching fields, the first occurrence is used.
  4. The JSON does not have space characters outside of string literals.

There is currently no support for code points in the format \uXXXX\uYYYY that are not from the basic multilingual plane (they are converted to CESU-8 instead of UTF-8).

The JSON string should not contain space characters outside of string literals.

Was this page helpful?

OverviewJSONExtract