Composite Data

VARIANT_FIELD_SELECT

VARIANT_FIELD_SELECT(variant, fieldname)

Selects fieldname from VARIANT_OBJECT or VARIANT_ARRAY variant object. This function can be used when arguments are evaluated dynamically at runtime.

Note

The function is complementary to accessing object elements directly.

output type:the type of the fieldname selected.

Example: select member named “ip” from a variant object:

1
dual() | select(obj:VARIANT_OBJECT(tuple)) | select(VARIANT_FIELD_SELECT(obj, 'ip'))
variant_field_select
0.0.0.0

TUPlE_FIELD_SELECT

TUPLE_FIELD_SELECT(tuple, fieldname)

Selects fieldname from tuple. This function can be used when arguments are evaluated dynamically at runtime.

Note

The function is complementary to accessing tuple elements directly.

output type:the type of the fieldname selected

Example: select member named “ip” from a variant object:

1
dual() | select(tuple) | select(TUPLE_FIELD_SELECT(tuple, 'ip'))
ip
0.0.0.0

ARRAY_AVG

ARRAY_AVG(array)

Computes arithmetic mean of numeric array members.

output type:DOUBLE

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_AVG(array));
array_avg
2.0

ARRAY_COUNT

ARRAY_COUNT(array)

Computes count of not NULL members of array.

output type:LONG

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_COUNT(array));
array_count
5

ARRAY_FIRST

ARRAY_FIRST(array)

Returns the first value in the array.

output type:the type of the array member

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_FIRST(array));
array_first
0

ARRAY_LAST

ARRAY_LAST(array)

Returns the last value in the array.

output type:the type of the array member

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_LAST(array));
array_last
4

ARRAY_MAX

ARRAY_MAX(array)

Returns the max value of numeric array members.

output type:the type of the array member

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_MAX(array));
array_max
4

ARRAY_MIN

ARRAY_MIN(array) Returns min value of numeric array members.

output type:the type of the array member

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_MIN(array));
array_min
0

ARRAY_JOIN

ARRAY_JOIN(array, str)

Returns string with array elements concatenated in order of their appearance and separated by str.

output type:STRING

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_JOIN(array, " + "));
array_join
0 + 1 + 2 + 3 + 4

ARRAY_SORT

ARRAY_SORT(array)

Returns copy of array with members sorted in ascending order.

output type:ARRAY

Example:

1
dual | select(array:[2,1,0,4,3]) | select(ARRAY_SORT(array));
array_sort
[0, 1, 2, 3, 4]

ARRAY_SORT(array, boolean)

Returns sorted array. Sorting order is determined by boolean argument: if true then ascending, otherwise descending.

output type:ARRAY

Example:

1
dual | select(array:[2,1,0,4,3]) | select(ARRAY_SORT(array, false));
array_sort
[4, 3, 2, 1, 0]

ARRAY_SUM

ARRAY_SUM(array)

Computes the sum of numeric array members.

output type:DOUBLE

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_SUM(array));
array_sum
10.0

ARRAY_IDX

ARRAY_IDX(array, expr)

Returns position of the first member in array which is equal to expr. If not found returns NULL.

output type:the type of array member

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_IDX(array,4));
array_idx
4

ARRAY_SELECT

ARRAY_SELECT(array, integer)

Returns element from array at position integer. If not found returns NULL.

output type:the type of array member.

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_SELECT(array,4));
array_4
4

ARRAY_SELECT(array, start_pos, end_pos)

Returns elements of array from start_pos to end_pos inclusive. If not found returns empty array.

output type:ARRAY

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_SELECT(array,1,3));
array_select
[1, 2, 3]

ARRAY_LEN

ARRAY_LEN(array)

Returns the number of elements in array.

output type:INTEGER

Example:

1
dual | select(array:[0,1,2,3,4]) | select(ARRAY_LEN(array));
array_len
5

ARRAY_REMOVE_NULLS

ARRAY_REMOVE_NULLS(array)

Removes elements with NULL values from array.

output type:ARRAY

Example:

1
2
3
dual
| select(array:[0,1,TO_NULL(2),3,4])
| select(array, new_array:ARRAY_REMOVE_NULLS(array));
array new_array
[0, 1, null, 3, 4] [0, 1, 3, 4]

REMOVE_NULLS

REMOVE_NULLS(variant_object)

REMOVE_NULLS(variant_array)

REMOVE_NULLS(array)

Removes elements with NULL values from variant_object, variant_array or array argument.

output type:VARIANT_OBJECT or VARIANT_ARRAY or ARRAY

Example:

1
2
3
dual | select(v_obj:VARIANT_OBJECT({a:1, b:"oho", c:TO_NULL(1)}))
| select(v_obj, REMOVE_NULLS(v_obj))
;
v_obj remove_nulls
{“a”:1,”b”:”oho”,”c”:null} {“a”:1,”b”:”oho”}

ARRAY_AGG

ARRAY_AGG(expr)

Collects expr values into an array.

output type:ARRAY

Example: collect values of DUAL generated field “i” into an array:

1
dual(5) | select(array:ARRAY_AGG(i))
array
[0, 1, 2, 3, 4]

ARRAY_UNIQ

ARRAY_UNIQ(array)

Returns array with unique values from argument array.

output type:ARRAY

Example:

1
dual | select(arr:[1,2,3,4,5,1,1,2]) | select(arr, ARRAY_UNIQ(arr));
arr array_uniq
[1, 2, 3, 4, 5, 1, 1, 2] [1, 2, 3, 4, 5]

UNNEST

UNNEST(array)

UNNEST(variant_object)

Expand an array or variant_object to a set of rows. This is the opposite of the ARRAY_AGG function.

Example:

1
dual | select(array:[0,1,2,3,4]) | select(UNNEST(array));
unnest
0
1
2
3
4

SPREAD

SPREAD(tuple)

Expands fields from the TUPLE argument to the fields of resultset row.

Example:

1
dual(offset:3,count:2) | select(tuple, SPREAD(tuple));
i ip ip6 s t ips ip6s
2 0.0.0.2 0.0.0.2 2ho2 2ho2 [0.0.0.2, 0.0.0.3] [0.0.0.2, 0.0.0.3]
3 0.0.0.3 0.0.0.3 3ho3 3ho3 [0.0.0.3, 0.0.0.4, 0.0.0.5] [0.0.0.3, 0.0.0.4, 0.0.0.5]