json - 将JSON数据从一个表插入到HIVE中的另一个表

我想根据数据上的键字段将JSON数据从一个表插入到其他表。

我的数据看起来像这样



在此，基于版本，我想将特定JSON行中的字段填充到另一个表中，是“BOX”还是“GAP”或“ABC”

例如:如果版本是“GAP”，则在一个表中填充特定的行，如果版本是“BOX”，则填充到另一表中...我的意思是BOX的所有行...

我如何使用HIVE实现这一目标。请帮忙。

注意:我的JSON数据在一个表中作为类型为string的列

最佳答案

演示

create table src (myjson string);

insert into src values
    ('{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"ABC":{"XYZ":"123.dfer","founder":"3.0","GHT":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}')
   ,('{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"GAP":{"XVY":"123.dfer","FAH":"3.0","GHT":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}')
   ,('{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"BOX":{"VOG":"123.dfer","FAH":"3.0","FAX":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}')
;

create table trg_abc (myjson string);
create table trg_gap (myjson string);
create table trg_box (myjson string);

from src
insert into trg_abc select myjson where get_json_object(myjson,'$.Rtype.MOD.Version[0].ABC') is not null
insert into trg_gap select myjson where get_json_object(myjson,'$.Rtype.MOD.Version[0].GAP') is not null
insert into trg_box select myjson where get_json_object(myjson,'$.Rtype.MOD.Version[0].BOX') is not null
;