How to query json data?

Siriphon · April 28, 2023, 11:18am

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.
Siriphon

Sanjeeb2022 · April 28, 2023, 11:24am

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

Koushik_Muthanna · April 28, 2023, 1:39pm

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,
Koushik

Siriphon · May 4, 2023, 3:25pm

Hi Everyone,

i would like to ask more question.

how to query from nested data i want page_location data?
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.