求可处理Null值的BigQuery STRING(json_expr)替代函数

BigQuery没有内置函数直接匹配你的需求，可通过自定义SQL函数实现，以下是满足要求的函数方案：

函数定义 #

CREATE OR REPLACE FUNCTION `你的项目ID.你的数据集ID.XXX`(json_expr JSON)
RETURNS STRING
AS (
  CASE
    WHEN JSON_TYPE(json_expr) = 'STRING' THEN STRING(json_expr)
    WHEN JSON_TYPE(json_expr) = 'NULL' THEN NULL
    ELSE ERROR('预期JSON字符串或null，实际收到 ' || JSON_TYPE(json_expr))
  END
);

验证示例 #

• 解析JSON字符串：

  SELECT `你的项目ID.你的数据集ID.XXX`(JSON '"purple"');
  

  返回SQL字符串 "purple"

• 解析JSON null：

  SELECT `你的项目ID.你的数据集ID.XXX`(JSON 'null');
  

  返回SQL NULL

• 解析布尔类型JSON值：

  SELECT `你的项目ID.你的数据集ID.XXX`(JSON 'true');
  

  返回错误：预期JSON字符串或null，实际收到 BOOLEAN

• 解析数字类型JSON值：

  SELECT `你的项目ID.你的数据集ID.XXX`(JSON '15');
  

  返回错误：预期JSON字符串或null，实际收到 NUMBER

逻辑说明 #

通过JSON_TYPE(json_expr)判断输入JSON的类型：

• 若为STRING类型，调用STRING(json_expr)转换为SQL字符串；
• 若为NULL类型，直接返回SQL NULL；
• 其他类型抛出明确错误，完全匹配你要求的校验逻辑。

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