将JSON多边形字符串转换为WKT：BigQuery字段转换求助

问题场景 #

查询BigQuery中字符串类型的delivery_area字段时，返回JSON格式的多边形数据：

需要转换成标准WKT格式：

解决方案 #

方法一：通用型（适配复杂结构） #

如果数据可能包含多个多边形或环，推荐用嵌套UNNEST的方法，兼容性更强：

SELECT
  original_delivery_area,
  CONCAT(
    'POLYGON((',
    STRING_AGG(
      CONCAT(CAST(longitude AS STRING), ' ', CAST(latitude AS STRING)),
      ','
    ),
    '))' AS delivery_area_wkt
FROM
  (SELECT delivery_area AS original_delivery_area FROM `你的项目.你的数据集.你的表`),
  UNNEST(JSON_EXTRACT_ARRAY(original_delivery_area)) AS polygon_obj,
  UNNEST(JSON_EXTRACT_ARRAY(JSON_EXTRACT(polygon_obj, '$.coordinates'))) AS ring,
  UNNEST(ring) AS coord_pair,
  UNNEST(coord_pair) WITH OFFSET AS (val, idx)
PIVOT (
  MAX(val) FOR idx IN (0 AS longitude, 1 AS latitude)
)
GROUP BY original_delivery_area;

步骤说明：

1. UNNEST(JSON_EXTRACT_ARRAY(original_delivery_area))：解开外层多边形数组，获取单个Polygon对象
2. UNNEST(JSON_EXTRACT_ARRAY(JSON_EXTRACT(polygon_obj, '$.coordinates')))：提取多边形的坐标环（支持多环场景）
3. UNNEST(ring)：拆分每个坐标对
4. UNNEST(coord_pair) WITH OFFSET：将经度（索引0）和纬度（索引1）分离
5. STRING_AGG：把所有坐标对拼接成逗号分隔的字符串，最后包裹POLYGON(())完成格式转换

方法二：简洁型（结构固定场景） #

如果数据结构固定（仅单个多边形、单个外环），可以用正则表达式配合JSON查询快速转换：

SELECT
  CONCAT(
    'POLYGON((',
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        JSON_QUERY(delivery_area, '$[0].coordinates[0]'),
        r'^\[|\]$', ''
      ),
      r',(?=[^\[]])', ' '
    ),
    '))' AS delivery_area_wkt
FROM `你的项目.你的数据集.你的表`;

步骤说明：

1. JSON_QUERY(delivery_area, '$[0].coordinates[0]')：直接提取最内层的坐标数组
2. 第一次REGEXP_REPLACE：去掉数组首尾的方括号
3. 第二次REGEXP_REPLACE：把坐标对内部的逗号替换成空格（避免替换坐标对之间的逗号）
4. 拼接POLYGON(())得到最终WKT格式

内容的提问来源于stack exchange，提问作者BoB