How can I loop through a json array in sql to select a value at a specific index

Question

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

Hope this helps, on SQL Server 2017: stackoverflow.com/questions/59292086/… — rahicks
– rahicks, Commented Dec 11, 2019 at 23:27

M_ Fa · Accepted Answer · 2017-02-11 10:15:02Z

7

source Here

sample data

{ "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 Code

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

edited Feb 11, 2017 at 10:15

answered Feb 11, 2017 at 10:03

M_ Fa

49711 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rahicks · Accepted Answer · 2019-12-11 23:32:23Z

4

Something like this will work as of SQL Server 2017.

        SET @i = 0;

        SET @length = 
        (SELECT COUNT(*) 
         FROM OPENJSON(@json,'$.names'))
        WHILE @i < @length
        BEGIN
            DECLARE @driverName NVARCHAR(100);
            SET @driverName = JSON_VALUE(@json,CONCAT('$.names[',@i,']'))
            -- do stuff here like call stored proc...
            SET @i = @i +1;
        END

If you don't need to call a stored proc, but just need to modify the JSON in some way you may find this useful. https://stackoverflow.com/a/59293083/7446265

answered Dec 11, 2019 at 23:32

rahicks

6138 silver badges21 bronze badges

1 Comment

Dinesh Madhup Over a year ago

The issue on above solution is JSON_VALUE(@json,CONCAT('$.names[',@i,']')). We can't use CONCAT on Path of JSON_VALUE

Devnarayan Nagar · Accepted Answer · 2019-09-12 13:53:12Z

Screenshot of result

I this example have multi layer json data and using cross join getting those in one table result

 declare @jsondata nvarchar(max);
    set @jsondata ='{"id":1,"planTypeId":0,"locationId":0,"auditId":0,"empId":0,"accessID":"string","actionItem":"string","resUserID":0,"createdDate":"2019-09-12T13:22:23.805Z","dueDate":"2019-09-12T13:22:23.805Z","lastModified":"2019-09-12T13:22:23.805Z","completedDate":"2019-09-12T13:22:23.805Z","completedBy":0,"status":0,"desc":"string","relQuestionID":0,"catID":0,"equipmentID":0,"steps":[{"id":1,"trnPlanId":1,"dueDate":"2019-09-12T13:22:23.805Z","type":1,"quizId":0,"taskId":0,"docCourseUrl":"string","users":[{"id":1,"empId":1,"status":true}]},{"id":2,"trnPlanId":2,"dueDate":"2019-09-12T13:22:23.805Z","type":2,"quizId":0,"taskId":0,"docCourseUrl":"string","users":[{"id":2,"empId":2,"status":true},{"id":3,"empId":3,"status":true}]}]}';



    SELECT
        JSON_Value (c.value, '$.id') as Id, 
        JSON_Value (c.value, '$.type') as planTypeId, 
        JSON_Value (p.value, '$.id') as userId, 
        JSON_Value (p.value, '$.empId') as empId

    FROM OPENJSON (@jsondata, '$.steps') as c
    CROSS APPLY OPENJSON (c.value, '$.users') as p


    SELECT
        JSON_Value (c.value, '$.id') as Id, 
        JSON_Value (c.value, '$.type') as planTypeId

    FROM OPENJSON (@jsondata, '$.steps') as c

Evinn · Accepted Answer · 2016-07-28 12:09:56Z

0

when you begin end your query to select openjson , could you add one more column , and the value is your looping index. so you can add another Where validation inside the begin end . OpenJSON read all your json and insert it to new row.

answered Jul 28, 2016 at 12:09

Evinn

1531 silver badge11 bronze badges

Collectives™ on Stack Overflow

How can I loop through a json array in sql to select a value at a specific index

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related