主页 / coding / 问题 / 79155857
Accepted
bircastri
Asked: 2024-11-04 22:38:20 +0800 CST

查询以使用斜杠字符分割的行中的值

  • 772

我有下表：

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))')

这是我的查询，返回 JSON：

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：

{
    "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]
    ]
}

我怎样才能获取该 JSON？

arrays

2 个回答

  1. Best Answer

    有几种方法可以解决这个问题：

    1. REPLACE()使用和嵌套操作的组合将 WKT 坐标解析为单个元素STRING_SPLIT()，然后将它们重建为 JSON 数组。
    2. 将 WKT 转换为地理元素并使用内置地理方法提取坐标组件并将其重建为 JSON（如 Charlieface 的答案中所示 - 这可能是最好的方法）。
    3. 使用REPLACE()直接将 WKT 转换为 JSON 嵌套数组语法，并将其包含在结果中JSON_QUERY()。

    下面使用第三种技术。限制是原始数据必须精确遵循预期格式 - 没有多余的空格或其他变化。

    SELECT 
    'OK' As [status],
    TU.livelloZoom as livelloZoom,
    JSON_QUERY(C.CoordinatesNestedArray) as area
FROM PRC_Tutors TU
CROSS APPLY (
    -- From: 'POLYGON((X1 Y1,X2 Y2))' (with no extra spaces)
    -- To:   '[[X1,Y1],[X2,Y2]]'
    SELECT
        REPLACE(REPLACE(REPLACE(REPLACE(
            TU.PosizioneGIS,
            'POLYGON((', '[['), '))' ,']]'), ',' ,'],['), ' ', ',')
            AS CoordinatesNestedArray
) C
WHERE UserID = @User_id
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

    示例结果（使用jsonformatter.org格式化）：

    {
  "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
    ]
  ]
}

    参见这个 db<>fiddle。

    • 2

  2. 首先，您不需要AA_V_PRC_Tutors N引用，您可以直接引用外部列。您也不需要转换为，nvarchar(max)因为这是的默认输出FOR JSON。

    不幸的是，SQL Server 尚不支持JSON_AGG，这意味着我们需要对其进行破解STRING_AGG，然后使用 来防止双重转义JSON_QUERY。

    因此，为了获得结果，我们需要将您的数据转换为实际geometry类型（不确定为什么一开始不是这样），然后使用STPointN来获取每个点。因此，我们还需要GENERATE_SERIES和STNumPoints来生成足够的人工行。

    SELECT (
    SELECT
      'OK' As [status],
      TU.livelloZoom as livelloZoom,
      JSON_QUERY((   
          SELECT N'[' + STRING_AGG(
            N'[' + CONVERT(nvarchar(max), v2.point.STX, 3) + N',' + CONVERT(nvarchar(max), v2.point.STY, 3) + N']',
            N','
          ) WITHIN GROUP(ORDER BY g.value) + N']'
          FROM (VALUES( CAST(PosizioneGIS AS geometry) )) AS v1(polygon)
          CROSS APPLY GENERATE_SERIES(1, v1.polygon.STNumPoints()) AS g
          CROSS APPLY (VALUES( v1.polygon.STPointN(g.value) )) AS v2(point)
      )) as area
      FROM PRC_Tutors TU
      WHERE UserID = @User_id
      FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
);

    db<>小提琴

    鉴于您使用的是 SQL Server 2016，您没有STRING_AGG，所以我们需要使用旧的FOR XML PATH('')hack。而您也没有，GENERATE_SERIES所以我们需要使用Itzik Ben-Gan 的交叉连接生成器。

    DECLARE @User_id int = 1;

WITH L0 AS (
    SELECT * FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) v(n)
),
L1 AS (
    SELECT 1 n FROM L0 a CROSS JOIN L0 b
)
SELECT (
    SELECT
      'OK' As [status],
      TU.livelloZoom as livelloZoom,
      JSON_QUERY(N'[' + STUFF((
          SELECT
            N',[' + CONVERT(nvarchar(max), v2.point.STX, 3) + N',' + CONVERT(nvarchar(max), v2.point.STY, 3) + N']'
          FROM (VALUES( CAST(PosizioneGIS AS geometry) )) AS v1(polygon)
          CROSS APPLY (
              SELECT TOP (v1.polygon.STNumPoints())
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS value
              FROM L1
          ) AS g
          CROSS APPLY (VALUES( v1.polygon.STPointN(g.value) )) AS v2(point)
          ORDER BY g.value
          FOR XML PATH('')
      ), 1, 1, N'') + N']') as area
      FROM PRC_Tutors TU
      WHERE UserID = @User_id
      FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
);

    db<>小提琴

    • 1

相关问题