You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Herman Tan <he...@redcubesg.com> on 2020/05/18 02:35:58 UTC
Need help with Drill SQL for a json file
Hi Drill community,
I need help in making a SQL Query to extract from attached JSON file.
How do I create a sql select to get Location/Address/Label and the
Location/DisplayPosition/Latitude and Longitude fields in 3 columns?
I have been trying all different ways but I have been getting NULLs.
Your help will be appreciated.
Regards,
Herman Tan
One record:
-----------------
{
"MetaInfo":{"Timestamp":"2020-05-17T08:18:09.892+0000"},
"View":[{
"_type":"SearchResultsViewType",
"ViewId":0,
"Result":[{
"Relevance":0.8,
"MatchLevel":"street",
"MatchQuality":{
"Country":1.0,
"City":1.0,
"District":1.0,
"Street":[0.85],
"PostalCode":1.0
},
"Location":{
"LocationId":"NT_PSJYsiXVY9dOfsNAh-g6bC",
"LocationType":"point",
"DisplayPosition":{"Latitude":-8.14916,"Longitude":113.71817},
"NavigationPosition":[{"Latitude":-8.14916,"Longitude":113.71817}],
"MapView":{
"TopLeft":{"Latitude":-8.14823,"Longitude":113.71793},
"BottomRight":{"Latitude":-8.14943,"Longitude":113.71926}
},
"Address":{
"Label":"Jalan Perumahan
Pesona Regency, Jember 68111, Indonesia",
"Country":"IDN",
"County":"Jawa Timur",
"City":"Jember",
"District":"Patrang",
"Subdistrict":"Patrang Kel.",
"Street":"Jalan Perumahan
Pesona Regency",
"PostalCode":"68111",
"AdditionalData":[{"value":"Indonesia","key":"CountryName"},{"value":"Jawa
Timur","key":"CountyName"}]}
},
"AdditionalData":[]
}]
}]
}
---------------------
Re: Need help with Drill SQL for a json file
Posted by Herman Tan <he...@redcubesg.com>.
Dear Drill,
My mistake, I missed "Response" in the first field.
Apologise for this careless mistake.
Regards,
Herman
On Mon, May 18, 2020 at 10:35 AM Herman Tan <he...@redcubesg.com> wrote:
> Hi Drill community,
>
> I need help in making a SQL Query to extract from attached JSON file.
>
> How do I create a sql select to get Location/Address/Label and the
> Location/DisplayPosition/Latitude and Longitude fields in 3 columns?
>
> I have been trying all different ways but I have been getting NULLs.
>
> Your help will be appreciated.
>
> Regards,
> Herman Tan
>
> One record:
> -----------------
> {
> "MetaInfo":{"Timestamp":"2020-05-17T08:18:09.892+0000"},
> "View":[{
> "_type":"SearchResultsViewType",
> "ViewId":0,
> "Result":[{
> "Relevance":0.8,
> "MatchLevel":"street",
> "MatchQuality":{
> "Country":1.0,
> "City":1.0,
> "District":1.0,
> "Street":[0.85],
> "PostalCode":1.0
> },
> "Location":{
> "LocationId":"NT_PSJYsiXVY9dOfsNAh-g6bC",
> "LocationType":"point",
>
> "DisplayPosition":{"Latitude":-8.14916,"Longitude":113.71817},
>
> "NavigationPosition":[{"Latitude":-8.14916,"Longitude":113.71817}],
> "MapView":{
>
> "TopLeft":{"Latitude":-8.14823,"Longitude":113.71793},
>
> "BottomRight":{"Latitude":-8.14943,"Longitude":113.71926}
> },
> "Address":{
> "Label":"Jalan Perumahan
> Pesona Regency, Jember 68111, Indonesia",
> "Country":"IDN",
> "County":"Jawa Timur",
> "City":"Jember",
> "District":"Patrang",
> "Subdistrict":"Patrang Kel.",
> "Street":"Jalan Perumahan
> Pesona Regency",
> "PostalCode":"68111",
>
> "AdditionalData":[{"value":"Indonesia","key":"CountryName"},{"value":"Jawa
> Timur","key":"CountyName"}]}
> },
> "AdditionalData":[]
> }]
> }]
> }
> ---------------------
>
>
>