Unable to select a column in BigQuery

Question

I am unable to select a column named: event_params.value.string_value

Here's my code:

SELECT
  DISTINCT (event_params.value.string_value)
FROM
  `data-22.events_20200914`

My error:

Cannot access field value on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [2:26]

What error do you get? What is the structure of the data? Sample data and desired results would help. — Gordon Linoff
This is the error I got: Cannot access field value on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [2:26] — Shantanu Jain
. . No one is asking for or wants actual data, just representative values to understand the question. — Gordon Linoff

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-09-15T15:13:33

Presumably you want:

SELECT DISTINCT event_param.value.string_value
FROM `mybits-54f8c.analytics_179636122.events_20200914 a CROSS JOIN
     UNNEST(event_params) event_param;

But this is just a guess.

Unable to select a column in BigQuery

1 Answers