Hologres:KeyValue function

Limits

• Only Hologres V2.1 and later support the KeyValue function. If the version of your Hologres instance is earlier than V2.1, you can manually upgrade your Hologres instance in the Hologres console or join the Hologres DingTalk group to contact Hologres technical support. For more information about how to manually upgrade your Hologres instance in the Hologres console, see the Manual upgrade (beta) section in "Instance upgrades". For more information about how to obtain technical support, see Obtain online support for Hologres.

• When you use the KeyValue function in an SQL statement, the from <table> field must be specified in the SQL statement.

Description

The KeyValue function splits a string specified by str into key-value pairs based on the delimiter specified by split1, splits the key-value pairs based on the delimiter specified by split2, and then returns the value that corresponds to a key.

• Syntax

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

• Parameters

  Parameter	Type	Description	Required
  str	TEXT	The string that you want to split.	Yes
  split1	TEXT	The delimiter based on which you want to split a string into key-value pairs. If this parameter is not specified, the string is split based on the semicolon (;). If the key-value pairs that are obtained after the string is split based on split1 contain multiple split2 fields, the return value is not defined.	No (If this parameter is specified, split2 must be specified.)
  split2	TEXT	The delimiter based on which you want to split the key-value pairs that are obtained after the string is split based on split1. If this parameter is not specified, the key-value pairs are split based on the colon (:).	No (If this parameter is specified, split1 must be specified.)
  key	TEXT	The key whose value is returned after the string is split based on split1 and split2.	Yes

• Return value

  A value of the TEXT type is returned. The return value varies based on the following rules:

  • If the value of split1 or split2 is null, null is returned.

  • If the value of str or key is null, or no key is matched after the key-value pairs are split, null is returned.

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

Examples

• Example 1: Return values based on a constant or a column.

  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');
  
  -- Return values based on a constant.
  select keyvalue(a, '1') from kvtest;
  
  -- The following result is returned:
  keyvalue
  ----------
  \N
  \N
  2
  4
  
  
  -- Return values based on a column.
  select keyvalue(a, b) from kvtest;
  
  -- The following result is returned:
  keyvalue
  ----------
  \N
  \N
  2
  4

• Example 2: Split strings based on specified delimiters.

  begin;
   create table kvtest(
       a text,
       b text
       );
  commit;
  
  insert into kvtest values('sp=11&&xd=1&&B2C=12&&tf=219', 'key');
  
  -- Return a value based on specified delimiters.
  select keyvalue(a,'&&','=', 'sp') from kvtest ;
  
  -- The following result is returned:
  keyvalue
  ----------
  11