Extract all JSON keys
You can
- extract JSON top-level keys with
map_keys(cast(json_column as map<varchar,json>))
- later "flatten" the key collections using
CROSS JOIN UNNEST
- then you can
SELECT DISTINCT
to get distinct top-level keys.
Example putting this together:
presto> SELECT DISTINCT m.key -> FROM (VALUES JSON '{"a": 2, "b": {"b1": 3, "b2": 5}}', JSON '{"c": 3, "a": 5}') -> example_table(json_column) -> CROSS JOIN UNNEST (map_keys(CAST(json_column AS map<varchar,json>))) AS m(key); key----- a b c(3 rows)