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
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2021-08-07 11:00:00 +0000
Seen: 10 times
Last updated: Feb 10 '23
What is the efficient way to obtain query results using the SKIP clause in Apache AGE?
What is the method and location of data storage in Apache AGE?
How can variables be interpreted as strings?
If values are present in a list of dictionaries, how can they be retrieved?
How can data be aggregated across various factor levels without altering the original levels?
How can I make a call with parameter using Flask (Jinja) and jQuery?
What is the method to retrieve every edge in Apache AGE?
How can we associate the type of a key within an object to that object?
Can a property be renamed in Apache AGE?
What is the process to remove duplicate nested data in ES5.6?