如何循环遍历sql中的json数组以选择特定索引处的值

我有一个存储过程，用于查询数据库并将结果存储在 Json 变量中。现在我想按索引循环遍历 Json 数组以获取特定值。有人可以告诉我如何实现这一目标吗？以下是我的查询

 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

source Here https://www.codeproject.com/Articles/1046120/Friday-the-th-JSON-is-coming-to-SQL-Server

样本数据

{ "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]')