如何在SQL中遍历JSON数组以选择特定索引处的值 [英] How can I loop through a json array in sql to select a value at a specific index
本文介绍了如何在SQL中遍历JSON数组以选择特定索引处的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个存储过程,用于查询数据库并将结果存储在Json变量中.现在,我想按索引循环遍历Json数组以获得特定值.可以告诉我如何实现这一目标.以下是我的查询
I have a stored procedure that query the database and store the result in a Json variable. Now I want to loop through the Json array by index to get a specific value. Can some tell me how to achieve this. Below is my query
DECLARE @json NVARCHAR(Max)
DECLARE @name VARCHAR(50) = 'Name'
SET @json = (select name from getalldataView where
SOUNDEX(name) LIKE SOUNDEX(@name) FOR JSON PATH, ROOT('Names'))
DECLARE @i int = 0
WHILE @i < lengthOFArray
BEGIN
SET @i = @i + 1;
SELECT value
FROM OPENJSON(@json, '$.Names[',@i,']');
END
推荐答案
源样本数据
{ "type": "MultiPolygon",
"coordinates": [
[
[[40, 40], [20, 45], [45, 30], [40, 40]]
],
[
[[20, 35], [10, 30], [10, 10], [30, 5], [45, 20], [20, 35]],
[[30, 20], [20, 15], [20, 25], [30, 20]]
]
]
}
SQL代码
SELECT polygons.[key] as polygon, lines.[key] as line, x, y
FROM OPENJSON(@multipolygon, '$.coordinates') as polygons
CROSS APPLY OPENJSON(polygons.value) as lines
CROSS APPLY OPENJSON(lines.value)
WITH (x float '$[0]', y float '$[1]')
这篇关于如何在SQL中遍历JSON数组以选择特定索引处的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文