CODEWITHSUNDEEP
jsonsql-serverstored-procedures
susmita rai
susmita rai

Reputation: 39

How can I loop through array of json in SQL Server?

I want to loop through data to get taggedEntityName and taggedEntityId:

{
    "data": [
              {
                  "taggedEntityName": "Organization",
                  "taggedEntityId": [
                                      145642,
                                      145625
                   ],
                  "index": 0
             },
            {
                 "taggedEntityName": "Job",
                 "taggedEntityId": [
                                      221138
                                   ],
                 "index": 1
             }
           ]
 }

Upvotes: 0

Views: 2442

Answers (1)

Zhorov
Zhorov

Reputation: 29983

If you use SQL Server 2016+, you need to use OPENJSON() to parse the input JSON. The structure of the input JSON is always important and in this specific case you need to use OPENSJON() with explicit schema twice:

JSON:

DEClARE @json nvarchar(max) = N'{
    "data": [
              {
                  "taggedEntityName": "Organization",
                  "taggedEntityId": [
                                      145642,
                                      145625
                   ],
                  "index": 0
             },
            {
                 "taggedEntityName": "Job",
                 "taggedEntityId": [
                                      221138
                                   ],
                 "index": 1
             }
           ]
 }'

Statement:

 SELECT j1.taggedEntityName, j2.taggedEntityId
 FROM OPENJSON(@json, '$.data') WITH (
    taggedEntityName varchar(50) '$.taggedEntityName',
    taggedEntityId nvarchar(max) '$.taggedEntityId'  AS JSON
 ) j1
 CROSS APPLY OPENJSON(j1.taggedEntityId) WITH (
    taggedEntityId int '$'
 ) j2

Result:

taggedEntityName  taggedEntityId
Organization      145642
Organization      145625
Job               221138

Upvotes: 2

Related Questions