You may try turn on this option `store.json.all_text_mode`, and then cast into numeric value in the query explicitly.
alter session set `store.json.all_text_mode` = true; On Wed, Apr 19, 2017 at 8:49 AM, Dela Cruz, Vergel <vergel.delac...@ap.jll.com> wrote: > Hi, > > > > I have a case where 2 json files from AQI website have similar format except > that the geo lat/lng have can sometimes have double quotes. > > This is causing an error when I run the query. If I remove the double quotes > from the file then it works so I’m pretty sure it’s the double quotes > > that’s giving the problem. The thing is I can’t do this for all the files > and I prefer not to touch the files anyway. Is there a way to overcome this > problem ? > > > > SELECT > > CAST(`t`.`data`['city']['geo'][0] AS FLOAT) AS `lat` > > FROM `dfs`.`root`.`./Docs/DataLake/MajorCities` AS `t` > > > > 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.
