OPENJSON and an Alias

Using Aliases in T-SQL is very common.

We can alias both Tables (FROM clause) and Columns (SELECT clause) and some other things too. It’s all pretty fundamental to writing good T-SQL queries.

Here’s an example

SELECT
	 dbs.collation_name
	,dbs.database_id AS [DatabaseID]
	,[IsReadOnly] = dbs.is_read_only
FROM 
	sys.databases AS dbs
JOIN
	sys.master_files MF
ON 
	dbs.database_id = MF.database_id
WHERE
	dbs.[name] = 'master';
GO

Note that we can use different styles, however, I recommend you standardise however you want to do this.

Anyway, the point of this post if to outline how aliasing is done when using the default and explicit JSON schemas in our T-SQL Queries

Here is an example of aliasing with the default schema

DECLARE @json NVARCHAR(MAX) =
N'
{
    "Configuration Property": {
      "Configuration name": "recovery interval (min)",
      "Value": 0,
      "minimum": 0,
      "maximum": 32767,
      "value_in_use": 0,
      "description": "Maximum recovery interval in minutes",
      "is_dynamic": true,
      "is_advanced": true
    }
}
';
 
SELECT 
      DS.[key]
	 ,DS.[value]
	 ,DS.[type]
FROM  
     OPENJSON(@json, '$."Configuration Property"')  AS DS;
GO

There’s nothing particularly unusual here.

The slightly unusual part may appear when aliasing with an explicit schema.

Here’s an example

DECLARE @json NVARCHAR(MAX) =
N'
{
    "Configuration Property": {
      "Configuration name": "recovery interval (min)",
      "Value": 0,
      "minimum": 0,
      "maximum": 32767,
      "value_in_use": 0,
      "description": "Maximum recovery interval in minutes",
      "is_dynamic": true,
      "is_advanced": true
    }
}
';
 
SELECT 
     ES.[Configuration name] 
    ,ES.[Value]
    ,ES.[minimum]  
    ,ES.[maximum]
    ,ES.[value_in_use]
    ,ES.[description]
    ,ES.[is_dynamic]
    ,ES.[is_advanced] 
FROM  
     OPENJSON(@json, '$."Configuration Property"')  
WITH 
( 
     [Configuration name] NVARCHAR(35) 
    ,[Value]              NVARCHAR(100)   
    ,[minimum]            NVARCHAR(100)  
    ,[maximum]            NVARCHAR(100)  
    ,[value_in_use]       NVARCHAR(100) 
    ,[description]        NVARCHAR(100)
    ,[is_dynamic]         BIT
    ,[is_advanced]        BIT
) AS ES;

Note that the alias for an explicit schema come after the WITH clause.

If we try to put the alias directly after the line

OPENJSON(@json, '$."Configuration Property"') 

Then we will receive an error.

While this may seem trivial it will become very useful when mixing the default schema and an explicit schema.

I hope this has helped on your SQL Server JSON journey. We’ll see more real soon.

Have a great day

Cheers

Marty