0

I have a UDF that returns two java objects with double values:

object1 = {1.0,1.5,2.0,2.5}
object2 = {5.0,6.0,7.0,8.0}

I would like to combine/merge them in following way returning a double array:

resultArray = [1.0,5.0,1.5,6.0,2.0,7.0,2.5,8.0]

I have looked in to arrayiterate to return table format and then array_agg to combine the two but I have not been successful with this approach.

Appreciate if I can get some assistance and guidance on what would be the fastest way to get the desired resultArray

Thank you.

ASAFDAR
  • 19
  • 1
  • 7

1 Answers1

0

Try to use ARRAYTABLE, then you join those two tables.

Ramesh Reddy
  • 554
  • 1
  • 3
  • 8
  • I tried ARRAYTABLE, it only gets me first value in array not the entire array in tabular format: `SELECT x.* FROM dataTable, ARRAYTABLE(getObject(dataTable.data,1)) COLUMNS doubleArray double) AS x` the UDF retrieves object arrays this way: `object1: getObject(dataTable.data,1) object2: getObject(dataTable.data,2)` – ASAFDAR Apr 12 '21 at 04:45
  • The following works BUT only for first values in each array, how can I combine multiple values: `SELECT array_agg(x.object1||','||y.object2) FROM dataTable , ARRAYTABLE(getObject(dataTable.data,1)) COLUMNS object1 double ) AS x, ARRAYTABLE(getObject(dataTable.data,2)) COLUMNS object2 double ) AS y` – ASAFDAR Apr 12 '21 at 05:15
  • you can do something like ` ARRAYTABLE(dataTable.object1, COLUMNS col1 double, row_number() over(order by col1) id) x` and create two virtual tables and then join by their id in a query – Ramesh Reddy May 03 '21 at 19:41