Count BigQuery event.params based on event.key

I am attempting to count the events filtering on their parameter.

For example, suppose that I have the following. Here’s the real one for the inquisitive minds out there. https://i.imgur.com/rPwmR9i.png

event_date | event_timestamp | event_name | event_params.key | event_params.value.string_value
-----------------------------------------------------------------------------------------------
some_date  | some_timestamp  | some_name  | some_key_1       | some_string_1
                                          | some_key_2       | some_string_2
                                          | some_key_3       | some_string_3
                                          | some_key_4       | some_string_4

event_params.key and event_params.value is an array of values. I need to filter out anything in that array that doesn’t match my key. In addition, I need to count each occurrence of a specific string value. I attempted to do something like the following:

 SELECT DISTINCT event_name,
(SELECT value.string_value FROM UNNEST(event_params)
  WHERE key = 'relationship_lenght') AS Length,
(SELECT value.string_value FROM UNNEST(event_params)
  WHERE key = 'relationship_location') AS Location,
(SELECT value.string_value FROM UNNEST(event_params)
  WHERE key = 'relationship_gender') AS Gender

FROM `stormbreaker-studios.analytics_243434300.events_20200902`

WHERE 
  event_name = 'relationship_created_gender' OR
  event_name = 'relationship_created_location' OR
  event_name = 'relationship_created_interests'

LIMIT 10

It returns null for all columns. What I’m expecting to get would be something along the lines of this, horizontal or vertical, makes no difference.

Expected Output

Male | Female | Trans | Non-Binary | New York | San Antonio | 1-5mo | 5-10mo |
------------------------------------------------------------------------------
10   |   11   |   1   |     3      |     3    |      17     |   1   |    61  |

Each Count per column is derived from the value associated with some_key in the above example. The value is static, from a list that is known. For example, the value for relationship_gender could be Male, Female, etc.. and I’d compare to match that.

Answer

I would generally structure your query like this:

with temp as (
  select * from `project.dataset.table`
  left join unnest(event_params) ep
  where event_name in('relationship_created_gender',
                      'relationship_created_location',
                      'relationship_created_interests')
),
logic as(
  select
    case when key = 'relationship_gender' and value.string_value = 'Male' then 1 else 0 end as gender_male,
    case when key = 'relationship_gender' and value.string_value = 'Female' then 1 else 0 end as gender_female,
    ... etc
    case when key = 'relationship_location' and value._value = 'New York' then 1 else 0 end as location_ny,
    ... etc
  from temp
)
select
  sum(gender_male) as Male,
  sum(gender_female) as Female,
  ...etc
from logic

You might have to make some adjustments based on if the things you want to count are in the string_value or int_value format. If you have a unique event_id, I would count distinct that instead of summing 1s.