Get JSON object keys as array in Presto/Trino

I have JSON data like this in one of my columns

{"foo": 1, "bar": 2}
{"foo": 1}

and I would like to run a query that returns the keys as an array

foo,bar
foo

Answer

Convert your JSON into a MAP and then use map_keys():

-- sample data
WITH dataset(js) as (
    VALUES (JSON '{"foo": 1, "bar": 2}'),
        (JSON '{"foo": 1}')
)

-- query
SELECT array_join(map_keys(CAST(js AS MAP(VARCHAR, JSON))),  ', ')
FROM dataset

Use json_parse() if your JSON column is of type VARCHAR

SELECT array_join(map_keys(CAST(json_parse(js) AS MAP(VARCHAR, JSON))),  ', ')
FROM dataset

Output:

_col0
bar, foo
foo