Converts a JSON string into a VARIANT value.
Note
This function is supported only in Ververica Runtime (VVR) 11.1.0 or later.
Syntax
VARIANT PARSE_JSON(VARCHAR jsonStr [, BOOLEAN allowDuplicateKeys])Arguments
| Argument | Data type | Description |
|---|---|---|
jsonStr | VARCHAR | The JSON string to parse. |
allowDuplicateKeys | BOOLEAN | (Optional) Whether to allow duplicate keys. true: retains the value of the last duplicate key. false (default): raises an error if duplicate keys exist. |
Usage notes
If
jsonStris NULL, the function returns NULL.
PARSE_JSON vs. TRY_PARSE_JSON
| Function | Error handling | Return value for invalid JSON | When to use |
|---|---|---|---|
| PARSE_JSON | Raises an error | Error | Strict input validation is required |
| TRY_PARSE_JSON | Tolerates errors | NULL | Invalid input is acceptable |
Examples
Basic usage
Input table T1:
| jsonString (VARCHAR) |
|---|
| {"k": "v1", "k2": 1} |
SELECT PARSE_JSON(jsonString) AS v
FROM T1;Result:
| v (VARIANT) |
|---|
| {"k": "v1", "k2": 1} |
Handling duplicate keys
By default, PARSE_JSON raises an error when duplicate keys are present:
SELECT PARSE_JSON('{"a": "123", "b": "456", "a": "789"}') AS v;To retain the last value of each duplicate key, set allowDuplicateKeys to true:
SELECT PARSE_JSON('{"a": "123", "b": "456", "a": "789"}', true) AS v;