Legacy UDF - Determine largest value in a dictionary/map

Question

I have a JSON map/dictionary where I want to find the largest value in it, but I’m having issues writing a UDF (using Legacy SQL syntax). I couldn’t find many resources or examples of this online, but I did see many for Standard SQL. Could anyone provide help or good online examples of dealing with maps, dictionaries, and user defined functions using Legacy SQL? Thanks!

Example input: {“cat_age”: 14, “dog_age”: 4} (this is a column in my table, let’s call the column name “AgeColumn”)

Output: 14

at least provide example of your input data and expected output! — Mikhail Berlyant, Apr 02 '19 at 17:03
I think I could actually use Standard, but I wanted to understand Legacy syntax better as well. Do you think it’s better to just do Standard? — , Apr 02 '19 at 17:42
obviously Standard is preferable and highly recommended by BigQuery Team, unless you have some solid reasons for Legacy SQL - which looks to me not a case here — Mikhail Berlyant, Apr 02 '19 at 17:44
I see! I will try Standard and see if I can get it to work. Thanks for the discussion on this! — , Apr 02 '19 at 17:44

score 0 · Accepted Answer · answered Apr 02 '19 at 17:52

Below is for BigQuery Standard SQL

#standardSQL
SELECT col, 
  (
    SELECT AS STRUCT CAST(SPLIT(item, ':')[SAFE_OFFSET(1)] AS INT64) age, item 
    FROM UNNEST(SPLIT(REGEXP_REPLACE(col, r'{|}', ''))) item
    ORDER BY CAST(SPLIT(item, ':')[SAFE_OFFSET(1)] AS INT64) DESC
    LIMIT 1
  ).*
FROM `project.dataset.table`

You ca test, play with above using dummy data as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '{“cat_age”: 14, “dog_age”: 4}' col UNION ALL
  SELECT '{“cat_age”: 11, “dog_age”: 16}' 
)
SELECT col, 
  (
    SELECT AS STRUCT CAST(SPLIT(item, ':')[SAFE_OFFSET(1)] AS INT64) age, item 
    FROM UNNEST(SPLIT(REGEXP_REPLACE(col, r'{|}', ''))) item
    ORDER BY CAST(SPLIT(item, ':')[SAFE_OFFSET(1)] AS INT64) DESC
    LIMIT 1
  ).*
FROM `project.dataset.table`

with result

Row     col                                 age     item     
1       {“cat_age”: 14, “dog_age”: 4}       14      “cat_age”: 14    
2       {“cat_age”: 11, “dog_age”: 16}      16      “dog_age”: 16

Thanks @Mikhail! I will mess with this and spend more time understanding it better. — , Apr 02 '19 at 17:55
P.S. no this is not related to UDF as I don't see any reasons for its use here — Mikhail Berlyant, Apr 02 '19 at 18:59

Legacy UDF - Determine largest value in a dictionary/map

1 Answers1