getting NULL value while using get_json_object instead of real value

[#1]

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.

[#2]

@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.

[#3]

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?

[#4]

what is your sample data here, table DDL?

[#5]

I have updated my question with the sample JSON data.

[#6]

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

[#7]

@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?

[#8]

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

[#9]

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