JSON_VALUE - Realtime Compute for Apache Flink - Alibaba Cloud Documentation Center

Extracts the expression of the specified path from a JSON string.

Limitations

This function 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)

Input parameters

Parameter

Data type

Description

content

VARCHAR

The JSON object that you want to parse.

Note

Parameter type must be VARCHAR. If it's not, use CAST to convert it. Passing non-VARCHAR data directly can cause parsing errors and unexpected NULL results.

path

VARCHAR

The expression of the specified path. You must use single quotation marks (') in a custom path. Example:

JSON_VALUE(json,'$.passenger_name') AS ABC

The following symbols are supported in a path:

$: indicates the root object.
[]: indicates the array subscript.
*: indicates the array wildcard characters.
.: indicates child elements.

Note

If the input parameters contain invalid JSON or NULL values, NULL is returned.

Example

Test data

Table 1 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;

Test result
id (INT)
value (VARCHAR)
1
[30,40]
2
["h0","h1","h2"]
3
h1
4
NULL
5
NULL
6
NULL

References

For more information about the built-in functions that are supported by fully managed Flink, see Supported functions.
For more information about user-defined functions (UDFs), see UDFs and Manage UDFs.

id (INT)	value (VARCHAR)
1	[30,40]
2	["h0","h1","h2"]
3	h1
4	NULL
5	NULL
6	NULL