CODEWITHSUNDEEP
google-bigqueryuser-defined-functions
user_1357
user_1357

Reputation: 7940

BigQuery UDF define constant dictionary and match for a given function argument

What is the best way to define a map: Map(1 -> "One", 2 -> "Two") and define a function which will match to above function? I am thinking of defining a dictionary via Javascript and matching in the function body. An example would be great. Thanks

Upvotes: 1

Views: 422

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

Below example for BigQuery Standard SQL 

#standardSQL
CREATE TEMP FUNCTION MAP(expr ANY TYPE, map ANY TYPE, `default` ANY TYPE ) AS (
  IFNULL((
    SELECT result 
    FROM (SELECT NULL AS search, NULL AS result UNION ALL SELECT * FROM UNNEST(map))
    WHERE search = expr), `default`)
);
WITH `project.dataset.table` AS (
  SELECT 1 id, 4 location_id UNION ALL
  SELECT 2, 2 UNION ALL
  SELECT 3, 5
)
SELECT id, location_id,
  MAP(location_id, 
    [ (1, 'Los Angeles'),
      (2, 'San Francisco'),
      (3, 'New York'),
      (4, 'Seattle')
    ], 'Non US') AS `Location`
FROM `project.dataset.table`

with result 

Row id  location_id Location     
1   1   4           Seattle  
2   2   2           San Francisco    
3   3   5           Non US

Upvotes: 1

Related Questions