Hive query to get data from json records

[#1]

Hive query giving null values when trying to pull the data from json which has @date column.below is the sample data and the query iam using.

{"name":"jai","@date":"2015-06-15"}

{"name":"pri","@date":"2017-08-25"}

CREATE TABLE json_obj( json string)

LOCATION '/user/*************/'

select

get_json_object(json_obj.json,'$.name') as name,

get_json_object(json_obj.json,'$.@date') as date

from json_obj;

ouput:

name date

jai null

pri null

expected output:

name date

jai 2015-06-15

pri 2017-08-25

[#2]

@srini

This is Hive bug reported here HIVE-2927.

Possible Workarounds would be:

Hive >=1.3.+

Then use replace function in hive with get_json_object

Example:

hive> select get_json_object(replace(jsn,'@',''),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

(or)

hive> select get_json_object(replace(jsn,'@date','date'),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

Hive <1.3:

Use regexp_replace function:

hive> select get_json_object(regexp_replace(jsn,'@',''),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

(or)

hive> select get_json_object(regexp_replace(jsn,'@date','date'),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

-->Final query would be:

hive> select get_json_object(jsn,'$.name'),get_json_object(regexp_replace(jsn,'@',''),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
jai     2015-06-15

-

If the answer is helpful to resolve the issue, Login and Click on Accept button below to close this thread.This will help other community users to find answers quickly 🙂