如何按数值排序JSON字段？将非数值JSON值转为null处理

针对BigQuery中JSON字段按数值排序时处理非数值值的需求，有几种更简洁的实现方式，比你当前用TO_JSON_STRING转后SAFE_CAST的方法更直接：

1. 直接使用SAFE.FLOAT64()函数 #

这是最简便的方式，SAFE前缀的转换函数会在转换失败时返回null，完全符合你想要的TRY_CAST效果：

SELECT 
  your_json_column,
  SAFE.FLOAT64(your_json_column) AS numeric_value
FROM your_table
ORDER BY numeric_value

如果JSON字段的值本身是数值类型（如123、45.67），会正常转换为浮点数；如果是字符串、布尔值、JSON对象等非数值类型，转换失败则返回null，排序时会将这些null统一放在末尾或开头（取决于排序规则）。

2. 精准过滤JSON类型（可选） #

如果不希望将布尔值（true会被SAFE.FLOAT64转为1.0，false转为0.0）或其他类型误判为数值，可以先通过JSON_TYPE()判断类型，仅转换明确的数值类型JSON值：

SELECT 
  your_json_column,
  CASE 
    WHEN JSON_TYPE(your_json_column) = 'number' 
    THEN FLOAT64(your_json_column) 
    ELSE NULL 
  END AS numeric_value
FROM your_table
ORDER BY numeric_value

3. 针对JSON对象内的特定字段 #

如果你的JSON字段是对象结构（如{"score": 89}或{"score": "invalid"}），需要提取内部字段转换的话，可以结合JSON_EXTRACT_SCALAR和SAFE_CAST：

SELECT 
  your_json_column,
  SAFE_CAST(JSON_EXTRACT_SCALAR(your_json_column, '$.score') AS FLOAT64) AS score_value
FROM your_table
ORDER BY score_value

对比你当前的方法，SAFE.FLOAT64()直接操作JSON类型，不需要额外的字符串转换步骤，性能和可读性更优。

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