BigQuery - Query Name, Key and Value

Question

I am trying to create a query in BigQuery.

My table looks like this. So there are a lot of events of show_in_detail and I want to access the ID (1234) and the name (Blablabla) of all events accumulated over a specific date sorted by the most popular ID in descending order. How do I do this?

+----------------+------------------+---------------------------------+
|   event_name   | event_params.key | event_params.value.string_value | 
+----------------+------------------+---------------------------------+
|show_in_detail  | id               | 1234                            |
|show_in_detail  | name             | Blablabla                       |
+----------------+------------------+---------------------------------+

I tried:

SELECT event_params.key FROM `tablename_and_date` LIMIT 1000

But got the error:

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

Mikhail Berlyant · Accepted Answer · 2020-02-07 05:49:47Z

1

Try below

#standardSQL 
SELECT param.key, param.value.string_value 
FROM `tablename_and_date`,
UNNEST(event_params) param
LIMIT 1000

answered Feb 7, 2020 at 5:49

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Casper Lindberg Over a year ago

Thanks Mikhail! It almost works so I mark your answer as correct. I still get results that are not key or value though and they aren't sorted. So what I'm thinking of (which is not working) is to add like: WHERE param.key = "id" OR param.key = "name" SORT(descending) And COUNT(nr of times a specific ID appears and show the result)

Mikhail Berlyant Over a year ago

the current question is answered as it was. this would be your next question - feel free to post with all new details and we will answer :o)

Collectives™ on Stack Overflow

BigQuery - Query Name, Key and Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related