The flatten given you access of the list of locations. You can use the following to put it into two columns:
SELECT DateTime, TaxCount, Location[0] as Latittude, Locations[1] as Longtitude FROM ( SELECT tbl.features[0].properties.`timestamp` as DateTime, tbl.features[0].properties.taxi_count as TaxiCount, FLATTEN(tbl.features[0].geometry.coordinates) as Location FROM `apvergeldelacruz`.`default`.`./Processed/Taxi_Avail_2017_03_13_13_54_09_201703130055` tbl); Here is what I got for your sample data: select locations[0] as latitude, locations[1] as longtitude from (select flatten(tbl.coordinates) locations from dfs.`/tmp/1.json` tbl); +------------+-------------+ | latitude | longtitude | +------------+-------------+ | 103.61408 | 1.25228 | | 103.62203 | 1.29562 | +------------+-------------+ On Fri, Mar 17, 2017 at 7:49 AM, Ted Dunning <ted.dunn...@gmail.com> wrote: > What result did you get? > > > > On Fri, Mar 17, 2017 at 12:02 AM, Dela Cruz, Vergel < > vergel.delac...@ap.jll.com> wrote: > >> Hi, >> >> >> >> I have a json file with a map of lat/long values like below. >> >> >> >> "coordinates":[[103.61408,1.25228],[103.62203,1.29562]…” >> >> >> >> I would like to have two columns for this field. One for Latitude and >> other for Longitude. >> >> Is there a way to split this column ? I have used the flatten function to >> get all the coordinate values. >> >> >> >> Below is the drill query I used. >> >> >> >> SELECT tbl.features[0].properties.`timestamp` as DateTime, >> >> tbl.features[0].properties.taxi_count as TaxiCount, >> >> FLATTEN(tbl.features[0].geometry.coordinates) as Location FROM >> `apvergeldelacruz`.`default`.`./Processed/Taxi_Avail_2017_03_13_13_54_09_201703130055` >> tbl >> >> >> >> Thanks, >> >> Vergel >> >> >> >> >> >> This email is for the use of the intended recipient(s) only. If you have >> received this email in error, please notify the sender immediately and then >> delete it. If you are not the intended recipient, you must not keep, use, >> disclose, copy or distribute this email without the author's prior >> permission. We have taken precautions to minimize the risk of transmitting >> software viruses, but we advise you to carry out your own virus checks on >> any attachment to this message. We cannot accept liability for any loss or >> damage caused by software viruses. The information contained in this >> communication may be confidential and may be subject to the attorney-client >> privilege. If you are the intended recipient and you do not wish to receive >> similar electronic messages from us in the future then please respond to >> the sender to this effect. >>
