Get JSON object keys as array in Presto/Trino-CodePudding

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

CodePudding user response：

I'm not sure how to work well with JSON, but if we convert the JSON to a MAP, the process is simple using map_values:

WITH data as (SELECT * FROM (VALUES JSON '{"foo": 1, "bar": 2}', JSON '{"foo": 1}') AS t(json_col))

SELECT map_values(CAST(json_col AS MAP(VARCHAR, INTEGER))) json_col
FROM data

Output:

json_col
{2,1}
{1}

CodePudding user response：

You can convert your json (use json_parse if column is of type varchar) into map and then join the 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

Output:

_col0
bar, foo
foo