JOIN_ALL_BY_KEY
Combines multiple key value arrays into one by the keys.
Syntax
JOIN_ALL_BY_KEY(fieldNames, inputs1[, inputs2, ..., inputsN])
Arguments
Name
Type
Description
Default Value
fieldNames
string
Optional comma separated field names
""
inputs#
any
Examples
fieldNames
inputs1
inputs2
inputs3
inputs4
inputs5
inputs6
Output
'first,second,third'
array['a', 'b', 'c']
array[1, 2, 3]
array['b', 'd', 'a']
array[2, 4, 1]
array['c', 'b']
array[false, true]
[{key: a
, first: 1, second: 1, third: null}, {key: b
, first: 2, second: 2, third: true}, {key: c
, first: 3, second: null, third: false}, {key: d
, first: null, second: 4, third: null}]
Transformation job example
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT fieldNames, inputs1[], inputs2[], inputs3[], inputs4[], inputs5[], inputs6[],
JOIN_ALL_BY_KEY('first,second,third', inputs1[], inputs2[], inputs3[], inputs4[], inputs5[], inputs6[]) AS Output
FROM default_glue_catalog.upsolver_samples.orders_raw_data
LET fieldNames = 'first,second,third',
inputs1 = array['a', 'b', 'c'],
inputs2 = array[1, 2, 3],
inputs3 = array['b', 'd', 'a'],
inputs4 = array[2, 4, 1],
inputs5 = array['c', 'b'],
inputs6 = array[false, true]
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
fieldNames
inputs1[]
inputs2[]
inputs3[]
inputs4[]
inputs5[]
inputs6[]
Output
'first,second,third'
array['a', 'b', 'c']
array[1, 2, 3]
array['b', 'd', 'a']
array[2, 4, 1]
array['c', 'b']
array[false, true]
[{key: a
, first: 1, second: 1, third: null}, {key: b
, first: 2, second: 2, third: true}, {key: c
, first: 3, second: null, third: false}, {key: d
, first: null, second: 4, third: null}]
Last updated