在Presto SQL/Scala中用JSON函数提取Sent最大值遇阻

原始数据 #

需求 #

提取每个id对应的最大Sent值，期望结果：

问题原因 #

value列的内容不是标准JSON格式：对象的键（如78kfcX）没有被双引号包裹，导致JSON_PARSE、json_extract等函数无法解析，返回NULL或报错。

解决方案 #

先将非标准字符串转换为标准JSON，再拆分提取Sent值并分组取最大值。以下是适用于BigQuery的SQL代码：

WITH formatted_data AS (
  -- 将非标准键替换为带双引号的标准JSON键
  SELECT 
    id,
    REGEXP_REPLACE(value, r'(\w+)=', r'"$1":') AS valid_json
  FROM your_table
),
unnested_data AS (
  -- 拆分JSON对象中的所有子项，提取Sent值
  SELECT 
    id,
    JSON_EXTRACT_SCALAR(item, '$.Sent') AS sent
  FROM formatted_data,
    UNNEST(JSON_QUERY_ARRAY(valid_json, '$.*')) AS item
)
-- 按id分组，取最大的Sent值
SELECT 
  id,
  MAX(CAST(sent AS INT64)) AS Sent
FROM unnested_data
GROUP BY id
ORDER BY id;

代码说明 #

1. 正则转换：REGEXP_REPLACE(value, r'(\w+)=', r'"$1":') 将所有字母数字组成的键（如78kfcX）替换为带双引号的格式，生成标准JSON字符串。
2. 拆分JSON数组：JSON_QUERY_ARRAY(valid_json, '$.*') 提取JSON对象的所有值，转换为数组；UNNEST 将数组拆分成多行，每个id对应多个Sent值。
3. 分组取最大值：通过GROUP BY id和MAX()函数获取每个id对应的最大Sent值，同时将字符串类型的Sent转换为整数。

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