Athena: Extracting data from JSON

Suppose you have a table in Athena and its column contain JSON data. How can you extract the individual keys? In the example, the table has column “fixedproperties” which contain JSON data –

athena_column_result

How can you display the data is below format?

column_data_seperated

select
    json_extract(fixedproperties, '$.objectId') as object_id,
    json_extract(fixedproperties, '$.custId') as cust_id,
    json_extract(fixedproperties, '$.score') as score
from json_demo;

For more examples on extracting data from JSON refer to https://docs.aws.amazon.com/athena/latest/ug/extracting-data-from-JSON.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s