sql - 在 SQL 字段中为每个 JSON 元素选择一行/将 JSON 字段转换为行

Question

我有一个包含很多行的表格，例如:

ID     |     Attributes
1      |     {"Rank":1, "LoadLocation": London, "Driver":Tom}
2      |     {"Rank":2, "LoadLocation": Southampton, "Driver":Dick}
3      |     {"Rank":3, "DischargeLocation": Stratford}

JSON 没有模板 - 它是 ID 行的任意数量属性的垃圾场。

为了在连接中使用，我想将它们放入表中:

ID     |     Attribute Name     |  Attribute Value 
1      |     'Rank'             |   1
1      |     'LoadLocation'     |   'London'
1      |     'Driver'           |   'Tom'
2      |     'Rank'             |    2
2      |     'LoadLocation'     |   'Southampton'
2      |     'Driver'           |   'Dick'
3      |     'Rank'             |    3 
3      |     'DischargeLocation'|   'Stratford'

我可以看出我可能需要使用 OpenJSON，而且为此我可能需要知道显式结构。我不知道结构，甚至不知道每一行都有不同数量的属性。

感谢收到的任何帮助!

Answer 1

如果你有 sql-server-2016 及以上版本，你可以使用 OPENJSON 和 CROSS APPLY

DECLARE @TestData TABLE (ID INT, Attributes VARCHAR(500))
INSERT INTO @TestData VALUES
(1 ,'{"Rank":1, "LoadLocation": "London", "Driver":"Tom"}'),
(2 ,'{"Rank":2, "LoadLocation": "Southampton", "Driver":"Dick"}'),
(3 ,'{"Rank":3, "DischargeLocation": "Stratford"}')


SELECT T.ID, X.[key] AS [Attribute Name], X.value AS [Attribute Value] 
FROM @TestData T
    CROSS APPLY (SELECT * FROM OPENJSON(T.Attributes)) AS X

结果:

ID          Attribute Name       Attribute Value    
----------- -------------------- -------------------
1           Rank                 1
1           LoadLocation         London
1           Driver               Tom
2           Rank                 2
2           LoadLocation         Southampton
2           Driver               Dick
3           Rank                 3
3           DischargeLocation    Stratford