解析JSON在SQL Server

问题描述：

DECLARE @json NVARCHAR(MAX) 
SET @json = 
N'[ 
     { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "age": 25 }, 
     { "id" : 5,"info": { "name": "Jane", "surname": "Smith" }, "dob": "2005-11-04T12:00:00" } 
]' 

SELECT * 
FROM OPENJSON(@json) 
    WITH (id int 'strict $.id', 
     firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname', 
     age int, dateOfBirth datetime2 '$.dob')

当JSON数据有一个数组里面，例如：

DECLARE @json NVARCHAR(MAX) 
SET @json = 
N'[ 
     { "id" : 2,"info": { "name": "John", "surname": "Smith" }, 
     "Phones": ["123","345","678"] // like here 
     } 
]'

有什么办法加入阵列解析后的数据得到的东西是这样的：

Id  First Name  Last Name Phone 
2  John   Smith  123 
2  John   Smith  345 
2  John   Smith  678

答

OUTER APPLY这里是你的朋友。让我们混合使用两种行，以获得更好的效果。

DECLARE @json NVARCHAR(MAX) 
SET @json = N' 
[ 
    { 
    "id": 2, 
    "info": { 
     "name": "John", 
     "surname": "Smith" 
    }, 
    "age": 25, 
    "Phones": [ 
     "123", 
     "345", 
     "678" 
    ] 
    }, 
    { 
    "id": 5, 
    "info": { 
     "name": "Jane", 
     "surname": "Smith" 
    }, 
    "dob": "2005-11-04T12:00:00" 
    } 
]' 

SELECT id, [name], [surname], age, dateOfBirth, number 
FROM (
    SELECT * 
    FROM OPENJSON(@json) 
    WITH (
     id   INT    'strict $.id', 
     [name]  NVARCHAR(50) '$.info.name', 
     [surname] NVARCHAR(50) '$.info.surname', 
     age   INT, 
     dateOfBirth DATETIME2  '$.dob', 
     Phones  NVARCHAR(MAX) AS JSON 
    ) 
) AS people 
OUTER APPLY OPENJSON(Phones) 
WITH (
    number NVARCHAR(50) '$' 
)

结果：

+----+------+---------+------+-----------------------------+--------+ 
| id | name | surname | age |   dateOfBirth   | number | 
+----+------+---------+------+-----------------------------+--------+ 
| 2 | John | Smith | 25 | NULL      | 123 | 
| 2 | John | Smith | 25 | NULL      | 345 | 
| 2 | John | Smith | 25 | NULL      | 678 | 
| 5 | Jane | Smith | NULL | 2005-11-04 12:00:00.0000000 | NULL | 
+----+------+---------+------+-----------------------------+--------+

伟大的解决方案，我被困在这个'Phones NVARCHAR（MAX）AS JSON'，所以实现它像value子句 – TheGameiswar

@TheGameiswar：如果值的数量严格限制（和/或已知）明确地将它们从数组可能会比第二个'OPENJSON'快。但是我没有经验JSON解析的时间，所以现在不需要优化。 :-) –

是的，你是对的，只是看到执行计划，原Json的成本为0，json低于outer apply的成本为20 – TheGameiswar

答

你将不得不做两遍：

提取每个人的数据和电话号码留下数组作为JSON
使用交叉/ OUTER APPLY解析每个人的电话号码数组并加入结果

事情是这样的：

DECLARE @json NVARCHAR(MAX) 
SET @json = 
N'[ 
    { "id" : 2,"info": { "name": "John", "surname": "Smith" }, "Phones": ["123","345","678"] }, 
    { "id" : 3,"info": { "name": "Jane", "surname": "Smith" }, "Phones": ["321","543"] } 
]'; 

WITH CTE AS (
    SELECT id, firstName, lastName, phones 
    FROM OPENJSON(@json) 
    WITH (
     id INT 'strict $.id', 
     firstName NVARCHAR(50) '$.info.name', 
     lastName NVARCHAR(50) '$.info.surname', 
     phones NVARCHAR(MAX) '$.Phones' AS JSON 
    ) 
) 
SELECT c.id, c.firstName, c.lastName, p.value as phone 
FROM CTE c 
    CROSS APPLY OPENJSON(c.phones) p

答

嗯，你总是可以做这样的事情：

SELECT ID, FirstName, LastName, value 
FROM OPENJSON(@json) 
WITH(ID int '$.id', 
    FirstName nvarchar(50) '$.info.name', 
    LastName nvarchar(50) '$.info.surname', 
    Phones nvarchar(max) '$.Phones' AS Json) 
CROSS APPLY OPENJSON(Phones)

希望这有助于。

解析JSON在SQL Server

相关推荐