如何解决BigQuery中从REPEATED模式JSON数组列提取userId值时的JSON_VALUE函数报错问题

你遇到的错误核心原因很明确：JSON_VALUE函数只接受单个字符串类型的JSON作为第一个参数，但你的列是重复数组类型（ARRAY），直接传入数组肯定会不匹配函数签名。

解决这个问题的关键是先把数组"拆开来"，再处理每个单独的JSON元素，具体步骤如下：

1. 用UNNEST()展开数组 #

UNNEST()函数会把你的数组列中的每个JSON元素拆分成单独的行，这样每个元素就变成了单个字符串，符合JSON_VALUE的参数要求。

基础提取SQL #

SELECT 
  JSON_VALUE(user_json, '$.userId') AS userId
FROM 
  table_name,
  UNNEST(column_name) AS user_json

用JSON_EXTRACT_SCALAR替代（效果一致） #

如果你习惯用JSON_EXTRACT_SCALAR，写法类似：

SELECT 
  JSON_EXTRACT_SCALAR(user_json, '$.userId') AS userId
FROM 
  table_name,
  UNNEST(column_name) AS user_json

2. 保留原表其他字段（避免丢失空数组行） #

如果你的表还有其他需要保留的字段，或者担心空数组的行被过滤掉，可以用LEFT JOIN UNNEST()的写法：

SELECT 
  t.*, -- 保留原表所有字段
  JSON_VALUE(user_json, '$.userId') AS userId
FROM 
  table_name t
LEFT JOIN UNNEST(t.column_name) AS user_json
  ON TRUE

这种写法下，即使某行的数组是空的，原表的字段依然会保留，userId字段会显示为NULL。

验证说明 #

拿你提供的示例行{"role":"SuperAdmin","_id":"abcd","userId":"efgh"}来说，UNNEST会把这个JSON字符串单独拆成一行，然后JSON_VALUE(user_json, '$.userId')就会正确提取出efgh。

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