Use the KeyValue function to extract a value from a key-value string - Hologres

The KeyValue function extracts a value from a delimited key-value string — useful for parsing log lines, URL query parameters, or event attributes stored as plain text.

Limitations

Hologres V2.1 or later is required. To upgrade an earlier instance, use the manual upgrade option in the Hologres console, or contact Hologres technical support through the DingTalk group. See the Manual upgrade (beta) section in "Instance upgrades" and Obtain online support for Hologres.
SQL statements that call KeyValue must include a FROM <table> clause.

Syntax

keyvalue(text <str>, [text <split1>, text <split2>,] text <key>)

The function splits str into key-value pairs using split1, then splits each pair into a key and value using split2, and returns the value for the specified key.

Parameters

Parameter	Type	Required	Default	Description
`str`	TEXT	Yes	—	The string to parse.
`split1`	TEXT	No	`;` (semicolon)	Delimiter that separates key-value pairs. If specified, `split2` must also be specified. If a pair contains multiple occurrences of `split2`, the return value is undefined.
`split2`	TEXT	No	`:` (colon)	Delimiter that separates a key from its value within each pair. If specified, `split1` must also be specified.
`key`	TEXT	Yes	—	The key whose value to return.

Return value

Returns a TEXT value. Returns null in these cases:

split1 or split2 is null.
str or key is null.
No matching key is found after splitting.

If multiple keys match, the value of the first matched key is returned.

Examples

Default delimiters (semicolon and colon)

This example shows both constant and column-based key lookups using the default delimiters (; between pairs, : between key and value).

BEGIN;
CREATE TABLE kvtest (
    a TEXT,
    b TEXT
);
COMMIT;

INSERT INTO kvtest VALUES ('0:1', 'a'), ('2:3', 'b'), ('1:2', '1'), ('1:4', '1');

-- Look up by a constant key
SELECT keyvalue(a, '1') FROM kvtest;
-- Result:
-- keyvalue
-- ----------
-- \N
-- \N
-- 2
-- 4

-- Look up by a column as the key
SELECT keyvalue(a, b) FROM kvtest;
-- Result:
-- keyvalue
-- ----------
-- \N
-- \N
-- 2
-- 4

Custom delimiters

This example parses a &&-separated, =-keyed string — a common format in URL query parameters and analytics event logs.

BEGIN;
CREATE TABLE kvtest (
    a TEXT,
    b TEXT
);
COMMIT;

INSERT INTO kvtest VALUES ('sp=11&&xd=1&&B2C=12&&tf=219', 'key');

-- split1='&&' separates pairs; split2='=' separates key from value
SELECT keyvalue(a, '&&', '=', 'sp') FROM kvtest;
-- Result:
-- keyvalue
-- ----------
-- 11