如何使用SQL Server读取复杂的json数组

Question

这是我的查询：

SELECT
JSON_QUERY(MyStringColumnWithJson, '$.Images') AS images
FROM MyTable

它返回一个带有JSON数据的字段，如下所示：

"{ 
"Images":
[
{"Name":"test1.jpeg","Description":"originalName1.jpeg"}, 
{"Name":"test2.jpeg","Description":"originalName2.jpeg"}, 
{"Name":"test3.jpeg","Description":"originalName3.jpeg"}
]
}"

如何将图像结果逐行读取到临时表结构中？

Answer 1

使用返回数据集的OPENJSON，而不是返回标量值的JSON_VALUE。例如：

DECLARE @JSON nvarchar(MAX) = N'{ 
"Images":
[
{"Name":"test1.jpeg","Description":"originalName1.jpeg"}, 
{"Name":"test2.jpeg","Description":"originalName2.jpeg"}, 
{"Name":"test3.jpeg","Description":"originalName3.jpeg"}
]
}';
SELECT *
FROM OPENJSON(@JSON, '$.Images')
WITH (Name nvarchar(128),
Description nvarchar(128))OJ;

---

SELECT I.[Name],
I.Description
FROM MyTable MT
CROSS APPLY OPENJSON(MT.YourJsonColumn, '$.Images')
WITH (Name nvarchar(128),
Description nvarchar(128)) I;