类似于@SelVazi的答案，但您也可以使用UDF，并且需要LEFT JOIN展平数组以获得预期结果。

CREATE TEMP FUNCTION keys(json STRING) RETURNS ARRAY<STRING> LANGUAGE js AS """
  return Object.keys(JSON.parse(json));
""";

CREATE TEMP FUNCTION values(json STRING) RETURNS ARRAY<STRING> LANGUAGE js AS """
  return Object.values(JSON.parse(json)).map(e => e.key);
""";

WITH sample_table AS (
  SELECT 'btjap' id, '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' `desc` UNION ALL
  SELECT 'fxlol', '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' UNION ALL
  SELECT 'nretx', '{"val":{}}'
)
SELECT id, code, key
  FROM sample_table
  LEFT JOIN UNNEST(keys(JSON_QUERY(`desc`, '$.val'))) code WITH offset
  LEFT JOIN UNNEST(values(JSON_QUERY(`desc`, '$.val'))) key WITH offset USING (offset);

• • 查询结果 *

这可以通过使用json_extract_keys从json中提取键（代码）来完成，然后我们可以使用REGEXP_EXTRACT访问嵌套的元素

with mytable as (
  select 'btjap' as id,     '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' as descr union all
  select 'fxlol',   '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' union all
  select 'nretx',   '{"val":{}}'
)
select id, code, REGEXP_EXTRACT(descr, FORMAT('"%s":{"key":"([a-z1-9]*)".*', code)) key
from mytable t,
UNNEST(bqutil.fn.json_extract_keys(JSON_QUERY(t.descr, '$.val'))) as code;

如果在您的位置找不到函数json_extract_keys，则可以创建一个等效的UDF：

CREATE TEMP FUNCTION json_extract_keys(json STRING) RETURNS ARRAY<STRING> LANGUAGE js AS """
  try {
    return Object.keys(JSON.parse(json));
  } catch {
    return null;
  }
""";

with mytable as (
  select 'btjap' as id,     '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' as descr union all
  select 'fxlol',   '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' union all
  select 'nretx',   '{"val":{}}'
)
select id, code, REGEXP_EXTRACT(descr, FORMAT('"%s":{"key":"([a-z1-9]*)".*', code)) key
from mytable t,
UNNEST(json_extract_keys(JSON_QUERY(t.descr, '$.val'))) as code;

结果：