如何在SQL中遍历JSON数组以选择特定索引处的值 [英] How can I loop through a json array in sql to select a value at a specific index

问题描述

我有一个存储过程，用于查询数据库并将结果存储在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屋！