Search code examples
sqlprestojsonpathtrino

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
Solution

  • 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