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
