Extracts the value at a specified path from a JSON string and returns it as a VARCHAR string. If the path matches an array or object, the matched structure is serialized to a VARCHAR string rather than returned as a native type.
Limitations
JSON_VALUE is supported only in Realtime Compute for Apache Flink that uses Ververica Runtime (VVR) 3.0.0 or later.
Syntax
VARCHAR JSON_VALUE(VARCHAR content, VARCHAR path)Parameters
| Parameter | Data type | Description |
|---|---|---|
content | VARCHAR | The JSON string to parse. The parameter type must be VARCHAR. If it's not, use CAST to convert it first — passing a non-VARCHAR value directly can cause parsing errors and unexpected NULL results. |
path | VARCHAR | The path expression that identifies the value to extract. Enclose the path in single quotation marks ('). Example: JSON_VALUE(json,'$.passenger_name') AS ABC |
The path parameter supports the following symbols:
| Symbol | Meaning |
|---|---|
$ | Root object |
[] | Array subscript |
* | Array wildcard |
. | Child element |
Usage notes
If
contentcontains invalid JSON, or if either parameter isNULL,NULLis returned.If the path matches an array or object, the result is a
VARCHARstring representation of that structure (for example,[30,40]), not a native array or object.
Examples
The following examples use a single SELECT statement against table T1 to show how different content and path combinations affect the output.
Table T1:
| id (INT) | json (VARCHAR) | path1 (VARCHAR) |
|---|---|---|
| 1 | [10, 20, [30, 40]] | $[2][*] |
| 2 | {"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"} | $.ccc.hhh[*] |
| 3 | {"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"} | $.ccc.hhh[1] |
| 4 | [10, 20, [30, 40]] | NULL |
| 5 | NULL | $[2][*] |
| 6 | "{xx]" | "$[2][*]" |
Test statement:
SELECT
id,
JSON_VALUE(json, path1) AS `value`
FROM
T1;Results:
| id (INT) | value (VARCHAR) | Notes |
|---|---|---|
| 1 | [30,40] | Array at $[2] — all elements via *, serialized to VARCHAR |
| 2 | ["h0","h1","h2"] | Nested array at $.ccc.hhh — all elements via *, serialized to VARCHAR |
| 3 | h1 | Single element at index 1 in the hhh array |
| 4 | NULL | path1 is NULL |
| 5 | NULL | json is NULL |
| 6 | NULL | json is invalid JSON |