Use the MULTI_KEYVALUE function to extract multiple values from a key-value string - Realtime Compute for Apache Flink

MULTI_KEYVALUE is a table function that extracts multiple values from a key-value formatted string in a single call. It splits the string into key-value pairs using the delimiter specified by split1, then splits each pair into a key and a value using the delimiter specified by split2, and returns the values for the requested keys.

Supported in Realtime Compute for Apache Flink with Ververica Runtime (VVR) 3.0.0 or later.

Syntax

MULTI_KEYVALUE(VARCHAR str, VARCHAR split1, VARCHAR split2, VARCHAR key_name1, VARCHAR key_name2, ...)

Because MULTI_KEYVALUE is a table function, call it with a lateral table join:

SELECT c1, c2
FROM T1, lateral table(MULTI_KEYVALUE(str, split1, split2, key1, key2))
as T(c1, c2);

Parameters

Parameter	Type	Description
`str`	`VARCHAR`	The string to parse.
`split1`	`VARCHAR`	The key-value pair delimiter. If `NULL`, spaces are used. If the value is longer than one character, each character is treated as a valid delimiter independently.
`split2`	`VARCHAR`	The key-value delimiter. If `NULL`, spaces are used. If the value is longer than one character, each character is treated as a valid delimiter independently.
`key_name1, key_name2, ...`	`VARCHAR`	The keys whose values to return. Add as many key arguments as needed.

When to use KEYVALUE instead

Warning

In VVR 8.0.7 and earlier, use the KEYVALUE scalar function instead of MULTI_KEYVALUE in either of these situations:

All values for split1, split2, and every key_name argument are constant strings.
Within a namespace running a specific engine version, MULTI_KEYVALUE is called multiple times with different split1 and split2 delimiters — whether in the same deployment or across multiple deployments. When KEYVALUE parses different key names from the same field in repeated calls, Flink reuses intermediate parsing results and applies a binary parsing mechanism, which improves overall processing efficiency. Because KEYVALUE is a scalar function, call it once per key.

Example

Basic usage

Parse k1=v1;k2=v2 with ; as the pair delimiter and = as the key-value delimiter:

-- Input: str = 'k1=v1;k2=v2', split1 = ';', split2 = '='
SELECT c1, c2
FROM T1, lateral table(MULTI_KEYVALUE(str, split1, split2, 'k1', 'k2'))
as T(c1, c2);
-- Output: c1 = 'v1', c2 = 'v2'

Behavior reference

The following table shows how MULTI_KEYVALUE handles various delimiter combinations and edge cases. All rows use the query shown above against table T1.

Test data (table T1)

str	split1	split2	key1	key2
k1=v1;k2=v2	;	=	k1	k2
NULL	;	=	k1	k2
k1:v1;k2:v2	;	:	k1	k3
k1:v1;k2:v2	;	=	k1	k2
k1:v1;k2:v2	,	:	k1	k2
k1:v1;k2=v2	;	:	k1	k2
k1:v1abck2:v2	abc	:	k1	k2
k1:v1;k2=v2	;	:=	k1	k2
k1:v1 k2:v2	NULL	:	k1	k2
k1 v1;k2 v2	;	NULL	k1	k2

Test statement

SELECT c1, c2
FROM T1, lateral table(MULTI_KEYVALUE(str, split1, split2, key1, key2))
as T(c1, c2);

Results

c1	c2
v1	v2
NULL	NULL
v1	NULL
NULL	NULL
NULL	NULL
v1	NULL
v1	v2
v1	v2
v1	v2
v1	v2

Key observations from the results:

Row 2: str is NULL — all output values are NULL.
Row 3: key k3 does not exist in the string — c2 is NULL.
Row 4: the actual delimiter is : but split2 is = — no match, both outputs are NULL.
Row 5: the actual pair delimiter is ; but split1 is , — no match, both outputs are NULL.
Row 6: split2 = ':' matches k1:v1 but not k2=v2 — c1 = 'v1', c2 = NULL.
Row 7: split1 = 'abc' is longer than one character, so a, b, and c are each treated as independent delimiters — splitting k1:v1abck2:v2 correctly.
Row 8: split2 = ':=' is longer than one character, so both : and = are valid key-value delimiters — matching both k1:v1 and k2=v2.
Rows 9–10: NULL delimiters default to space.