A special version of PARSE_JSON that returns NULL instead of raising an error when parsing fails.
Limits
Supported only in Ververica Runtime (VVR) 11.1.0 or later.
Syntax
VARIANT TRY_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 in the JSON string. true: retains the last value for each duplicate key. false (default): returns NULL if duplicate keys appear. |
Returns
Returns a VARIANT value containing a JSON document.
Returns NULL if:
The input
jsonStris NULL.The JSON string is invalid and cannot be parsed.
allowDuplicateKeysisfalse(default) and the JSON string contains duplicate keys.
Comparison of PARSE_JSON and TRY_PARSE_JSON
| Function | Error handling mechanism | Return value for invalid JSON input | Suitable scenarios |
|---|---|---|---|
PARSE_JSON | Explicitly raises errors. | Returns an error. | Strict input validation is required. |
TRY_PARSE_JSON | Tolerates errors. | Returns NULL. | Tolerance for invalid input is needed. |
Example
The T1 table contains the following test data:
| jsonString (VARCHAR) |
|---|
{"k": "v1", "k2": 1} |
{ |
Run the following query:
SELECT TRY_PARSE_JSON(jsonString) AS v
FROM T1;Results:
| v (VARIANT) |
|---|
{"k": "v1", "k2": 1} |
| NULL |
The second row contains invalid JSON ({), so TRY_PARSE_JSON returns NULL. If you used PARSE_JSON instead, the query would fail with an error.