简化BigQuery中的JSON排序查询方法探讨

你当前有一个用于按JSON路径的字符串值排序JSON数据的SQL查询：

with tbl as (
  select json '2' j union all
  select json '1' j union all
  select json '"hello"' union all
  select json '"alpha"' union all
  select json '[1,2,3]' union all
  select json '{"x": "zzz"}' union all
  select json '{"x": "abc"}' union all
  select json '{"x": {"z": 2}}'
)
select j, JSON_EXTRACT(j, '$.x') from tbl
order by CASE WHEN JSON_EXTRACT(j, '$.x') IS NULL THEN NULL ELSE TO_JSON_STRING(JSON_EXTRACT(j, '$.x')) END NULLS LAST

存在两个待优化点：

• CASE表达式写法冗余，尝试用IFNULL简化失败（IFNULL不支持JSON类型）；必须过滤SQL null，否则TO_JSON_STRING会将其转为字符串"null"，而你需要将这类null排在末尾。
• 另一个近似可行的查询会将非字符串值{"z": 2}转为null，不符合你希望按其字符串化后的值排序的需求。

优化方案 #

可以用IF函数替代冗余的CASE表达式，写法更简洁，同时完全保留原逻辑：

with tbl as (
  select json '2' j union all
  select json '1' j union all
  select json '"hello"' union all
  select json '"alpha"' union all
  select json '[1,2,3]' union all
  select json '{"x": "zzz"}' union all
  select json '{"x": "abc"}' union all
  select json '{"x": {"z": 2}}'
)
select j, JSON_EXTRACT(j, '$.x') from tbl
order by IF(JSON_EXTRACT(j, '$.x') IS NOT NULL, TO_JSON_STRING(JSON_EXTRACT(j, '$.x')), NULL) NULLS LAST

优化说明 #

1. 简化条件判断：IF函数直接判断JSON_EXTRACT(j, '$.x')是否为SQL NULL，不为NULL时调用TO_JSON_STRING生成排序用的字符串，否则返回NULL，配合NULLS LAST将所有无x键的JSON数据排在末尾，避免了CASE表达式的冗余写法。
2. 保留非字符串值排序能力：继续使用TO_JSON_STRING而非JSON_VALUE，确保所有非NULL的JSON值（包括对象、数组、数字等）都被转为标准JSON字符串参与排序，不会像JSON_VALUE那样将非字符串值转为NULL。

如果你的SQL方言不支持IF函数（比如部分PostgreSQL环境），可以通过子查询减少重复调用JSON_EXTRACT，提升代码可读性：

with tbl as (
  select json '2' j union all
  select json '1' j union all
  select json '"hello"' union all
  select json '"alpha"' union all
  select json '[1,2,3]' union all
  select json '{"x": "zzz"}' union all
  select json '{"x": "abc"}' union all
  select json '{"x": {"z": 2}}'
),
extracted as (
  select j, JSON_EXTRACT(j, '$.x') as x_val
  from tbl
)
select j, x_val from extracted
order by CASE WHEN x_val IS NULL THEN NULL ELSE TO_JSON_STRING(x_val) END NULLS LAST

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