SQL Server - How to use loop to control the value of declare variable from selecting table

Question

For example, we declare a variable to contain JSON statement

DECLARE @json NVARCHAR(MAX)
SET @json = 'json statement1'

and then we can use open JSON to open the data in the JSON statement

select * from openJSON(@json)

If all the JSON statements are stored in a table column, such as table1(NOTE: here 'JSON Statments' are standard JSON statement, and we can use select data from table1 to retrieve all the JSON statements):

ID     Data
1      json statement1
2      json statement2
3      json statement3 
4      json statement4 
...     ...

How to use the loop to open those JSON languages, rather than declaring different variables declare @json1, @json2, @json3..... If we have more than thousands JSON statement in the table, declaring different variables to contain those JSON statements will not be working.

You probably need JSON_QUERY: learn.microsoft.com/en-us/sql/t-sql/functions/… — Paweł Dyl
– Paweł Dyl, Commented Sep 26, 2018 at 13:44
Why do you want to do them one by one? Why not all of them at the same time? — Thom A
– Thom A ♦, Commented Sep 26, 2018 at 13:46
Yes, you are right! The reason to fetch those JSON one by one is because all Those JSON statements contain similar structure but with different vale. Each JSON statement corresponds to one primary key. If we fetch all JSON at the same time, we may need to combine all those statements together which may be a big cost as well. — user3651247
– user3651247, Commented Sep 26, 2018 at 14:01

kkica · Accepted Answer · 2018-09-26 14:16:57Z

2

Use cursors

DECLARE db_cursor CURSOR FOR 
select data from table1 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @json

WHILE @@FETCH_STATUS = 0  
BEGIN  
      select * from openJSON(@json)
      //your other code here 

      FETCH NEXT FROM db_cursor INTO @json
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

More about cursors here

edited Sep 26, 2018 at 14:16

answered Sep 26, 2018 at 13:45

kkica

4,1041 gold badge22 silver badges41 bronze badges

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

6 Comments

Thom A Over a year ago

@GuidoG, although true, FROM OPENJSON, according to the documentation, only returns 3 columns: key,value and type. It would take a functional change to SQL Server to occur for that to happen. That's not likely to change, or at least not unless you're changing SQL Server version. That's not to say, that * can be used, just that it's far less likely to be a problem than it would be against a object like a table or view.

user3651247 Over a year ago

I don’t quite understand why use ` select ID, data from openJSON(@json)` here? The json statements should be retrieved from table1, like select data from table1

kkica Over a year ago

i missunderstood what you wanted. So you have a table with json and you want to loop over the json to do something? See my edited answer

user3651247 Over a year ago

Yes, for each JSON statement, it’s a structured JSON language(contain the key and value). Just wondering how to loop all the structured JSON statements(saved in a table column value) to retrieve the details(the details can be retrieve by using openJSON WITH).

user3651247 Over a year ago

Cursor is a nice option! Thanks Krist!

|

ThorPF · Accepted Answer · 2018-09-26 14:20:29Z

1

I think, if you know about structure of JSON you can use JSON_VALUE function to get the values you need like this:

SELECT 
    JSON_VALUE(t.jsonColumn,'$.key1') AS [Value1],
    JSON_VALUE(t.jsonColumn,'$.key2') AS [Value2] 
FROM table t

answered Sep 26, 2018 at 14:20

ThorPF

536 bronze badges

Comments

Tim Mylott · Accepted Answer · 2018-09-26 15:45:02Z

Here's another option if you have json data stored in a column in multiple records and you want to parse that json for each record.

Cross apply to pass that data column containing the json data to OPENJSON and then you can use WITH.

This is assuming the json data in all the records has the same structure.

