RJP RJP - 1 year ago 145
JSON Question

Apache Drill to query JSON having whitespace in column name

My dataset is of the following format:

"full_address":"4734 Lebanon Church Rd\nDravosburg, PA 15034",
"Fast Food",
"name":"Mr Hoagie",

"Good For":{
"Noise Level":"average",
"Takes Reservations":false,
"Has TV":false,
"Outdoor Seating":false,
"Waiter Service":false,
"Accepts Credit Cards":true,
"Good for Kids":true,
"Good For Groups":true,
"Price Range":1

I want to query the attributes of the dataset. However many of the attributee names have spaces in it. How do you reference an attribute name with space? Example - I want to find the average 'Price Range' for restaurants in Vegas. I tried referencing it using:

select avg(`t.attributes.Price Range`) from `mongo.274_BI`.`yelp_dataset`t where t.city = 'Las Vegas';

It returns null. There is a problem due to the space in between Price and Range. I queried the 'Parking' field with no issues. How can I solve this problem?

Answer Source

Use below query:

select avg(t.attributes.`Price Range`) from `mongo.274_BI`.`yelp_dataset`t where t.city = 'Las Vegas';

Additionally, you may require to CAST attributes.Price Range as int or double.


select avg(cast(t.attributes.`Price Range` as double)) from `mongo.274_BI`.`yelp_dataset`t where t.city = 'Las Vegas';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download