We keep running into Arrays in various POCs mostly in the context of nested JSONs. One approach to handling Arrays, especially if they are 1D arrays is to possibly try to create a 1:M relationship. Here is an example of what we did at one of our POCs last year:
So say you have a few data points in a delimited file like so:
array_data|actionid [1,2,3,4,5]|5001 [4,5,6,7,8]|5002
Once you have the data loaded, you have the table like:
dbadmin=> select * from array_test; array_data | actionid ------------+---------- 4,5,6,7,8 | 5002 1,2,3,4,5 | 5001
There may be better ways to do this but we used a hash function to create a key column that we can use to join with.
dbadmin=> alter table array_test add column array_data_hash int default hash(array_data); ALTER TABLE
Now you have the table as such and a ‘reasonable’ join key (as far as POCs are concerned).
dbadmin=> select * from array_test; array_data | actionid | array_data_hash ------------+----------+--------------------- 4,5,6,7,8 | 5002 | 7715900607358066213 1,2,3,4,5 | 5001 | 6079478777414744874
Create the other table containing the exploded/tokenized data of individual array elements:
dbadmin=> create table array_data_exploded(array_data_hash int, array_data_tokenized int); CREATE TABLE dbadmin=> INSERT /*+ DIRECT */ INTO array_data_exploded SELECT array_data_hash, words::INT FROM (SELECT array_data_hash, v_txtindex.StringTokenizerDelim(array_data, ',') over (partition by array_data_hash) from array_test) foo; OUTPUT -------- 10 dbadmin=> select * from array_data_exploded; array_data_hash | array_data_tokenized ---------------------+---------------------- 7715900607358066213 | 4 7715900607358066213 | 5 7715900607358066213 | 6 7715900607358066213 | 7 7715900607358066213 | 8 6079478777414744874 | 1 6079478777414744874 | 2 6079478777414744874 | 3 6079478777414744874 | 4 6079478777414744874 | 5Simple aggregate, sum():
dbadmin=> select actionid, sum(array_data_tokenized) from array_test inner join array_data_exploded on array_test.array_data_hash=array_data_exploded.array_data_hash group by 1; actionid | sum ----------+----- 5002 | 30 5001 | 15
Hope that helps and feel free to improve on this!
TL;DR Load the arrays as Varchar, create a (default) column that had the hashed value of the array, and use this hash as a ‘join key’ to another table that had the individual elements of the array tokenized.
