Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

getting NULL value while using get_json_object instead of real value

avatar
Contributor

I am new to hive and playing around a nested JSON file and trying to capture a particular field(ID) from it. So I am trying to use get_json_object function as its only one field which is needed from the JSON file.

The structure of my JSON file is :-

Object.resource.Object.entries.Array.Object.payload.Object.Id
	

I am using the following query to capture the "ID" field from the JSON File :-

>SELECT GET_JSON_OBJECT(Id.json,'$.Object.resource.Object.entries.Array.Object.payload.Object.Id') FROM Id;
OK
NULL
Time taken: 0.341 seconds, Fetched: 1 row(s)
hive>
I also tried :-
>SELECT GET_JSON_OBJECT(Id.json,'$') FROM Id;
OK
NULL
Time taken: 0.886 seconds, Fetched: 1 row(s)
hive>

Tried my best to understand but not able to understand why getting this NULL value? Should I be using JSON SerDe instead?

My JSON data looks like :-

"httpStatus": 10807,
"resource": {
"feed": {
"id": "planning.planning-service.ID",
"name": "ID",
"description": "Publish events on create and updates of ID"
},
"entries": [{
"eventId": "9668383e-ec96-4d6a-b873-2312dd008e7b",
"eventType": "ID",
"publishedDate": "2016-05-31T18:52:29.219Z",
"payload": {
"ID": "e9301a6e-7ccf-4c89-bd05-19c1b9067a61"
},
"_links": {
"self": {
"href": "http://lynsys.access:8080"
},
"source": {
"href": "http://lynsys.access:8080"
}
}
},
{
"eventId": "422a39cf-388d-4bbf-9a58-cd5aa8a17fc0",
"eventType": "ID",
"publishedDate": "2016-05-31T18:52:35.507Z",
"payload": {
"ID": "ecb761d2-fd22-401a-97b2-6e982705cc03"
},
"_links": {
"self": {
"href": "http://lynsys.access:8080"
},
"source": {
"href": "http://lynsys.access:8080"
}
}
},

Need to capture ID from the JSON file.

8 REPLIES 8

avatar
Super Guru

@Vijay Parmar you are getting this error from bash shell, you can not run the query from bash shell.use hive cli to run this query.

avatar
Contributor

Thanks Rajkumar that was silly mistake. Now when I am executing the below query :-

SELECT GET_JSON_OBJECT(Id.json,'$.Object.resource.Object.entries.Array.Object.payload.Object.Id') FROM Id;

OK
NULL
Time taken: 0.341 seconds, Fetched: 1 row(s)
hive>

Why there is Null and no values? Any suggestion please?

avatar
Super Guru

what is your sample data here, table DDL?

avatar
Contributor

I have updated my question with the sample JSON data.

avatar
Super Guru

@Vijay Parmar please try this select GET_JSON_OBJECT(json_table.json,'$.Id') from table;

avatar
Contributor

@Rajkumar Singh I have already tried that and getting NULL as the output. Is there something I am doing wrong or is there other way round for getting the result?

avatar
Super Guru

could you please share your table ddl. how you created the table.

avatar
Contributor

Hi @Rajkumar Singh

Are you to to fix the problem,even i have same requirement , for me getting null values can you share the ddl and select query for this