This web page was created programmatically, to learn the article in its unique location you'll be able to go to the hyperlink bellow: https://www.sqlservercentral.com/articles/fun-with-jsonand if you wish to take away this text from our website please contact us [ad_1] 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. 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 parameterFirst row is column headingColumn separatorLine feedCreate 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 csvThe 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 0 -- Value used and quotename(t2.) 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_textYou 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. 0 -- Value used and quotename(t2.) [ad_2] This web page was created programmatically, to learn the article in its unique location you'll be able to go to the hyperlink bellow: https://www.sqlservercentral.com/articles/fun-with-jsonand if you wish to take away this text from our website please contact us