-
There could be a few potential issues causing your current query to not return the expected result. The most likely one is that the ‘product_id’ and ‘site_interaction’ keys may not be present in the same event parameter. Unnesting the event_params columns will create a separate row for every key-value pair, and trying to join them on the same row later using a WHERE condition might result in an empty dataset if the conditions are not met.
Here, you might want to consider utilizing a CASE WHEN in an aggregated function to help in isolating the respective keys. Doing so would mean unnesting the array only once but testing which specific key we’re handling in each situation.
For example:
`
SELECT
MAX(CASE WHEN ep.key = ‘product_id’ THEN ep.value.string_value END) as product_id,
COUNT(1) as share
FROM
‘dataset’,
UNNEST(event_params) as ep
WHERE
event_name = ‘listing’
GROUP BY
product_id
`This will first find the ‘product_id’ for each ‘listing’ event, then count how many times each product_id appears in those events. This seems to align with your required output, but please adjust as needed based on your exact dataset and requirements.
