如何在HIVE中实现JSON结构扁平化并查询嵌套JSON字段？

问题原因 #

你的查询无法生效是因为表中HEADER字段被定义为STRING类型，而非Hive原生的嵌套STRUCT结构，无法直接通过层级点运算符访问深层嵌套的JSON字段。你之前能查询header.identifier大概率是表结构只定义了第一层STRUCT，内层user字段仍然是JSON字符串类型，无法继续下钻查询。

解决方案 #

方案1：临时查询使用get_json_object函数 #

无需修改表结构，适合临时单次查询场景，语法为get_json_object(json字符串列, '$.字段路径')，示例代码：

SELECT *
FROM TBL a
WHERE get_json_object(a.HEADER, '$.identifier') = '1234'
  AND get_json_object(a.HEADER, '$.user.location') LIKE '%USA%';

方案2：多字段扁平化查询使用LATERAL VIEW + json_tuple #

需要一次性提取多个嵌套字段时，该方案性能比多次调用get_json_object更高，示例代码：

SELECT 
  outer_fields.identifier,
  outer_fields.tweetmessage,
  outer_fields.createddate,
  user_fields.location,
  user_fields.id AS user_id,
  user_fields.name AS user_name,
  user_fields.screenname,
  user_fields.geoenabled
FROM TBL a
-- 解析外层JSON字段
LATERAL VIEW json_tuple(a.HEADER, 'identifier', 'tweetmessage', 'createddate', 'user', 'geolocation') outer_fields 
  AS identifier, tweetmessage, createddate, user, geolocation
-- 解析内层user嵌套JSON
LATERAL VIEW json_tuple(outer_fields.user, 'location', 'id', 'name', 'screenname', 'geoenabled') user_fields 
  AS location, id, name, screenname, geoenabled
WHERE outer_fields.identifier = '1234'
  AND user_fields.location LIKE '%USA%';

方案3：定义嵌套STRUCT结构实现原生点查询 #

适合频繁查询该JSON字段的场景，建表时指定JsonSerDe并定义嵌套STRUCT结构，后续可直接用点运算符访问任意层级字段：

CREATE TABLE IF NOT EXISTS tweet_tbl (
  header STRUCT<
    user: STRUCT<location:STRING, id:BIGINT, name:STRING, screenname:STRING, geoenabled:BOOLEAN>,
    tweetmessage:STRING,
    createddate:STRING,
    identifier:STRING,
    geolocation:STRING
  >
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;

建表导入数据后，你原来的点查询语句可直接正常执行：

SELECT *
FROM tweet_tbl a
WHERE a.header.identifier = '1234'
  AND a.header.user.location LIKE '%USA%';

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