SQL to query OSM data on Google BigQuery

osm_extract.sql

SELECT
  osm_id,
  feature_type,
  osm_timestamp,
  geometry,
  -- Here we are going to extract a couple of attributes from the all_tags array:
  (SELECT value FROM UNNEST(all_tags) WHERE key = 'name' ) AS name,
  (SELECT value FROM UNNEST(all_tags) WHERE key = 'addr:city') AS city
FROM
  bigquery-public-data.geo_openstreetmap.planet_features
WHERE
  ('amenity', 'pub') IN ( -- the Key Value pair that you want to search for.
    SELECT
      (key, value)
    FROM
      UNNEST(all_tags))