JSON Functions

```
"-4e3"
-3.4
5
{"def":[1,2,3]}
```

## simpleJSONExtractString[​](#simpleJSONExtractString "Direct link to simpleJSONExtractString")

Introduced in: v21.4.0

Parses `String` in double quotes from the value of the field named `field_name`.

**Implementation details**

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).

**Syntax**

```
simpleJSONExtractString(json, field_name)
```

**Aliases**: `visitParamExtractString`

**Arguments**

- `json` — The JSON in which the field is searched for. [`String`](/docs/sql-reference/data-types/string)
- `field_name` — The name of the field to search for. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the unescaped value of a field as a string, including separators. An empty string is returned if the field doesn't contain a double quoted string, if unescaping fails or if the field doesn't exist [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"\\n\\u0000"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263a"}');
INSERT INTO jsons VALUES ('{"foo":"hello}');

SELECT simpleJSONExtractString(json, 'foo') FROM jsons ORDER BY json;
```

```
\n\0

☺
```

## simpleJSONExtractUInt[​](#simpleJSONExtractUInt "Direct link to simpleJSONExtractUInt")

Introduced in: v21.4.0

Parses `UInt64` from the value of the field named `field_name`.
If `field_name` is a string field, it tries to parse a number from the beginning of the string.
If the field does not exist, or it exists but does not contain a number, it returns `0`.

**Syntax**

```
simpleJSONExtractUInt(json, field_name)
```

**Aliases**: `visitParamExtractUInt`

**Arguments**

- `json` — The JSON in which the field is searched for. [`String`](/docs/sql-reference/data-types/string)
- `field_name` — The name of the field to search for. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns the number parsed from the field if the field exists and contains a number, `0` otherwise [`UInt64`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"4e3"}');
INSERT INTO jsons VALUES ('{"foo":3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractUInt(json, 'foo') FROM jsons ORDER BY json;
```

```
0
4
0
3
5
```

## simpleJSONHas[​](#simpleJSONHas "Direct link to simpleJSONHas")

Introduced in: v21.4.0

Checks whether there is a field named `field_name`.

**Syntax**

```
simpleJSONHas(json, field_name)
```

**Aliases**: `visitParamHas`

**Arguments**

- `json` — The JSON in which the field is searched for. [`String`](/docs/sql-reference/data-types/string)
- `field_name` — The name of the field to search for. [`const String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `1` if the field exists, `0` otherwise [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONHas(json, 'foo') FROM jsons;
SELECT simpleJSONHas(json, 'bar') FROM jsons;
```

```
1
0
```

## toJSONString[​](#toJSONString "Direct link to toJSONString")

Introduced in: v21.7.0

Serializes a value to its JSON representation. Various data types and nested structures are supported.
64-bit [integers](/docs/sql-reference/data-types/int-uint) or bigger (like `UInt64` or `Int128`) are enclosed in quotes by default. [output\_format\_json\_quote\_64bit\_integers](/docs/operations/settings/formats#output_format_json_quote_64bit_integers) controls this behavior.
Special values `NaN` and `inf` are replaced with `null`. Enable [output\_format\_json\_quote\_denormals](/docs/operations/settings/formats#output_format_json_quote_denormals) setting to show them.
When serializing an [Enum](/docs/sql-reference/data-types/enum) value, the function outputs its name.

See also:

- [output\_format\_json\_quote\_64bit\_integers](/docs/operations/settings/formats#output_format_json_quote_64bit_integers)
- [output\_format\_json\_quote\_denormals](/docs/operations/settings/formats#output_format_json_quote_denormals)

**Syntax**

```
toJSONString(value)
```

**Arguments**

- `value` — Value to serialize. Value may be of any data type. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns the JSON representation of the value. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Map serialization**

```
SELECT toJSONString(map('key1', 1, 'key2', 2));
```

```
┌─toJSONString(map('key1', 1, 'key2', 2))─┐
│ {"key1":1,"key2":2}                     │
└─────────────────────────────────────────┘
```

**Special values**

```
SELECT toJSONString(tuple(1.25, NULL, NaN, +inf, -inf, [])) SETTINGS output_format_json_quote_denormals = 1;
```

```
┌─toJSONString(tuple(1.25, NULL, NaN, plus(inf), minus(inf), []))─┐
│ [1.25,null,"nan","inf","-inf",[]]                               │
└─────────────────────────────────────────────────────────────────┘
```

- [Types of JSON functions](#types-of-functions)
  - [simpleJSON (visitParam) functions](#simplejson-visitparam-functions)- [JSONExtract functions](#jsonextract-functions)- [Case-Insensitive JSONExtract Functions](#case-insensitive-jsonextract-functions)- [JSONAllPaths](#JSONAllPaths)- [JSONAllPathsWithTypes](#JSONAllPathsWithTypes)- [JSONAllValues](#JSONAllValues)- [JSONArrayLength](#JSONArrayLength)- [JSONDynamicPaths](#JSONDynamicPaths)- [JSONDynamicPathsWithTypes](#JSONDynamicPathsWithTypes)- [JSONExtract](#JSONExtract)- [JSONExtractArrayRaw](#JSONExtractArrayRaw)- [JSONExtractArrayRawCaseInsensitive](#JSONExtractArrayRawCaseInsensitive)- [JSONExtractBool](#JSONExtractBool)- [JSONExtractBoolCaseInsensitive](#JSONExtractBoolCaseInsensitive)- [JSONExtractCaseInsensitive](#JSONExtractCaseInsensitive)- [JSONExtractFloat](#JSONExtractFloat)- [JSONExtractFloatCaseInsensitive](#JSONExtractFloatCaseInsensitive)- [JSONExtractInt](#JSONExtractInt)- [JSONExtractIntCaseInsensitive](#JSONExtractIntCaseInsensitive)- [JSONExtractKeys](#JSONExtractKeys)- [JSONExtractKeysAndValues](#JSONExtractKeysAndValues)- [JSONExtractKeysAndValuesCaseInsensitive](#JSONExtractKeysAndValuesCaseInsensitive)- [JSONExtractKeysAndValuesRaw](#JSONExtractKeysAndValuesRaw)- [JSONExtractKeysAndValuesRawCaseInsensitive](#JSONExtractKeysAndValuesRawCaseInsensitive)- [JSONExtractKeysCaseInsensitive](#JSONExtractKeysCaseInsensitive)- [JSONExtractRaw](#JSONExtractRaw)- [JSONExtractRawCaseInsensitive](#JSONExtractRawCaseInsensitive)- [JSONExtractString](#JSONExtractString)- [JSONExtractStringCaseInsensitive](#JSONExtractStringCaseInsensitive)- [JSONExtractUInt](#JSONExtractUInt)- [JSONExtractUIntCaseInsensitive](#JSONExtractUIntCaseInsensitive)- [JSONHas](#JSONHas)- [JSONKey](#JSONKey)- [JSONLength](#JSONLength)- [JSONMergePatch](#JSONMergePatch)- [JSONSharedDataPaths](#JSONSharedDataPaths)- [JSONSharedDataPathsWithTypes](#JSONSharedDataPathsWithTypes)- [JSONType](#JSONType)- [JSON\_EXISTS](#JSON_EXISTS)- [JSON\_QUERY](#JSON_QUERY)- [JSON\_VALUE](#JSON_VALUE)- [dynamicElement](#dynamicElement)- [dynamicType](#dynamicType)- [isDynamicElementInSharedData](#isDynamicElementInSharedData)- [isValidJSON](#isValidJSON)- [simpleJSONExtractBool](#simpleJSONExtractBool)- [simpleJSONExtractFloat](#simpleJSONExtractFloat)- [simpleJSONExtractInt](#simpleJSONExtractInt)- [simpleJSONExtractRaw](#simpleJSONExtractRaw)- [simpleJSONExtractString](#simpleJSONExtractString)- [simpleJSONExtractUInt](#simpleJSONExtractUInt)- [simpleJSONHas](#simpleJSONHas)- [toJSONString](#toJSONString)

Was this page helpful?