CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Lijju Mathew
Lijju Mathew

Reputation: 2091

Struct to JSON in Big query google analytics

I have a query which has an ouput as below attached screen shot. Here is the query 

#standardSQL
select
visitNumber,
visitId,
fullVisitorId,
hits.customDimensions
from table_a 
left join UNNEST(hits) as hits limit 10;

enter image description here

Below is one row and I want the output of customDimension as JSON as below

enter image description here

I tried using TO_JSON_STRING function in big query, it didnt give the output as below. I also tried ARRAY, ARRAY_CONCAT but couldnt get it above format. Appreciate if some one can help.

Upvotes: 0

Views: 2079

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Below is for BigQuery Standard SQL and can be good start for you to tweak for your specific needs 

#standardSQL
SELECT
  visitNumber,
  visitId,
  fullVisitorId,
  (
    SELECT CONCAT('[',STRING_AGG(CONCAT('{"',CAST(index AS STRING), '":', '"', IFNULL(value, ''), '"', '}'), ','), ']') 
    FROM UNNEST(hits.customDimensions)
  ) AS customDimensions
FROM table_a  
LEFT JOIN UNNEST(hits) AS hits 
LIMIT 10

Upvotes: 4

Related Questions