Hi Divya, From the hopefully forthcoming Drill book: Drill does not support mixed type maps or lists, so if your data is inconsistent in this regard, you might encounter schema type errors when querying JSON files. To fix this issue, Drill has two configuration options which can fix this problem. Setting store.json.read_numbers_as_double to true, will alleviate number formatting errors. This can be done in the configuration files or with the following query:
ALTER SESSION SET `store.json.read_numbers_as_double` = true If the JSON data you are reading did not correctly encode the various data types—such as putting quotation marks around numbers, or capitalizing True/False or something like that, you may encounter additional errors when reading JSON data files. If that is the case, the last resort is activating Drill’s all text mode which causes Drill to interpret all fields as varchars. This can be accomplished with the following query: ALTER SESSION SET `store.json.all_text_mode` = true > On May 31, 2018, at 05:12, Divya Gehlot <[email protected]> wrote: > > Hi , > I have complex file json file and I am able to query as below : > SELECT `t`.`results`.`_id` as `_id`,`t`.`results`.`Session` as > `Session`,`t`.`results`.`Gateway` as `Gateway`,`t`.`results`.`Device` as > `Device`,`t`.`results`.`Client` as `Client`,`t`.`results`.`Category` as > `Category`, > `t`.`results`.`LastUpdate` as `LastUpdate`,`t`.`results`.`Coordinates` as > `Coordinates_x`,`t`.`results`.`Start` as `Start`,`t`.`results`.`End` as > `End` FROM (SELECT FLATTEN(results) as `results` FROM > `tmp`.`default`.`./path/to/file.json`) `t` > > > _id > Session Gateway Device Client Category LastUpdate Coordinates_x Start End > > 5b0fa3bbf9490e0b080bb758 > 5b0fa3c40000001cdf003c53 02422d207f0b 2 0 0 1527751644 > [[36.11,19.31,0],[38.51,9.5,12],[25.64,13.07,24]] 1527751620 1527751644 > 5b0fa3bbf9490e0b080bb75b 5b0fa3c40000001cdf003c55 02422d207f0b 8 0 0 > 1527751644 [[36.11,19.31,0],[33.32,26.47,12],[23.15,15.27,24]] > 1527751620 1527751644 > > > Now my query is how do I flatten the Coordinates values. > I tried below query to get the coordinates but its not giving blank values : > SELECT `t`.`results`.`_id` as `_id`,`t`.`results`.`Session` as > `Session`,`t`.`results`.`Gateway` as `Gateway`,`t`.`results`.`Device` as > `Device`,`t`.`results`.`Client` as `Client`,`t`.`results`.`Category` as > `Category`, > `t`.`results`.`LastUpdate` as > `LastUpdate`,`t`.`results`.`Coordinates[0][1]` as > `Coordinates_x`,`t`.`results`.`Start` as `Start`,`t`.`results`.`End` as > `End` FROM (SELECT FLATTEN(results) as `results` FROM > `tmp`.`default`.`./path/to/file.json` ) `t` > > For ease of understanding I removed some of the columns while reading the > data . > > > Appreciate the help ! > > Thanks, > Divya
