Custom Functions

Intelligent Data Store provides custom User Defined Functions to simplify the processing of data.

Scalar Functions

get_json_field

get_json_field(input, path)

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.
Argument Description
input JSON String input
path JSON path
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'

Examples

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

get_json_array(input, path)

This function extracts JSON fields from the given JSON string, at the specified JSON path. The function always returns ARRAY<STRING>. If ARRAY elements are needed in a different format, apply CAST when elements are accessed. If the JSON path does not exist, it logs an error and returns null. If this function is used to access a single field, this field is returned as ARRAY.

Argument Description

input

JSON String input

path

JSON path

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]'

Examples

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