JSON parsing issue in hive

Question

i am getting some issues while querying json data.

my sample data look like ...

{"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":{"GGG":"123.dfer","FFF":"3.0","DDD":"Florida","GOP":"fg45","cdc":"QQQ","ZZZ":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}

{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"BOX":{"FRG":"123.dfer","CXD":"3.0","FAX":"Florida","SXD":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}

i have done follwing

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"}}]}}}') ;

The issue is when i start do select get_json_object(myjson,'$.Rtype.MOD.Version[0].ABC.fashion') where get_json_object(myjson,'$.Rtype.MOD.Version[0].ABC') is not null from src

am getting NULLS for the some fields

count value for this say 2345
without where condition also countvalue 2345. this is the issue

the observasion i have seen is this is due to it is trying to fetch data that is $.Rtype.MOD.Version[0].GAP

Answer 1

hive> load data local inpath '/home/satish/s.json' into table sjson;

Loading data to table hivelearning.sjson
Table hivelearning.sjson stats: [numFiles=1, totalSize=216]

hive> select * from sjson;

{"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"}}]}}}

Time taken: 1.297 seconds, Fetched: 1 row(s)

hive> select get_json_object(data,'$.Rtype.MOD.Version[0].ABC.fashion') from sjson;

OK

fg45

Time taken: 0.084 seconds, Fetched: 1 row(s)