OPENJSON in compatibility level 100 SQL SERVER 2016

Question

I need to use the functionality of OPENJSON() in an old database with compatibility level 100. The server runs SQL SERVER 2016. So i came up with this idea: Create another DB "GeneralUTILS" (lvl 130) in the same server and call this function from lvl 100 DB:

CREATE FUNCTION [dbo].[OPENJSON_](@json NVARCHAR(MAX))
RETURNS   @Results TABLE ([Key] nVARCHAR (4000)  , [Value] NVARCHAR(MAX), [Type] INT)
AS
BEGIN
    INSERT INTO @Results 
    SELECT * from OPENJSON(@json) 

    RETURN
END

But i don't have the WITH clause to modify the output table in the lvl 100 database.

Answer 1

Most important might be the question why you need this at all...

I hope I got correctly, what you need:

(Hint: This needs at least SQL-Server 2016)

--create two mock-up-databases

CREATE DATABASE dbOld;
GO
ALTER DATABASE dbOld SET COMPATIBILITY_LEVEL = 100; --v2008
GO

CREATE DATABASE dbForJsonIssues;
GO
ALTER DATABASE dbForJsonIssues SET COMPATIBILITY_LEVEL = 130; --v2016
GO

--Now we will create a stored procedure in the "higher" database

USE dbForJsonIssues;
GO
--Attention: replacing FROM is a very hacky way... Read the hints at the end...
--You might use parameters for the JSON-string and the JSON-path, but then you must use sp_executesql
CREATE PROCEDURE EXEC_Json_Command @Statement NVARCHAR(MAX), @TargetTable NVARCHAR(MAX)
AS
BEGIN
    DECLARE @statementWithTarget NVARCHAR(MAX)=REPLACE(@Statement,'FROM',CONCAT(' INTO ',@TargetTable,' FROM'));
    PRINT @statementWithTarget; --you can out-comment this line...
    EXEC(@statementWithTarget);
END
GO

--Now we go into the "lower" database

USE dbOld;
GO

--A synonym is not necessary, but allows for easier code
CREATE SYNONYM dbo.ExecJson FOR dbForJsonIssues.dbo.EXEC_Json_Command;
GO

--This is how to use it

DECLARE @json NVARCHAR(MAX)=N'[{"someObject":[{"attr1":"11", "attr2":"12"},{"attr1":"21", "attr2":"22"}]}]';

DECLARE @Statement NVARCHAR(MAX)=CONCAT(N'SELECT * FROM OPENJSON(N''',@json,N''',''$[0].someObject'') WITH(attr1 INT,attr2 INT)');

--the target table will be created "on the fly"
--You can use ##SomeTarget too, but be careful with concurrencies in both approaches...
EXEC ExecJson @Statement=@Statement,@TargetTable='dbOld.dbo.SomeTarget';
SELECT * FROM SomeTarget;

--We can drop this table after dealing with the result
DROP TABLE SomeTarget;
GO

--Clean-up (carefull with real-data!)

USE master;
GO
DROP DATABASE dbOld;
DROP DATABASE dbForJsonIssues;

The most important concepts:

We cannot use the JSON-statements directly within the database, but we can create a statement on string base, pass it to the stored procedure and use EXEC() for its execution.

Using SELECT * INTO SomeDb.SomeSchema.SomeTargetTable FROM ... will create a table with the fitting structure. Make sure to use a table not existing in your database.

It is not really needed to pass the target table as parameter, you might place this in the statement yourself. Replacing the FROM in the stored procedure is a very shrewed way and could lead into troubles if from is found in another place.

You might use similar procedures for various needs...

Answer 2

Yeah. No way this would pass the smoke screen at our office. Anyway someone asked me to do something similar, but the use case was for parsing json arrays only. Since Json_Query and Json_Value are available I hacked this together just to give them something to work with. My colleague liked the results. Turns out he's much cooler than I am after he modified it.

Declare @Fields NVarchar(2000) = 'Name,Coolness'
Declare @Delimiter As Varchar(10) = ',';
Declare @Xml As Xml = Cast(('<V>' + Replace(@Fields, @delimiter, '</V><V>') + '</V>' ) As Xml);
Declare @Json Nvarchar(4000) = N'{"Examples":[{"Name": "Chris","Coolness": "10"},{"Name": "Jay","Coolness": "1"}]}';

Exec ('Begin Try Drop Table #JsonTemp End Try Begin Catch End Catch');
Create Table #JsonTemp (JsonNode Nvarchar(1000));

Declare @Max INTEGER = 100;
Declare @Index INTEGER = 0;
While @Index < @Max
Begin
    Declare @Affected Integer = 0;
    Declare @Select Nvarchar(200) = '''' + 'lax$.Examples[' + Convert(Nvarchar, @Index) + ']' + '''';
    Declare @Statement Nvarchar(2000)= 'Select Json_Query(' + '''' + @Json + '''' + ', ' + @Select + ') Where Json_Query(' + '''' + @Json + '''' + ', ' + @Select + ') Is Not Null';

    Insert Into #JsonTemp (JsonNode) Exec sp_executesql @Statement;
    Set @Affected = @@RowCount;
    If (@Affected = 0) Begin Break End
    Set @Index = @Index + 1;
End

Declare @Table Table(Field NVarchar(200));
Declare @Selector NVarchar(500) = 'Json_Value(' + '''' + '{"Node":' + '''' + ' + ' + 'JsonNode' + ' + ' + '''' + '}' + '''' + ', ' + '''' + '$.Node.@Field' + '''' + ')';

Insert Into @Table(Field)
Select N.value('.', 'Varchar(10)') As Field 
From @XML.nodes('V') As A(N);

Declare @Selectors Varchar(8000);
Select @Selectors = Coalesce(@Selectors + ', ', '') + Replace(@Selector, '@Field', Field) + ' As ' + Field
From @Table

Exec ('Select ' + @Selectors + ' From #JsonTemp');