CODEWITHSUNDEEP
databasepresto
TTT
TTT

Reputation: 4434

Presto identify unique values

New to presto, I have a table with a column contains a lot key value pairs. I can extract each row's keys using `map_keys', but I am wondering is there a function to combine and list all unique keys in one row?

get all the keys 
SELECT
    DISTINCT MAP_KEYS(col)
FROM tbl
what it looks like: 
_col0
[key1, key2]
[key2, key3]

desired outputs:

_col0
[key1, key2, key3]

Upvotes: 1

Views: 7003

Answers (1)

David Phillips
David Phillips

Reputation: 10218

SELECT array_agg(DISTINCT k)
FROM tbl
CROSS JOIN UNNEST(map_keys(col)) AS t (k)

Upvotes: 4

Related Questions