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