About

Use JSON as a complex parameter

SQL Server allows you to pass a JSON document as a parameter. Use this feature to pass complex parameter down to the DB script.

Imagine you have a JSON document with the following profile

Define a database GET operation so that we can pass Parameters to the database profile (script):

The profile has one parameter which is a JSON document:

Populate the Parameter in the Connector shape: Current Data

This is how your script can then use the JSON to flatten it:

/*  ======================================================================
    <no functionality>
    ----------------------------------------------------------------------
    05.07.2023  msc -   Created
    ======================================================================
*/

DECLARE @json nvarchar(max) = -- ?
-- SAMPLE JSON
'{
    "GRID" : "abc",
    "IdentRegions" :[
        { "ServiceIdentifier" : "SI_1", "ServiceRegion" : "SR_1" },
        { "ServiceIdentifier" : "SI_2", "ServiceRegion" : "SR_2" },
        { "ServiceIdentifier" : "SI_3", "ServiceRegion" : "SR_3" }
    ]
}'

if (len(@json) = 0) set @json = null
if (@json is null) RAISERROR ('JSON must not be null!".', 17, 0)
if (IsJson(@json)=0 ) RAISERROR ('Parameter is not a valid JSON document!".', 17, 0)

declare @currentRecords as table ( 
  GRID nvarchar(50), 
  ServiceIdentifier nvarchar(20), 
  ServiceRegion nvarchar(20)
)

insert into @currentRecords
	-- Flatten hierarchical JSON
	select  GRID, ServiceIdentifier, ServiceRegion 
    from  OPENJSON( @json, '$')
		WITH  ( GRID             nvarchar(50 ) '$.GRID' ,
		       JIdentRegions    nvarchar(max) '$.IdentRegions' AS JSON
		      ) t1
		      OUTER APPLY OPENJSON(t1.JIdentRegions) WITH (
		        ServiceIdentifier nvarchar(20) '$.ServiceIdentifier'
		        , ServiceRegion nvarchar(20)  '$.ServiceRegion'
			    ) t2

The result will look like this

Last updated