I have a sharded table with one pk column and a text column. The text column holds an object in json format. I want to enable ad hoc business analytics by using drill or presto.
Just experimented with both but i am unable to figure out how to parse the json and access its fields in a query.
For drill i tried convert_from(features,'JSON') and for presto i tried json_parse(features). Both seem to convert column text to JSON as a simple select but i cannot access object fields in the same query.
Performance is important so need to avoid io, open to options requiring development effort or hardware scaling.
I was able to analyze json column in presto by using json_extract_scalar on output of json_parse ex. json_extract_scalar(json_parse(features),'$.r_id'). This returns me a string which i can cast to required data type.