CODEWITHSUNDEEP
sqlprestojsonpathtrino
user3064538
user3064538

Reputation:

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

Upvotes: 5

Views: 9998

Answers (2)

Guru Stron
Guru Stron

Reputation: 142123

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

Upvotes: 10

BeRT2me
BeRT2me

Reputation: 13242

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}

Upvotes: 1

Related Questions