Bigquery request data from 2 arrays

Question

I am using the following BigQuery Export data scheme https://support.google.com/analytics/answer/3437719?hl=en&ref_topic=3416089

And want to export hits data with extra 2 fields from custom dimensions (array fields) on plain SQL it would be something like this:

select visitId,visitStartTime,customDimensions.value, hits.SOME_HIT_INFO, hits.customDimensions.value from session_data left join hits left join customDimensions where customDimensions.index=100 left join hits.customDimensions where hits.customDimensions.index=200

How it will look on BigQuery query syntax ?

UPD:

for example I have the folowing data:

{
'visitId' : 1
'visitStartTime' : ...,
....
'hits' : [
  {
    'refferer: 'REF1'
    ....
    'customDimensions' : [
       {'index':200,'value':'HITVALUE200'},
       {'index':202,'value':'HITVALUE202'},
    ]
  }
],
'customDimensions' : [
   {'index':100,'value':'VALUE100'},
   {'index':102,'value':'VALUE102'},
]
}

I want to receive something like this:

+------------------------------------------------------------+
| visitId | hits.refferer | hits_cd_INDEX_201 | cd_INDEX_101 |
+------------------------------------------------------------+
| 1       | REF1          | HITVALUE201       | HITVALUE101  |
+------------------------------------------------------------+

```

not clear what exactly you need! please provide more info. like what is expected result? — Mikhail Berlyant
and what the logic behind this? btw. your expected output does not match with the example you provided! — Mikhail Berlyant
@MikhailBerlyant: I want to extract 2 special values (based in index ) from array fields acording their index. General bigquery syntax does not allow it, thats why I have written this pseudo SQL request. — Oleg
i see - just wanted to double check - hits field is an array - so for the same visitId you will get potentially multiple refferers and respective hitvalues - correct? — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-04-03T15:40:25

Below is for BigQuery Standard SQL

#standardSQL
SELECT visitId,   
  ARRAY(
    SELECT AS STRUCT refferer, 
      (SELECT value FROM UNNEST(hit.customDimensions) dim WHERE index = 200) cd_index_200 
    FROM UNNEST(hits) hit
    ) hits,
  (SELECT value FROM UNNEST(customDimensions) dim WHERE index = 100) cd_index_100
FROM `project.dataset.session_data`

Bigquery request data from 2 arrays

1 Answers