Custom Functions

This function extracts a single JSON field from the given JSON string, at the specified JSON path. The function always returns STRING. Use CAST function on the return of this function, if a different datatype is required. If the JSON path does not exist, it logs an error and returns null. This function cannot be used to retrieve multiple elements or JSON arrays.

ksql> describe function GET_JSON_FIELD;
 
Name        : GET_JSON_FIELD
Author      : IDS
Version     : 1.0.0
Overview    : Extracts a single JSON field from the given JSON string, at the specified JSON path. This function always returns STRING. Use CAST function on the return of this function, if a different datatype is required. If the JSON path does not exist, logs a error, and returns null. This function cannot be used to retrieve multiple elements, or JSON arrays.
Type        : SCALAR
Variations  :
 
    Variation   : GET_JSON_FIELD(input VARCHAR, path VARCHAR)
    Returns     : VARCHAR
    Description : From given JSON string, extract single field at given JSON path. Eg: get_json_field('[1,2,3]','$[0]') => '1', get_json_field('{"key":"val"}','$.key') => 'val'

get_json_field('{"key1":{"subkey1":"val1"}}', '$.key1.subkey1') => 'val1'
 
get_json_field('[1,2,3]', '$[2]') =>'3'

ksql> describe function GET_JSON_ARRAY;
 
Name        : GET_JSON_ARRAY
Author      : IDS
Version     : 1.0.0
Overview    : Extracts JSON fields from the given JSON string, at the specified JSON path. This function always returns ARRAY<STRING>. If ARRAY elements are needed in different format, apply cast when elements are accessed. If the JSON path does not exist, logs a error, and returns null. If this function is used to access a single field, this field is returned as array.
Type        : SCALAR
Variations  :
 
    Variation   : GET_JSON_ARRAY(input VARCHAR, path VARCHAR)
    Returns     : ARRAY<VARCHAR>
    Description : From given JSON string, extract fields at given JSON path. When array indices are referred, this are zero-indexed. And the range is inclusive of the first index and exclusive of second index, if provided. Eg: get_json_field('[1,2,3]','$[0:2]') => '[1,2]'

get_json_array('{"key1":{"subkey1":"val1"}}', '$.key1.subkey1') => ['val1']
 
get_json_array('[1,2,3,4,5]', '$[-3:-1]') => ['3', '4']