如何在Power BI中拆分JSON列？Hive非规范化JSON解析需求

针对你遇到的这种未规范化的JSON数据，Hive提供了专门的函数来处理，尤其是把mentionedlocations这类数组字段拆分成单独行的需求，我给你分享两种实用的方法：

方法一：用from_json + explode（推荐，Hive 2.3+适用） #

这个方法更直观可靠，先把JSON字符串转换成Hive能识别的数组结构，再展开成多行。

假设你的表名为your_table，mentionedlocations里的每个地点对象包含locationName和locationId（可以根据实际结构调整），执行以下SQL：

-- 第一步：将JSON数组字符串解析为Hive数组结构
CREATE TEMPORARY VIEW parsed_locations AS
SELECT 
  -- 这里替换成你需要保留的其他原始字段，比如id、content等
  id,
  content,
  from_json(
    mentionedlocations,
    -- 定义JSON结构，根据实际字段调整类型和字段名
    'array<struct<locationName:string, locationId:string>>'
  ) AS locations_list
FROM your_table;

-- 第二步：将数组展开为单独的行
SELECT 
  id,
  content,
  loc.locationName,
  loc.locationId
FROM parsed_locations
-- 使用OUTER explode可以保留原表中mentionedlocations为空数组的行
LATERAL VIEW OUTER explode(locations_list) exploded AS loc;

如果你的JSON结构更复杂（比如嵌套多层对象），只需要修改from_json里的schema定义，比如嵌套struct：'array<struct<locationName:string, locationId:string, address:struct<city:string, street:string>>>'。

方法二：正则分割 + explode（兼容低版本Hive） #

如果你的Hive版本不支持from_json，可以用正则处理JSON数组字符串，再拆分解析：

SELECT 
  id,
  content,
  -- 解析地点对象里的具体字段
  get_json_object(formatted_location, '$.locationName') AS locationName,
  get_json_object(formatted_location, '$.locationId') AS locationId
FROM (
  SELECT 
    id,
    content,
    -- 补回被分割掉的括号，还原成完整的JSON对象
    concat('{', location_raw, '}') AS formatted_location
  FROM your_table
  LATERAL VIEW OUTER explode(
    split(
      -- 去掉JSON数组前后的[]
      regexp_replace(mentionedlocations, '^\\[|\\]$', ''),
      -- 按"},{"分割数组元素
      '\\},\\{'
    )
  ) exploded AS location_raw
) tmp;

这个方法依赖正则匹配，所以如果JSON里包含特殊字符（比如字段值里有},）可能会出错，优先推荐第一种方法。

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