Extract values from a JSON string using JSON_TUPLE - Realtime Compute for Apache Flink

JSON_TUPLE is a table-valued function that extracts multiple values from a JSON string in a single pass. Unlike scalar functions such as GET_JSON_OBJECT or JSON_VALUE, which return one value per call, JSON_TUPLE accepts one or more path arguments and emits one output row per path argument — use it with a LATERAL TABLE join to expand those rows into your result set.

Version requirement

Ververica Runtime (VVR) 3.0.0+

Syntax

JSON_TUPLE(str, path1, path2, ..., pathN)

Parameters

Parameter	Type	Required	Description
`str`	VARCHAR	Yes	The JSON string to extract values from.
`path1, path2, ..., pathN`	VARCHAR	Yes	One or more key paths identifying values to extract from `str`. Do not include a leading `$`. Path arguments can be string literals or column references.

Usage notes

The path syntax does not follow standard JSONPath notation — omit the leading $ that standard JSONPath requires.
Path arguments can be string literals ('myKey') or column references that resolve to VARCHAR at runtime.
If a path is not found in the input JSON, the corresponding output column is NULL.
If str is NULL, behavior depends on the VVR version. See NULL value handling behavior change.

Examples

Sample data
Table T1. T1
d (VARCHAR)
s (VARCHAR)
{"qwe":"asd","qwe2":"asd2","qwe3":"asd3"}
qwe3
{"qwe":"asd4","qwe2":"asd5","qwe3":"asd3"}
qwe2
Example
The following query extracts two values per row from column d: the value at the literal key 'qwe', and the value at the key named by column s (a column reference). Because there are two path arguments, JSON_TUPLE produces two output rows per input row.
```
SELECT d, v
FROM T1, lateral table(JSON_TUPLE(d, 'qwe', s))
AS T(v);
```

Output

d (VARCHAR)	v (VARCHAR)
`{"qwe":"asd","qwe2":"asd2","qwe3":"asd3"}`	asd
`{"qwe":"asd","qwe2":"asd2","qwe3":"asd3"}`	asd3
`{"qwe":"asd4","qwe2":"asd5","qwe3":"asd3"}`	asd4
`{"qwe":"asd4","qwe2":"asd5","qwe3":"asd3"}`	asd5

NULL value handling: Behavior change

The behavior of JSON_TUPLE when str is NULL changed between VVR versions. If your job was developed before VVR 11.0 and relies on the legacy behavior, review the behavior comparison below.

Behavior comparison

Assume the following query, where the str argument is NULL:

SELECT * FROM (VALUES (1), (2)) AS v(x)
, LATERAL TABLE(
  json_tuple(CAST(NULL AS STRING), 'a', 'b')
) AS T;
-- Each input row returns two rows with NULL. Total: 4 rows — (1,null), (1,null), (2,null), (2,null)

VVR version	Result	Content
Before VVR 11.0 (legacy behavior)	Four rows total (two per input row, one per path argument)	Each row contains one NULL column
VVR 11.0+ (new behavior)	Zero rows (empty set)	None

d (VARCHAR)	s (VARCHAR)
`{"qwe":"asd","qwe2":"asd2","qwe3":"asd3"}`	qwe3
`{"qwe":"asd4","qwe2":"asd5","qwe3":"asd3"}`	qwe2