如何让SQL查询返回嵌套数组格式的GIS坐标JSON结果？

现有环境与问题 #

表结构 #

CREATE TABLE [dbo].[PRC_Tutors]
(
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [LivelloZoom] [int] NULL,
    [PosizioneGIS] [nvarchar](max) NULL,

    CONSTRAINT [PK_PRC_Tutors] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

测试数据 #

INSERT INTO [dbo].[PRC_Tutors] ([ID], [UserID], [LivelloZoom], [PosizioneGIS])
VALUES (1, 1, 18, 'POLYGON((10.932815861897701 45.598233964590946,11.138809514241451 44.993985917715946,12.270401311116451 44.969266679434696,12.152298283772701 45.529569413809696,10.932815861897701 45.598233964590946))')

INSERT INTO [dbo].[PRC_Tutors] ([ID], [UserID], [LivelloZoom], [PosizioneGIS])
VALUES (2, 100, 10, 'POLYGON((12.053217932309531 44.2095244414735,12.261958166684531 44.14085989069225,12.341609045590781 44.280935574286,12.168574377622031 44.42650442194225,12.053217932309531 44.2095244414735))')

当前查询及返回结果 #

当前使用的SQL查询：

SELECT  
    CAST((SELECT 
              'OK' As [status],
              TU.livelloZoom AS livelloZoom,
              (SELECT  REPLACE(REPLACE(N.PosizioneGIS, 'POLYGON((',''), '))', '') AS area
               FROM AA_V_PRC_Tutors N
               WHERE N.ID = TU.ID
               FOR JSON PATH, INCLUDE_NULL_VALUES) AS area
          FROM 
              AA_V_PRC_Tutors TU
          WHERE 
              UserID = @User_id
          FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) AS nvarchar(max))

返回的JSON结果：

{
    "status":"OK",
    "livelloZoom":18,
    "area":[
        {"area":"10.932815861897701 45.598233964590946,
        11.138809514241451 44.993985917715946,
        12.270401311116451 44.969266679434696,
        12.152298283772701 45.529569413809696,
        10.932815861897701 45.598233964590946"
        }
    ]
}

期望的JSON格式 #

需要生成嵌套数组格式的area字段：

{
    "status":"OK",
    "livelloZoom":18,
    "area":[
        [10.932815861897701, 45.598233964590946],
        [11.138809514241451, 44.993985917715946],
        [12.270401311116451, 44.969266679434696],
        [12.152298283772701, 45.529569413809696],
        [10.932815861897701, 45.598233964590946]
    ]
}

要实现这个需求，需要拆解GIS字符串为坐标对，再转换为嵌套数组格式。结合SQL Server的字符串处理和JSON生成能力，可通过以下步骤完成：

1. 适配低版本SQL Server：创建字符串拆分函数（可选） #

如果你的SQL Server版本低于2016，无法使用内置的STRING_SPLIT，需先创建自定义拆分函数：

CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Result TABLE(Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @Index INT
    SET @Index = CHARINDEX(@Delimiter, @String)
    WHILE @Index > 0
    BEGIN
        INSERT INTO @Result(Value) VALUES(SUBSTRING(@String, 1, @Index - 1))
        SET @String = SUBSTRING(@String, @Index + 1, LEN(@String))
        SET @Index = CHARINDEX(@Delimiter, @String)
    END
    INSERT INTO @Result(Value) VALUES(@String)
    RETURN
END

2. 修改查询语句 #

使用以下SQL生成目标JSON结构：

SELECT  
    CAST((SELECT 
              'OK' As [status],
              TU.livelloZoom AS livelloZoom,
              (
                  SELECT 
                      JSON_QUERY('[' + STRING_AGG('[' + REPLACE(s.Value, ' ', ', ') + ']', ', ') + ']') AS [value]
                  FROM dbo.SplitString(REPLACE(REPLACE(N.PosizioneGIS, 'POLYGON((',''), '))', ''), ',') s
                  WHERE N.ID = TU.ID
                  FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
              ) AS area
          FROM 
              AA_V_PRC_Tutors TU
          WHERE 
              UserID = @User_id
          FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) AS nvarchar(max))

逻辑说明 #

• 先通过REPLACE去除GIS字符串的POLYGON((前缀和))后缀，得到纯坐标序列；
• 用拆分函数将坐标序列按逗号拆分为单个坐标对（如10.932815861897701 45.598233964590946）；
• 将每个坐标对的空格替换为逗号，包裹成[x,y]格式的数组字符串；
• 用STRING_AGG将所有单个坐标数组拼接成完整的嵌套数组，再通过JSON_QUERY确保这部分被识别为JSON数组而非字符串；
• 最后通过FOR JSON PATH生成最终结构，WITHOUT_ARRAY_WRAPPER确保返回单个JSON对象。

低版本SQL Server适配（替代STRING_AGG） #

若SQL Server版本低于2017，用STUFF+FOR XML PATH替代STRING_AGG：

-- 替换子查询中的STRING_AGG部分
SELECT 
    JSON_QUERY('[' + STUFF((
        SELECT ', [' + REPLACE(s.Value, ' ', ', ') + ']'
        FROM dbo.SplitString(REPLACE(REPLACE(N.PosizioneGIS, 'POLYGON((',''), '))', ''), ',') s
        WHERE N.ID = TU.ID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ']') AS [value]

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