How to query json data?

Hi Guy,

How to query json data ?

i’ve insert GA(google analytic) data on aws athena and then i need to query data to do a ETL and visualization.

i found error in quicksight can’t read array data types.
i have to use data in column event_param.

GA data source

Error in Quicksight

Thanks for your help.

Hi @Siriphon - Can you use json_extract to read JSON data. Please see the link - Extracting data from JSON - Amazon Athena for reference.

Regards - Sanjeeb

1 Like

Hi @Siriphon ,

The blog provides an example and details on querying json data , unnesting it. You can use the same techniques for your requirements : Analyze and visualize nested JSON data with Amazon Athena and Amazon QuickSight | AWS Big Data Blog

1/You could create a view in Athena and then directly connect that view in QuickSight.
2/You could also write custom sql in QuickSight connecting to the Athena table

Kind regards,


Hi Everyone,

i would like to ask more question.

  1. how to query from nested data i want page_location data?
  2. how to split text and get value from sc_camp ?

this is my query

SELECT eventparams.key,
eventparams.value.string_value, * FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) limit 1000

please see detail in photo below.

Many thanks,

Hi @Siriphon ,

Please check athena+sql related queries in repost :

To extract values from your string, you can look into regular expressions : Functions in Amazon Athena - Amazon Athena

Kind Regards,


Hi Koushik,

Would you please recommend Amz athena online course , i would like to learn more ?

Many Thanks,

@Siriphon : Workshop Studio

1 Like