You would need to obviously adjust for your data needs. This example is json with an array called "JsonData".

    DECLARE @JsonData TABLE
        (
            [ID] INT
          , [data] NVARCHAR(MAX)
        );

    --Load Test data
    INSERT INTO @JsonData (
                              [ID]
                            , [data]
                          )
    VALUES ( 1 -- ID - int
           , N'{    "JsonData":[      
            {        "Column1":"1","Column2":"TestData1","Column3":"This is more test data for number 1"}
            ,{        "Column1":"2","Column2":"TestData2","Column3":"This is more test data for number 2"}
            ,{        "Column1":"3","Column2":"TestData3","Column3":"This is more test data for number 3"}
            ]  }' -- data - nvarchar(max)
        )
         , ( 2 -- ID - int
           , N'{    "JsonData":[      
            {        "Column1":"4","Column2":"TestData4","Column3":"This is more test data for number 4"}
            ,{        "Column1":"5","Column2":"TestData5","Column3":"This is more test data for number 5"}
            ,{        "Column1":"6","Column2":"TestData6","Column3":"This is more test data for number 6"}
            ]  }' -- data - nvarchar(max)
         )
         , ( 3 -- ID - int
           , N'{    "JsonData":[      
            {        "Column1":"7","Column2":"TestData7","Column3":"This is more test data for number 7"}
            ,{        "Column1":"8","Column2":"TestData8","Column3":"This is more test data for number 8"}
            ,{        "Column1":"9","Column2":"TestData9","Column3":"This is more test data for number 9"}
            ]  }' -- data - nvarchar(max)
         );

    --Here you can see your scenario of json in multiple recrods.
    SELECT *
    FROM   @JsonData;

    --We can parse that json for each record with a cross apply and using WITH
    SELECT [a].[ID]
         , [b].[Column1]
         , [b].[Column2]
         , [b].[Column3]
    FROM   @JsonData [a]
    CROSS APPLY
           OPENJSON([a].[data], '$.JsonData')
               WITH (
                        [Column1] INT '$.Column1'
                      , [Column2] NVARCHAR(200) '$.Column2'
                      , [Column3] NVARCHAR(200) '$.Column3'
                    ) [b];

If your json has nested arrays, that can still be accomplished, basically another cross apply to get into the nested array. Here's an example of dealing with a nested array in this scenario:

    DECLARE @JsonData TABLE
        (
            [ID] INT
          , [data] NVARCHAR(MAX)
        );

    --Load Test data
    INSERT INTO @JsonData (
                              [ID]
                            , [data]
                          )
    VALUES ( 1 -- ID - int
           , N'{
      "JsonData": [
        {
          "Column1": "1",
          "Column2": "TestData1",
          "Column3": "This is more test data for number 1",
          "JsonDataNested": [
            {
              "NestedColumn1": "NestedColumn1",
              "NestedColumn2": "NestedColumn2"
            }
            ,{
              "NestedColumn1": "NestedColumn1_1",
              "NestedColumn2": "NestedColumn2_2"
            }
          ]
        }
      ]
    }'         -- data - nvarchar(max)
        );

    --We can parse that json for each record with a cross apply and using WITH
    SELECT [a].[ID]
         , [b].[Column1]
         , [b].[Column2]
         , [b].[Column3]
         , [c].*
    FROM   @JsonData [a]
    CROSS APPLY
           OPENJSON([a].[data], '$.JsonData') --top array
               WITH (
                        [Column1] INT '$.Column1'
                      , [Column2] NVARCHAR(200) '$.Column2'
                      , [Column3] NVARCHAR(200) '$.Column3'
                      , [JsonDataNested] NVARCHAR(MAX) AS JSON --Nested array
                    ) [b]
    CROSS APPLY
           OPENJSON([b].[JsonDataNested], '$')
               WITH (
                        [NestedColumn1] NVARCHAR(200) '$.NestedColumn1'
                      , [NestedColumn2] NVARCHAR(200) '$.NestedColumn2'
                    ) [c];

Collectives™ on Stack Overflow

SQL Server - How to use loop to control the value of declare variable from selecting table

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related