Hive表JSON字段键值与数组元素扁平化实现咨询

首先预设你的源表信息如下，你可以根据实际业务场景替换对应名称：

• 源表名：source_table
• 主键字段名：id
• 存储JSON内容的字段名：json_data

适用Hive 2.3及以上版本的查询语句 #

SELECT
  t.key AS `Key`,
  v.value AS `Value`,
  t.id AS `ID`
FROM (
  -- 拆分JSON顶层键，每行对应一个顶层键、原ID、原JSON字段
  SELECT
    id,
    json_data,
    key
  FROM source_table
  LATERAL VIEW EXPLODE(JSON_OBJECT_KEYS(json_data)) tmp AS key
) t
-- 按当前键取出对应数组，拆分数组得到单个元素值
LATERAL VIEW EXPLODE(
  FROM_JSON(
    GET_JSON_OBJECT(t.json_data, CONCAT('$.', t.key)),
    'array<string>'
  )
) tmp_v AS value
-- 若仅需筛选ID为ABC的记录保留以下条件，全表处理直接删除即可
WHERE t.id = 'ABC';

函数说明 #

• JSON_OBJECT_KEYS()：输入JSON对象，返回所有顶层键组成的字符串数组
• EXPLODE()：将数组拆分为多行，每行对应数组的一个元素
• GET_JSON_OBJECT()：按照JSONPath规则提取JSON中指定路径的内容
• FROM_JSON()：将JSON格式的字符串转为Hive指定类型的数据，此处指定array<string>将数组字符串转为Hive字符串数组

旧版本Hive兼容写法 #

如果你的Hive版本低于2.3不支持FROM_JSON函数，可以使用字符串处理的方式替代数组解析逻辑，查询语句如下：

SELECT
  t.key AS `Key`,
  REGEXP_REPLACE(v.value, '^"|"$', '') AS `Value`,
  t.id AS `ID`
FROM (
  SELECT
    id,
    json_data,
    key
  FROM source_table
  LATERAL VIEW EXPLODE(JSON_OBJECT_KEYS(json_data)) tmp AS key
) t
LATERAL VIEW EXPLODE(
  SPLIT(
    REGEXP_REPLACE(GET_JSON_OBJECT(t.json_data, CONCAT('$.', t.key)), '^\\[|\\]$', ''),
    '","'
  )
) tmp_v AS value
WHERE t.id = 'ABC';

执行上述语句后即可得到你期望的4条扁平化输出结果。

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