Convert JSON polygon string to WKT-CodePudding

When I query the delivery_area field, which is a string, I get the output in the following format:

delivery_area
[{"coordinates":[[[0.8123028621006346,30.85630865393481],[0.11085785655090209,32.86672588714604],[1.7985246621952369,32.36947850046717],[2.2195190465495487,31.495235858111492],[0.8123028621006346,30.85630865393481]]],"type":"Polygon"}]

The expected output would be in this format(WKT):

delivery_area
POLYGON((0.8123028621006346 30.85630865393481,0.11085785655090209 32.86672588714604,1.7985246621952369 32.36947850046717,2.2195190465495487 31.495235858111492,0.8123028621006346 30.85630865393481))

I have tried multiple functions from the BQ documentation without success so far.

Any idea?

CodePudding user response：

Solved!

Here is your query slightly modified:

WITH
input AS (
  SELECT '{"coordinates":[[[0.8123028621006346,30.85630865393481],[0.11085785655090209,32.86672588714604],[1.7985246621952369,32.36947850046717],[2.2195190465495487,31.495235858111492],[0.8123028621006346,30.85630865393481]]],"type":"Polygon"}' AS json_string
)

SELECT 
  json_string,
  json_query(json_string,'$.coordinates') AS extract_coordinates,
  json_value(json_string,'$.type') AS extract_type
CONCAT(upper(json_value(json_string,'$.type')),REPLACE(REPLACE(REPLACE(REPLACE(son_query(json_string,'$.coordinates'),'[[[','(('),',',' '),'] [',','),']]]','))')) AS converted
FROM input

Thank you for your help @Xavier :)

CodePudding user response：

Not pretty but would something like this help?

with 
input as (
  select '{"coordinates":[[[0.8123028621006346,30.85630865393481],[0.11085785655090209,32.86672588714604],[1.7985246621952369,32.36947850046717],[2.2195190465495487,31.495235858111492],[0.8123028621006346,30.85630865393481]]],"type":"Polygon"}' as json_string
)

select 
  json_string,
  json_query(json_string,'$.coordinates') as extract_coordinates,
  json_value(json_string,'$.type') as extract_type,
  concat(upper(json_value(json_string,'$.type')),'((',replace(replace(json_query(json_string,'$.coordinates'),'[',''),']',''),'))') converted
from input

If the leading and closing [ / ] are causing issues, they could be removed using right(left(json_string, length(json_string) - 1), length(json_string) - 2) but that doesn't help making it cleaner. Hopefully there is a better way!