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:
- JSON parameter
- First row is column heading
- Column separator
- 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:
- The textual content with the variable names. The variable names are positioned between brackets (e.g. [NAME]).
- 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