sql - 拆分列数据并过滤它们

Question

使用 SQL 语句，我想按某一列中的某个坐标过滤表。

我想将它们拆分为经纬度，然后过滤每个值。如何在 SQL 中完成。

id     pid      code    class   sclass         name     coordinates
24079   4273753 5122    Roads   residential Apoteksgatan    [
[
[
58.26574,
12.9477909
],
[
58.2643948,
12.9490654
],
[
58.2640484,
12.9494174
],
[
58.2638978,
12.9498716
],
[
58.2637401,
12.9505464
],
[
58.2634622,
12.9518316
]
]
]```

标签： sqlsql-server

Answer 1

如果您是 SQL Server 2016 版或更高版本，则可以使用OPENJSON()

DECLARE @testdata TABLE
    (
        [id] INT
      , [pid] BIGINT
      , [code] INT
      , [class] NVARCHAR(100)
      , [sclass] NVARCHAR(100)
      , [name] NVARCHAR(100)
      , [coordinates] NVARCHAR(MAX)
    );

INSERT INTO @testdata (
                          [id]
                        , [pid]
                        , [code]
                        , [class]
                        , [sclass]
                        , [name]
                        , [coordinates]
                      )
VALUES ( 24079
       , 4273753
       , 5122
       , 'Roads'
       , 'residential'
       , 'Apoteksgatan'
       , '[ [ [ 58.26574, 12.9477909 ], [ 58.2643948, 12.9490654 ], [ 58.2640484, 12.9494174 ], [ 58.2638978, 12.9498716 ], [ 58.2637401, 12.9505464 ], [ 58.2634622, 12.9518316 ] ] ]' );

SELECT      [a].[id]
          , [d].[Key]
          , [d].[Value]
FROM        @testdata [a]
CROSS APPLY OPENJSON([a].[coordinates]) [b]
CROSS APPLY OPENJSON([b].[Value]) [c]
CROSS APPLY OPENJSON([c].[Value]) [d];

这给了你以下结果

id          Key Value
----------- --- -----------
24079       0   58.26574
24079       1   12.9477909
24079       0   58.2643948
24079       1   12.9490654
24079       0   58.2640484
24079       1   12.9494174
24079       0   58.2638978
24079       1   12.9498716
24079       0   58.2637401
24079       1   12.9505464
24079       0   58.2634622
24079       1   12.9518316