修复BigQuery中提取字符串列表第二个名称的SQL语句问题

现有如下数据：

需要在Google BigQuery中提取每个列表里的第二个名称。当前使用的SQL语句：

coalesce(split(names,',')[safe_ordinal(2)],split(names,'||')[safe_ordinal(2)])

但ID=1时返回结果为“S.E”，正确结果应为“Chandra Kirana”，需修复该问题。

问题出在ID1的字符串是类JSON数组格式，其中包含带逗号的名称，直接用逗号分割会错误拆分元素。可以针对两种格式分别处理：

方案一：字符串处理兼容格式 #

SELECT
  ID,
  Names,
  CASE
    -- 处理带大括号的类JSON格式
    WHEN STARTS_WITH(Names, '{') THEN
      TRIM(SPLIT(REPLACE(REPLACE(Names, '{', ''), '}', ''), '","')[SAFE_ORDINAL(2)], '" ')
    -- 处理||分隔的格式
    WHEN Names LIKE '%||%' THEN
      SPLIT(Names, '||')[SAFE_ORDINAL(2)]
    ELSE NULL
  END AS second_name
FROM
  your_table_name

逻辑说明： #

1. 对带大括号的字符串，先移除首尾的{}，再用","作为分隔符拆分，确保每个带引号的名称被正确分割
2. 用TRIM清理元素首尾的引号和空格，最后取第2个元素
3. 对||分隔的字符串直接拆分取第2个元素

方案二：JSON解析（更稳妥） #

如果类JSON格式的字符串是标准结构，可转为JSON数组解析，避免特殊字符干扰：

SELECT
  ID,
  Names,
  CASE
    WHEN STARTS_WITH(Names, '{') THEN
      -- 将{}替换为[]，转为标准JSON数组后提取第二个元素
      JSON_EXTRACT_STRING_ARRAY(REPLACE(REPLACE(Names, '{', '['), '}', ']'))[SAFE_ORDINAL(2)]
    WHEN Names LIKE '%||%' THEN
      SPLIT(Names, '||')[SAFE_ORDINAL(2)]
    ELSE NULL
  END AS second_name
FROM
  your_table_name

两种方案测试结果：

• ID1返回Chandra Kirana
• ID2返回Asep
• ID3返回Paulus Santoso

内容的提问来源于stack exchange，提问作者Denoma