Question 110846 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How do you unpivot GA4 (Google Analytics 4) event_params (JSON) in SQL Server?

We are exporting GA4 (Google Analytics 4) data from Big Query via a built-in Microsoft connector. Everything is coming through, but the event_params is in a very unusual JSON format. I have unpivoted numerous other arrays from different sources with no issue using the OPENJSON function and CROSS APPLY but this one is beating me so far.

I have searched around the internet and so far I have only found documentation on how to do it in Big Query, which doesn’t work for me with the built in connectors.

My goal is to pivot each of the params and values with non-null values into their own columns, such as below, although there are about 10 custom columns I will have in the end.

event_date event_timestamp page_location ga_session number etc
First row
Second row

Below is a sample of my data

event_date event_timestamp event_params
20221022 1666490454564810 {"v":[{"v":{"f":[{"v":"page_location"},{"v":{"f":[{"v":"https://welcome.mypage.com/"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_number"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_title"},{"v":{"f":[{"v":"Welcome Hub"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"engaged_session_event"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_id"},{"v":{"f":[{"v":null},{"v":"1666490454"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ignore_referrer"},{"v":{"f":[{"v":"true"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_referrer"},{"v":{"f":[{"v":"https://login.mypage.com/callback?code=rgE7OZ3rLlJb8NcqfzOF370PhKojttNbKOddEKxg3p2aL&state=RDlGdC1vQWRlR0VHTHQ3M1p3TlVvS0hnVTVSbXA4UVM0NGtNR2ljbkl1Vg%3D%3D"},{"v":null},{"v":null},{"v":null}]}}]}}]}
20221022 1666490454564810 {"v":[{"v":{"f":[{"v":"engaged_session_event"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_referrer"},{"v":{"f":[{"v":"https://login.mypage.com/callback?code=rgE7OZ3rLlJb8NcqfzOF370PhKojttNbKOddEKxg3p2aL&state=RDlGdC1vQWRlR0VHTHQ3M1p3TlVvS0hnVTVSbXA4UVM0NGtNR2ljbkl1Vg%3D%3D"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ignore_referrer"},{"v":{"f":[{"v":"true"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_title"},{"v":{"f":[{"v":"Welcome Hub"},{"v":null},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_number"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"ga_session_id"},{"v":{"f":[{"v":null},{"v":"1666490454"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"session_engaged"},{"v":{"f":[{"v":null},{"v":"1"},{"v":null},{"v":null}]}}]}},{"v":{"f":[{"v":"page_location"},{"v":{"f":[{"v":"https://welcome.mypage.com/"},{"v":null},{"v":null},{"v":null}]}}]}}]}
Tags:

2

Answers


  1. 0

    i have the same problem than you for the GA4 data.

    After searching, i found that you can select the column and on the transformation tab clic on analyse JSON. After it’s possible to expand the column.

    But when expanding the column, it’s not helping that much to have header column with event… :/

    Login or Signup to reply.
  2. 0

    My solution (which I don’t like) is to use the query option of the Big Query connector and bring every event as a json into my sql db… then parse the data either using JSON_QUERY or maybe ADF data flow.

    At least this way it keeps all the names correctly… but there’s more manual work with the jsons…

    SELECT TO_JSON_STRING(t) as value from   
   `BigQueryTable.events_*` as t
WHERE
   _TABLE_SUFFIX BETWEEN '20221127' AND '20221128'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search