如何在BigQuery中实现有效的JSON对象比较函数？

方案一：用JavaScript UDF实现（推荐，覆盖全基础类型） #

直接用JS原生的JSON值比较逻辑写UDF，完全符合你要的数字转译、字符串Unicode排序、布尔值比较的需求：

CREATE TEMP FUNCTION JSON_COMPARE(a JSON, b JSON, operator STRING)
RETURNS BOOLEAN
LANGUAGE js AS """
  const valA = JSON.parse(a);
  const valB = JSON.parse(b);
  
  switch(operator) {
    case '<': return valA < valB;
    case '>': return valA > valB;
    case '=': return valA == valB;
    case '<=': return valA <= valB;
    case '>=': return valA >= valB;
    case '===': return valA === valB;
    default: return null;
  }
""";

-- 测试你的案例，所有结果均为TRUE
SELECT 
  JSON_COMPARE(JSON '1', JSON '2', '<') AS test1,
  JSON_COMPARE(JSON '9', JSON '10', '<') AS test2,
  JSON_COMPARE(JSON '"a"', JSON '"b"', '<') AS test3,
  JSON_COMPARE(JSON '"D"', JSON '"a"', '<') AS test4,
  JSON_COMPARE(JSON 'false', JSON 'true', '<') AS test5;

这个UDF会自动把JSON字符串解析成JS原生值，然后按传入的操作符执行比较，不用手动处理类型判断，省心高效。

方案二：纯SQL实现（基础类型场景） #

如果不想依赖JS UDF，可以通过判断JSON类型，转成对应SQL类型后再比较，适合只需要处理数字、字符串、布尔的场景：

CREATE TEMP FUNCTION JSON_LESS_THAN(a JSON, b JSON)
RETURNS BOOLEAN AS (
  CASE
    -- 数字类型转FLOAT64比较
    WHEN JSON_TYPE(a) = 'NUMBER' AND JSON_TYPE(b) = 'NUMBER' THEN JSON_VALUE(a)::FLOAT64 < JSON_VALUE(b)::FLOAT64
    -- 字符串类型直接提取后比较（自动解析转义引号）
    WHEN JSON_TYPE(a) = 'STRING' AND JSON_TYPE(b) = 'STRING' THEN JSON_VALUE(a) < JSON_VALUE(b)
    -- 布尔类型转BOOL比较
    WHEN JSON_TYPE(a) = 'BOOLEAN' AND JSON_TYPE(b) = 'BOOLEAN' THEN JSON_VALUE(a)::BOOL < JSON_VALUE(b)::BOOL
    -- 数组、对象等复杂类型可自行扩展递归逻辑
    ELSE NULL
  END
);

-- 测试案例
SELECT 
  JSON_LESS_THAN(JSON '1', JSON '2') AS test1,
  JSON_LESS_THAN(JSON '9', JSON '10') AS test2,
  JSON_LESS_THAN(JSON '"a"', JSON '"b"') AS test3,
  JSON_LESS_THAN(JSON '"D"', JSON '"a"') AS test4,
  JSON_LESS_THAN(JSON 'false', JSON 'true') AS test5;

这个方案更可控，要是需要自定义比较规则可以调整，但处理复杂JSON结构（比如嵌套对象、数组）时，得自己写递归逻辑，代码会繁琐不少。

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