Hive中如何将json_tuple返回的字符串转为array<struct>类型

方案1：使用from_json函数（Hive 2.3及以上版本推荐） #

from_json是Hive 2.3版本后新增的JSON解析函数，支持直接根据指定Schema将JSON字符串转换为对应复杂类型，同时也能顺便把你查询结果中同样为JSON字符串格式的period/count/day字段转为期望的数组类型：

SELECT 
  b.type AS type,
  from_json(b.period, 'array<string>') AS period,
  from_json(b.count, 'array<string>') AS count,
  from_json(b.day, 'array<string>') AS day,
  -- 直接指定结构体数组的Schema完成转换
  from_json(b.content, 'array<struct<count:string,value:int,unit:string>>') AS content
FROM sample_table a
-- 注意你原SQL中此处误写为a.content，实际表中存储JSON的列是json_column
LATERAL VIEW JSON_TUPLE(a.json_column, 'type', 'period', 'count', 'day', 'content') b 
AS type, period, count, day, content

方案2：低版本Hive兼容方案 #

如果你的Hive版本低于2.3不支持from_json，可以通过拆分数组、解析单条结构、重新组装的方式实现：

WITH base_parse AS (
  SELECT 
    b.type AS type,
    -- 先转其他三个数组字段，低版本可以用split+regexp_replace实现array<string>转换
    split(regexp_replace(b.period, '^\\[|\\]|\\"', ''), ',') AS period,
    split(regexp_replace(b.count, '^\\[|\\]|\\"', ''), ',') AS count,
    split(regexp_replace(b.day, '^\\[|\\]|\\"', ''), ',') AS day,
    -- 拆分content数组为单个JSON对象字符串
    explode(split(regexp_replace(b.content, '^\\[|\\]$', ''), '},\\{')) AS content_item_str
  FROM sample_table a
  LATERAL VIEW JSON_TUPLE(a.json_column, 'type', 'period', 'count', 'day', 'content') b 
  AS type, period, count, day, content
),
content_parse AS (
  SELECT 
    type,period,count,day,
    -- 补全JSON对象括号后解析单个字段
    get_json_object(concat('{', content_item_str, '}'), '$.count') AS content_count,
    cast(get_json_object(concat('{', content_item_str, '}'), '$.value') AS int) AS content_value,
    get_json_object(concat('{', content_item_str, '}'), '$.unit') AS content_unit
  FROM base_parse
)
-- 组装为结构体数组
SELECT 
  type,period,count,day,
  collect_list(named_struct('count', content_count, 'value', content_value, 'unit', content_unit)) AS content
FROM content_parse
-- 注意：如果表中有唯一主键，建议用主键分组，避免字段重复导致的合并错误
GROUP BY type,period,count,day

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