javascript - 用于合并值列表/数组的 BigQuery JavaScript UDF

Question

所以我有这些列，从col1到colx都是STRING/TEXT类型:

id   |   col1   |    col2   | col3     | ...
-----|----------|-----------|----------|------
1    |[{"a":1}] | [{"b":2}] | [{"c":3}]| ...
-----|----------|-----------|----------|------
2    | ....

是否有一种简单的方法可以使用 UDF 连接和合并这些值，结果为[{"a":1}, {"b":2}, {"c":3}]？

我考虑过先进行字符串连接和正则表达式替换，但 SQL 会非常冗长，所以我现在正在研究 JS UDF。然而，我不知道如何使用任意数量的列/参数来做到这一点。感谢您的想法!

编辑1

澄清一下，对于 UDF 实现，如果我可以以任意顺序选择任意数量的参数，即

• func(col1, col2) 给我 [{"a":1}, {"b":2}] 和
• func(col1, col2, col3) 给我 [{"a":1}, {"b":2}, {"c": 3}].

Answer 1

Is it possible to make the args selective, say, I can choose combine(col1, col2) or combine(col1, col2, col100)?

func(col1, col2) gives me [{"a":1}, {"b":2}], and
func(col1, col2, col3) gives me [{"a":1}, {"b":2}, {"c": 3}].

以下示例适用于 BigQuery 标准 SQL

#standardSQL
CREATE TEMP FUNCTION combine(s ANY TYPE) AS (
  REGEXP_REPLACE(TO_JSON_STRING(s), r'\\"', '"')
);
WITH `project.dataset.table` AS (
  SELECT 1 id, '{"a":1}' col1, '{"b":2}' col2, '{"c":3}' col3 UNION ALL
  SELECT 2 id, '{"d":4}' col1, '{"e":5}' col2, '{"f":6}' col3 
)
SELECT id, 
  combine([col1, col2]) combined_2columns,
  combine([col1, col2, col3]) combined_3columns
FROM `project.dataset.table` t
-- ORDER BY id  

带输出

Row id  combined_2columns       combined_3columns    
1   1   ["{"a":1}","{"b":2}"]   ["{"a":1}","{"b":2}","{"c":3}"]  
2   2   ["{"d":4}","{"e":5}"]   ["{"d":4}","{"e":5}","{"f":6}"]