下面是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
select 
  json_extract_scalar(second_object, "$.adUnitCode") as adUnitCode,
  json_extract_scalar(second_object, "$.id") as id,
  json_extract_scalar(second_object, "$.name") as name
from `project.dataset.table`, unnest(
  [json_extract_array(regexp_replace(mapping, r"(: )([\w-]+)(,|})", "\\1'\\2'\\3"))[safe_offset(1)]]
) as second_object

如果应用于您问题中的样本数据-输出为

if applied to sample data from your question - output is

这是在regexp_replace函数中使用适当的regexp.我现在包括了所有字母字符和-.您可以根据需要添加更多内容作为替代方案，您可以尝试 regexp_replace(mapping，r(:)([^，}] +)"，"\\ 1'\\ 2'")，如以下示例所示-这样一来，您无需更改代码即可涵盖更多情况

as you can see, the "trick" here is to use proper regexp in regexp_replace function. I've included now any alphabetical chars and - . you can include more as you see needed As an alternative yo can try regexp_replace(mapping, r"(: )([^,}]+)", "\\1'\\2'") as in below example - so you will cover potentially more cases without changes in code

#standardSQL
select 
  json_extract_scalar(second_object, "$.adUnitCode") as adUnitCode,
  json_extract_scalar(second_object, "$.id") as id,
  json_extract_scalar(second_object, "$.name") as name
from `project.dataset.table`, unnest(
  [json_extract_array(regexp_replace(mapping, r"(: )([^,}]+)", "\\1'\\2'"))[safe_offset(1)]]
) as second_object