How to extract keys in a nested json array object in presto?

@jerad 

You can extract keys from a nested JSON array object in Presto using the json_extract function. Here's an example query that demonstrates how to extract keys from a nested JSON array object:

1

SELECT json_extract(json_parse('{"data": [{"key1": "value1"}, {"key2": "value2"}]}'), '$.data[0]') AS nested_object

This query will extract the nested object at index 0 from the JSON array and return it as a column called nested_object. You can modify the json_extract function's path argument to extract keys from different levels of nesting or different objects within the array.

You can also use UNNEST to extract keys from all objects within the JSON array:

1
2
3
4
5

SELECT nested_object.key
FROM (
  SELECT json_parse('{"data": [{"key1": "value1"}, {"key2": "value2"}]}') AS json_data
) t
CROSS JOIN UNNEST(json_extract(json_data, '$.data')) AS nested_object(key VARCHAR)

This query will extract the keys from all objects within the JSON array and return them as a column called key.