Spark SQL中用get_json_object提取JSON最后一个EndTime的问题

问题原因 #

你尝试在get_json_object的JSONPath中直接调用Spark SQL的array_size函数是无效的，因为JSONPath语法不支持嵌套Spark SQL函数，get_json_object仅能识别标准JSONPath表达式，无法解析Spark的函数调用。

另外你提供的JSON字符串存在语法错误：第一个ServiceFqdn键值对后缺少逗号，且标准JSON要求使用双引号而非单引号，这些问题会导致后续解析失败，需要先修正格式。

解决方案 #

方法1：解析为结构化数组（推荐，更直观） #

先通过regexp_replace将单引号替换为双引号转为标准JSON，再用from_json解析为结构体数组，最后通过size函数获取数组长度，定位到最后一个元素的EndTime：

SELECT 
  from_json(
    regexp_replace(dns_flip_details, "'", '"'),
    'flipDetails array<struct<ServiceFqdn:string, StartTime:string, EndTime:string>>'
  ).flipDetails[size(from_json(regexp_replace(dns_flip_details, "'", '"'), 'flipDetails array<struct<ServiceFqdn:string, StartTime:string, EndTime:string>>').flipDetails) - 1].EndTime AS last_end_date
FROM abc

或者分步处理，提高可读性：

WITH formatted_json AS (
  SELECT 
    -- 修正JSON格式：单引号转双引号，确保原JSON语法正确（补全缺失的逗号）
    regexp_replace(dns_flip_details, "'", '"') AS standard_json
  FROM abc
),
parsed_array AS (
  SELECT 
    from_json(
      standard_json,
      'flipDetails array<struct<ServiceFqdn:string, StartTime:string, EndTime:string>>'
    ).flipDetails AS flip_details_list
  FROM formatted_json
)
SELECT 
  flip_details_list[size(flip_details_list) - 1].EndTime AS last_end_date
FROM parsed_array

方法2：拼接JSONPath（适合坚持用get_json_object的场景） #

先获取数组长度，再动态拼接JSONPath字符串传入get_json_object：

SELECT 
  get_json_object(
    regexp_replace(dns_flip_details, "'", '"'),
    concat('$.flipDetails[', size(from_json(regexp_replace(dns_flip_details, "'", '"'), 'flipDetails array<struct<ServiceFqdn:string>>').flipDetails) - 1, '].EndTime')
  ) AS last_end_date
FROM abc

注意事项 #

• 确保原始JSON字符串语法正确：键值对之间的逗号不能缺失，所有字符串必须用双引号包裹（标准JSON要求）。
• 若StartTime和EndTime需要转为日期类型，可以在解析后用to_timestamp函数转换，比如：

to_timestamp(flip_details_list[size(flip_details_list)-1].EndTime, 'yyyy-MM-dd HH:mm:ss.SSSSSSXXX') AS last_end_date

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