working with arrays in trino / presto (remove, sort)-CodePudding

in trino or presto I want to

take an array remove a pattern type e.g. remove "b", re-order sort the array, then create a hash from the array.

I know how to get length: select json_array_length('["b", "a", "b", "c"]'); but I'm unsure how to do the rest

CodePudding user response：

You can use a variety of functions to operate on arrays of values: filter, array_sort, etc:

SELECT xxhash64(cast(json_format(cast(array_sort(filter(data, v -> v <> 'b')) as json)) as varbinary))
FROM (VALUES array['b', 'a', 'c', 'c']) AS t(data);

Since there's no way to directly compute a hash of an array of values (e.g., via xxhash64, which requires a varbinary value), you have to first convert the filtered/sorted array into a varbinary. This can be easily done by first turning it into JSON which has a textual representation and can easily be cast to varbinary:

xxhash64(
  cast(
    json_format(
      cast(<array value> as json) 
    as varbinary))

CodePudding user response：

with the help from Martin here is what I came up with

select xxhash64(cast(json_format(cast(array_sort(filter(CAST(JSON '["b", "a", "b", "c"]' AS ARRAY(varchar)), v -> v <> 'b')) as json)) as varbinary));