Bigquery UNNEST数组

Question

我想列出item_name，但contentType必须是"POI“

1

select param.item_name from 'dataset', 
UNNEST(event_params) AS param
where event_name  = "select_content" AND param.value.string_value = "POI"

Answer 1

下面是针对BigQuery标准SQL的说明

#standardSQL
SELECT 
  ( SELECT value.string_value
    FROM UNNEST(event_params) param
    WHERE key = 'item_name'
  ) AS item_name
FROM `project.dataset.table`
WHERE (
  SELECT COUNT(1) FROM UNNEST(event_params) param
  WHERE (key, value.string_value) = ('content_type', 'POI') OR key = 'item_name'
  ) = 2