如何在BigQuery中模拟PostgreSQL的数组直接比较功能？

PostgreSQL的数组比较逻辑是：

按元素索引顺序逐个对比，第一个不相等的元素的比较结果即为整个数组的比较结果；若所有对应位置元素都相等，则长度更短的数组更小。

要在BigQuery中模拟这一行为，可以通过自定义标量函数（UDF）实现，以下是针对数值类型数组的示例：

实现自定义比较函数 #

CREATE OR REPLACE FUNCTION `your-project.your-dataset.array_less_than`(arr1 ARRAY<INT64>, arr2 ARRAY<INT64>)
RETURNS BOOL
LANGUAGE js AS r"""
  const minLen = Math.min(arr1.length, arr2.length);
  for (let i = 0; i < minLen; i++) {
    if (arr1[i] < arr2[i]) return true;
    if (arr1[i] > arr2[i]) return false;
  }
  // 所有对应元素相等，比较长度
  return arr1.length < arr2.length;
""";

使用示例 #

-- 模拟PostgreSQL的 ARRAY[1,2] < ARRAY[1,3]
SELECT `your-project.your-dataset.array_less_than`([1,2], [1,3]);
-- 返回 true

-- 测试其他场景
SELECT 
  `your-project.your-dataset.array_less_than`([1,2,3], [1,2]) AS case1, -- false（元素全相等，长数组更大）
  `your-project.your-dataset.array_less_than`([2], [1,3]) AS case2, -- false（第一个元素2>1）
  `your-project.your-dataset.array_less_than`([1,1], [1,1,1]) AS case3; -- true（元素全相等，短数组更小）

扩展说明 #

• 如果需要支持其他数据类型（如字符串数组），只需修改UDF中的数组类型声明（将INT64改为STRING），JavaScript的比较逻辑对字符串同样适用（按Unicode编码顺序对比）。
• 也可以用SQL原生逻辑实现（无需JS），但代码会更冗长，适合对JS UDF有顾虑的场景：

CREATE OR REPLACE FUNCTION `your-project.your-dataset.array_less_than_sql`(arr1 ARRAY<INT64>, arr2 ARRAY<INT64>)
RETURNS BOOL
AS (
  CASE
    -- 先找第一个不相等的元素位置
    WHEN EXISTS (
      SELECT 1
      FROM UNNEST(arr1) elem1 WITH OFFSET pos1
      JOIN UNNEST(arr2) elem2 WITH OFFSET pos2 ON pos1 = pos2
      WHERE elem1 != elem2
      ORDER BY pos1
      LIMIT 1
    ) THEN (
      SELECT elem1 < elem2
      FROM UNNEST(arr1) elem1 WITH OFFSET pos1
      JOIN UNNEST(arr2) elem2 WITH OFFSET pos2 ON pos1 = pos2
      WHERE elem1 != elem2
      ORDER BY pos1
      LIMIT 1
    )
    -- 所有元素相等，比较长度
    ELSE ARRAY_LENGTH(arr1) < ARRAY_LENGTH(arr2)
  END
);

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