Revision history [back]

To extract JSON objects with dynamic keys in Athena/Presto, you can use the json_extract() function along with the map_keys() function.

For example, if you have a JSON object with dynamic keys as follows:

{
  "user1": {
    "name": "John",
    "age": 30
  },
  "user2": {
    "name": "Jane",
    "age": 25
  }
}

You can extract the keys dynamically using the following query:

SELECT map_keys(json_extract(your_json_column, '$')) AS keys
FROM your_table;

This will return a list of keys in the JSON object:

["user1", "user2"]

You can then use these keys to extract specific values from the JSON object using the json_extract() function again:

SELECT json_extract(your_json_column, '$.user1.name') AS name,
       json_extract(your_json_column, '$.user1.age') AS age
FROM your_table;

This will return the following results:

name | age
-----|----
John | 30