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