Enjoyable with JSON – SQLServerCentral

Last yr, I used quite a lot of JSON to trade information between programs. There are a number of methods to extract information from a JSON file, however there may be one particular, in all probability less-used chance that I’d like to focus on. For one mission, I obtained JSON recordsdata containing a variable variety of parameters and their values. If I might have recognized all of the parameter names, I may have used a extra frequent method to extract the information, however that was not the case.

Now JSON incorporates key-value pairs. In SQL Server, you will have the power to get these key and worth pairs with the OPENJSON table-valued perform. This provides you a consequence desk with the keys, the values, and the kind of worth (see OPENJSON (Transact-SQL) – SQL Server | Microsoft Learn. With this resolution in thoughts, I bought enthusiastic about different prospects.

Let’s begin with an instance. First we have to create a desk and insert a number of rows.

-- Step 1: Create desk with take a look at information 
create desk #test_data 
(
id int major key,
title varchar(100),
birth_date date
)

-- Step 2: Insert rows  
insert into #test_data
values(1, 'Olivia', '2025-01-05'),
  (2, 'Emma', '2025-03-02'),
  (3, 'Liam', '2025-11-15'),
  (4, 'Noah', '2025-12-22')

Now we’re capable of choose the rows within the desk as JSON output with the FOR JSON PATH clause.

-- Step 3: Create JSON for desk information
choose t.* from #test_data as t for json path 

The result’s a JSON containing all rows as an array of JSON objects (see Format Query Results as JSON with FOR JSON – SQL Server | Microsoft Learn)

In the subsequent step we’re going to separate the consequence JSON output from earlier step, with the OPENJSON desk valued perform.

-- Step 4: Select from JSON 
choose * from openjson((choose t.* from #test_data as t for json path)) t

The result’s a key-value-pair for every array factor within the JSON array. The secret’s the factor quantity within the array, the worth is a JSON (sort=5) from the row.

Now we’re going to cut up the JSON values once more to key-value pairs, however we need to preserve the factor variety of the unique row.

-- Step 5: Break down JSON to a single row for every column
choose t1. as row, t2.* 
from openjson((choose t.* from #test_data as t for json path)) t1
cross apply openjson(t1.worth) t2

The consequence exhibits the factor as row and a key-value-pair for every column.

With a little bit extra effort, you possibly can choose the unique key as an alternative of the factor quantity. To forestall the column is added twice (id and key_column), the WHERE-clause is used to omit the important thing column within the consequence.

-- Step 6: Break down JSON to a single row for every column and use key as an alternative of row
choose 
   json_value(t1.worth, '$.key_column') as key_column,
   t2.* 
   from openjson((choose t.id as key_column, t.* from #test_data as t for json path)) t1
   cross apply openjson(t1.worth) t2
   the place t2. <> 'key_column'

Now the consequence exhibits the important thing column as an alternative of the row.

The final step is to place the JSON, for instance for one chosen row, right into a variable. This provides us the likelihood to execute a perform or process with a JSON variable as parameter.

-- Step 7: Use variable with JSON 
declare @variable nvarchar(max) = (choose t.* from #test_data as t the place t.id = 1 for json path)
choose t1., t2.* 
from openjson(@variable) t1
cross apply openjson(t1.worth) t2

The result’s the key-value-pair for every column of the chosen row.

Now we’re capable of cut up a JSON In a row for every column, we may additionally use this to create a CSV or to interchange a number of values in a textual content.

We frequently get the query to export the results of a question to a file. The first query is at all times: “What are you going to do with the result?”. But, typically an export is required.

Now we’re capable of break down a JSON to a row for every column, we may additionally reassemble it to a comma separated file. The instance question from step 5 already provides us all the knowledge we want.

We are going to create a perform JSON_to_CSV with the next parameters:

1. JSON parameter
2. First row is column heading
3. Column separator
4. Line feed

Create the perform under.

-- Step 8: Create and execute perform JSON to CSV 
create or alter perform "json_to_csv"
(
   @json_parameter             nvarchar(max),
   @first_row_column_heading   int = 1,
   @column_separator           char(1) = ';',
   @line_feed                  char(1)        
)
returns nvarchar(max)
as
start
    -- Transform JSON to CSV. For the JSON parameter use a question like "select * from table for json path, include_null_values")
    return(choose string_agg(csv.row_value, @line_feed) -- Line feed
             from (choose prime 1  -- Column heading
                      (choose string_agg(t22.worth, @column_separator) 
                            from(choose quotename(t2., '"') as worth
                                    from openjson(t1.worth) t2
                                    the place t2.sort in (0, 1, 2, 3)) t22) as row_value  -- Check varieties to make use of.
                         from openjson(@json_parameter) t1
                         the place @first_row_column_heading = 1 
                       union all 
                       choose 
                         (choose string_agg(t22.worth, @column_separator) from(choose  -- Column separator. Text in double quotes.
                                           case when t2.sort = 0 then 'NULL'
                                                when t2.sort = 1 then quotename(t2.worth, '"')
                                                when t2.sort in (2, 3) then t2.worth
                                                finish as worth
                                           from openjson(t1.worth) t2) t22) as row_value    
                            from openjson(@json_parameter) t1
                         ) csv
       )
finish
GO 

We can take a look at the perform with the question under. Be certain so as to add INCLUDE_NULL_VALUES when deciding on rows as JSON. Otherwise the columns with NULL-values aren’t current within the consequence.

-- Step 9: Execute JSON to CSV
choose dbo.json_to_csv((choose * from #test_data for json path, include_null_values), 1, ';', char(10)) as csv

The result’s one lengthy string with separators for columns and rows. If we set the consequence output within the SSMS to textual content, we get the consequence under.

Note: If the values within the JSON may include the column separator or the road feed character, the consequence might be unpredictable. You first have to interchange these characters or use a distinct characters.

Recently I needed to substitute a number of values in a textual content. I needed to create a generic perform that might substitute a number of values directly. This perform would have 2 parameters:

1. The textual content with the variable names. The variable names are positioned between brackets (e.g. [NAME]).
2. An inventory of variable names and values.

For the second parameter I used a JSON string.

To make sure that I solely course of variables which are used within the textual content, I added a the place clause to examine if the variable exists. I didn’t need to use a cursor to loop by way of the variable names. To forestall an infinite loop. I added a most (100). For instance, when the worth of a variable additionally incorporates a variable title with brackets.

-- Step 10: Create perform Merge textual content
create or alter perform "merge_text"
(
   @source_text   nvarchar(max),
   @json_parms    nvarchar(max)
)
returns nvarchar(max)
as
start

    declare @loop_count int,
            @result_text nvarchar(max) = @source_text 
    
    if isjson(@json_parms) = 1 
    start 
        choose @loop_count = 1  -- units @@rowcount for first loop
        whereas @@ROWCOUNT = 1  
          and @loop_count < 100
        start 
            choose prime 1
                @loop_count += 1, 
                @result_text = substitute(@result_text, t1.quote_name_key, t1.[value])
                from (choose 
                         quotename(t2.) as quote_name_key,
                         t2.[value],
                         t2.[type]
                         from openjson(@json_parms) t1
                         cross apply openjson(t1.worth) t2 
                         the place charindex(quotename(t2.), @result_text) > 0  -- Value used 
                           and quotename(t2.) <> t1.worth
                           and t2.worth will not be null
                         ) t1 
        finish 
    finish 
    return @result_text
finish
GO

You can take a look at the perform with the instance @variable worth from the #test_data above.

-- Step 11 Execute merge textual content 
declare @textual content nvarchar(max) = N'Hello [name], I need to merge your document with ID=tag:google.com,2013:googlealerts/feed:12474734899934552135, title=[name] and birthdate [birth_date] into the textual content.',
        @variable nvarchar(max) = (choose t.* from #test_data as t the place t.id = 1 for json path)

choose dbo.merge_text(@textual content, @variable) as merged_text

This is what I needed to share.

Thanks Erland Sommarskog to your ideas!

The final step cleans up the created objects.

-- Step 12: Clean-up
drop desk if exists #test_data
drop perform if exists dbo.json_to_csv
drop perform if exists dbo.merge_text

You can discover the whole SQL script under.

Have enjoyable with JSON!

-- Step 1: Create desk with take a look at information 
create desk #test_data 
(
id int major key,
title varchar(100),
birth_date date
)

-- Step 2: Insert rows  
insert into #test_data
values(1, 'Olivia', '2025-01-05'),
  (2, 'Emma', '2025-03-02'),
  (3, 'Liam', '2025-11-15'),
  (4, 'Noah', '2025-12-22')

-- Step 3: Create JSON for desk information
choose t.* from #test_data as t for json path 

-- Step 4: Select from JSON 
choose * from openjson((choose t.* from #test_data as t for json path)) t

-- Step 5: Break down JSON to a single row for every column
choose t1. as row, t2.* 
from openjson((choose t.* from #test_data as t for json path)) t1
cross apply openjson(t1.worth) t2

-- Step 6: Break down JSON to a single row for every column and use key as an alternative of row
choose 
   json_value(t1.worth, '$.key_column') as key_column,
   t2.* 
   from openjson((choose t.id as key_column, t.* from #test_data as t for json path)) t1
   cross apply openjson(t1.worth) t2
   the place t2. <> 'key_column'

-- Step 7: Use variable with JSON 
declare @variable nvarchar(max) = (choose t.* from #test_data as t the place t.id = 1 for json path)
choose t1., t2.* 
from openjson(@variable) t1
cross apply openjson(t1.worth) t2
GO

-- Step 8: Create and execute perform JSON to CSV 
create or alter perform "json_to_csv"
(
   @json_parameter             nvarchar(max),
   @first_row_column_heading   int = 1,
   @column_separator           char(1) = ';',
   @line_feed                  char(1)        
)
returns nvarchar(max)
as
start
    -- Transform JSON to CSV. For the JSON parameter use a question like "select * from table for json path, include_null_values")
    return(choose string_agg(csv.row_value, @line_feed) -- Line feed
             from (choose prime 1  -- Column heading
                      (choose string_agg(t22.worth, @column_separator) 
                            from(choose quotename(t2., '"') as worth
                                    from openjson(t1.worth) t2
                                    the place t2.sort in (0, 1, 2, 3)) t22) as row_value  -- Check varieties to make use of.
                         from openjson(@json_parameter) t1
                         the place @first_row_column_heading = 1 
                       union all 
                       choose 
                         (choose string_agg(t22.worth, @column_separator) from(choose  -- Column separator. Text in double quotes.
                                           case when t2.sort = 0 then 'NULL'
                                                when t2.sort = 1 then quotename(t2.worth, '"')
                                                when t2.sort in (2, 3) then t2.worth
                                                finish as worth
                                           from openjson(t1.worth) t2) t22) as row_value    
                            from openjson(@json_parameter) t1
                         ) csv
       )
finish
GO 

-- Step 9: Exceute JSON to CSV
if exists(choose * from sys.objects o the place o.title="json_to_csv" and o.sort="FN")
start 
    choose dbo.json_to_csv((choose * from #test_data for json path, include_null_values), 1, ';', char(10)) as csv
finish 
GO

-- Step 10: Create perform Merge textual content
create or alter perform "merge_text"
(
   @source_text   nvarchar(max),
   @json_parms    nvarchar(max)
)
returns nvarchar(max)
as
start

    declare @loop_count int,
            @result_text nvarchar(max) = @source_text 
    
    if isjson(@json_parms) = 1 
    start 
        choose @loop_count = 1  -- units @@rowcount for first loop
        whereas @@ROWCOUNT = 1  
          and @loop_count < 100
        start 
            choose prime 1
                @loop_count += 1, 
                @result_text = substitute(@result_text, t1.quote_name_key, t1.[value])
                from (choose 
                         quotename(t2.) as quote_name_key,
                         t2.[value],
                         t2.[type]
                         from openjson(@json_parms) t1
                         cross apply openjson(t1.worth) t2 
                         the place charindex(quotename(t2.), @result_text) > 0  -- Value used 
                           and quotename(t2.) <> t1.worth
                           and t2.worth will not be null
                         ) t1 
        finish 
    finish 
    return @result_text
finish
GO

-- Step 11 Execute merge textual content 
if exists(choose * from sys.objects o the place o.title="merge_text" and o.sort="FN")
start 
declare @textual content nvarchar(max) = N'Hello [name], I need to merge your document with ID=tag:google.com,2013:googlealerts/feed:12474734899934552135, title=[name] and birthdate [birth_date] into the textual content.',
        @variable nvarchar(max) = (choose t.* from #test_data as t the place t.id = 1 for json path)

choose dbo.merge_text(@textual content, @variable) as merged_text
finish

    -- Step 12: Clean-up
    drop desk if exists #test_data
    drop perform if exists dbo.json_to_csv
    drop perform if exists dbo.merge_text