在SQL 2014中解析JSON的简单方法

CREATE TABLE TestTable (ID INT IDENTITY(1,1), JsonData NVARCHAR(MAX));

INSERT INTO TestTable (JsonData)
VALUES ('{"Name":"John","Age":30,"City":"New York"}');

SELECT 
    *
FROM 
    TestTable
CROSS APPLY 
    OPENJSON(JsonData)

Key     Value
-------------------------
Name    John
Age     30
City    New York

SELECT 
    ID,
    JSON_VALUE(JsonData, '$.Name') AS Name,
    JSON_VALUE(JsonData, '$.Age') AS Age,
    JSON_VALUE(JsonData, '$.City') AS City
FROM 
    TestTable;

ID  Name  Age  City
-------------------------
1   John  30   New York