Google在BigQuery-utils中为BigQuery提供了一些有用的UDF。
不要重新发明轮子
所以，我带来了两个UDF来回答这个问题。

1.GET_VALUE(k字符串，arr any类型)

给定[{‘key’：‘a’，‘Value’：‘aaa’}形式的键和键值Map列表，返回标量类型值。

2.cw_map_parse(m字符串，pd字符串，kvd字符串)

要进行Map转换的字符串。
有了这些，您可以编写如下查询：

SELECT get_value('kids', cw_map_parse(str, ',', ':')) kids,
       get_value('chances', cw_map_parse(str, ',', ':')) chances,
  FROM UNNEST(['cars:0,kids:143,cats:1,lost:0,win:1,chances:0,missed:0']) str;
+------+---------+
| kids | chances |
+------+---------+
|  143 |       0 |
+------+---------+

但由于以下要求，需要对CW_MAP_PARSE实现进行一点定制。
如果我把键称为‘Cars’，我得到的值是‘0’。如果我把这个键叫做‘机会’，我应该得到‘0：0：0：0：0：0：0：0：0：0：0：0：0：0：0：0’
下面是一个带有定制UDF函数的查询。str_to_map()是cw_map_parse()的定制版本。

CREATE TEMP FUNCTION str_to_map(m string, pd string, kvd string)
RETURNS ARRAY<STRUCT<key STRING, value STRING>> AS (
  ARRAY(
    SELECT AS STRUCT kv[SAFE_OFFSET(0)] AS key, kv[SAFE_OFFSET(1)] AS value
      FROM (
        SELECT SPLIT(REGEXP_REPLACE(kv, r'^(.*?)' || kvd, r'\1|'), '|') AS kv 
          FROM UNNEST(SPLIT(m, pd)) AS kv
      )
));

CREATE TEMP FUNCTION get_value(get_key STRING, arr ANY TYPE) AS (
  (SELECT value FROM UNNEST(arr) WHERE key = get_key)
);

SELECT get_value('cars', map) cars,
       get_value('kids', map) kids,
       get_value('chances', map) chances,
       get_value('missed', map) missed,
  FROM UNNEST(['cars:0,kids:143,cats:1,lost:0,win:1,chances:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0,missed:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0']) str,
       UNNEST([STRUCT(str_to_map(str, ',', ':') AS map)]);

+------+------+-------------------------------------+-------------------------------------+
| cars | kids |               chances               |               missed                |
+------+------+-------------------------------------+-------------------------------------+
|    0 |  143 | 0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0 | 0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0 |
+------+------+-------------------------------------+-------------------------------------+

针对该特定情况的另一个超级简单的选择

select 
  json_value(json, '$.cars') cars,
  json_value(json, '$.kids') kids,
  json_value(json, '$.cats') cats,
  json_value(json, '$.lost') lost,
  json_value(json, '$.win') win,
  json_value(json, '$.chances') chances,
  json_value(json, '$.missed') missed
from your_table, 
unnest([format('{%s}', regexp_replace(str, r'([^:,]+):([\d:]*\d)', r'"\1":"\2"'))]) json

带输出