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
Advertisement
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 |