如何在BigQuery中从JSON数组的user_id提取固定长度ID

先解析JSON数组提取user_id #

首先需要展开JSON中的members数组，提取出每个元素的user_id字段：

SELECT 
  member.user_id
FROM 
  `your_project.your_dataset.your_table`,
  UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.members')) AS member

方法1：用简单正则提取（覆盖所有前缀场景） #

所有前缀均以-或_结尾，只需将开头到最后一个-/_的内容替换为空，即可得到目标ID。这个正则逻辑简单，无需深入学习正则也能理解：

SELECT 
  REGEXP_REPLACE(member.user_id, r'^.*[-_]', '') AS extracted_id
FROM 
  `your_project.your_dataset.your_table`,
  UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.members')) AS member

• 正则^.*[-_]解释：从字符串开头匹配任意内容，直到最后一个-或_，将这部分替换为空后，剩下的就是需要的后缀ID。

方法2：无需正则（适合完全不想碰正则的情况） #

如果目标ID是标准的5段UUID格式（如示例中的8-4-4-4-12结构），可以通过拆分字符串、截取最后5段再拼接的方式提取：

SELECT 
  ARRAY_TO_STRING(
    ARRAY_SLICE(
      SPLIT(member.user_id, r'[-_]'), 
      ARRAY_LENGTH(SPLIT(member.user_id, r'[-_]')) - 5, 
      ARRAY_LENGTH(SPLIT(member.user_id, r'[-_]'))
    ), 
    '-'
  ) AS extracted_id
FROM 
  `your_project.your_dataset.your_table`,
  UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.members')) AS member

• 逻辑：先把user_id按-和_拆分成数组，取数组的最后5个元素，再用-拼接成完整的ID。

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