JSON Functions

Returns true for rows in a Dynamic column that are stored in shared variant format rather than as separate subcolumns.

When a Dynamic column has a `max_types` limit, values that exceed this limit are stored in a shared binary format
instead of being separated into individual typed subcolumns. This function identifies which rows are stored in this shared format.

**Syntax**

```
isDynamicElementInSharedData(dynamic)
```

**Arguments**

- `dynamic` — Dynamic column to inspect. [`Dynamic`](/docs/sql-reference/data-types/dynamic)

**Returned value**

Returns true if the value is stored in shared variant format, false if stored as a separate subcolumn or is NULL. [`Bool`](/docs/sql-reference/data-types/boolean)

**Examples**

**Checking storage format in Dynamic column with max\_types limit**

```
CREATE TABLE test (d Dynamic(max_types=2)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, isDynamicElementInSharedData(d) FROM test;
```

```
┌─d─────────────┬─isDynamicElementInSharedData(d)─┐
│ ᴺᵁᴸᴸ          │ false                           │
│ 42            │ false                           │
│ Hello, World! │ true                            │
│ [1,2,3]       │ true                            │
└───────────────┴─────────────────────────────────┘
```

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

Introduced in: v20.1.0

Checks that the string passed is valid JSON.

**Syntax**

```
isValidJSON(json)
```

**Arguments**

- `json` — JSON string to validate [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `1` if the string is valid JSON, otherwise `0`. [`UInt8`](/docs/sql-reference/data-types/int-uint)

**Examples**

**Usage example**

```
SELECT isValidJSON('{"a": "hello", "b": [-100, 200.0, 300]}') = 1;
SELECT isValidJSON('not JSON') = 0;
```

```
1
0
```

**Using integers to access both JSON arrays and JSON objects**

```
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 0);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 1);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 2);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', -1);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', -2);
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 3);
```

```
0
1
1
1
1
1
0
```

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

Introduced in: v21.4.0

Parses a true/false value from the value of the field named `field_name`.
The result is `UInt8`.

**Syntax**

```
simpleJSONExtractBool(json, field_name)
```

**Aliases**: `visitParamExtractBool`

**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 value of the field is `true`, `0` otherwise. This means this function will return `0` including (and not only) in the following cases:

- If the field doesn't exists.
- If the field contains `true` as a string, e.g.: `{"field":"true"}`.
- If the field contains `1` as a numerical value. [`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":false,"bar":true}');
INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

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

```
0
1
0
0
```

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

Introduced in: v21.4.0

Parses `Float64` 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**

```
simpleJSONExtractFloat(json, field_name)
```

**Aliases**: `visitParamExtractFloat`

**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, otherwise `0`. [`Float64`](/docs/sql-reference/data-types/float)

**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 simpleJSONExtractFloat(json, 'foo') FROM jsons ORDER BY json;
```

```
0
-4000
0
-3.4
5
```

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

Introduced in: v21.4.0

Parses `Int64` 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**

```
simpleJSONExtractInt(json, field_name)
```

**Aliases**: `visitParamExtractInt`

**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 [`Int64`](/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 simpleJSONExtractInt(json, 'foo') FROM jsons ORDER BY json;
```

```
0
-4
0
-3
5
```

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

Introduced in: v21.4.0

Returns the value of the field named `field_name` as a `String`, including separators.

**Syntax**

```
simpleJSONExtractRaw(json, field_name)
```

**Aliases**: `visitParamExtractRaw`

**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 value of the field as a string, including separators if the field exists, or an empty string otherwise [`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":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":{"def":[1,2,3]}}');
INSERT INTO jsons VALUES ('{"baz":2}');

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

```
"-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**