Compare between JSON string using openjson in SQL Server 2016

Question

I have two scenarios of inserting json string. I check the json string is already exists or not using the below code and then I insert into the table

**Scenario - 1**


 declare @jsonStr nvarchar(max) = {"Manufacurer": "test", "Model": "A", "Color":"New Color","Thickness":"1 mm"}
        declare @SqlStr nvarchar(max), @counts int

        Select @dynamicFilter = Coalesce(@dynamicFilter,'') +' and Json_Value(ItemOfProduct,''$."'+ [key] +'"'') = ''' +[value]+'''' from openjson(jsonStr)

        set @SqlStr = 'select @cnt = count(*) from tblTest where '+@dynamicFilter
        Exec sp_executesql @SqlStr, N'@cnt int output', @cnt = @counts output

        if(@counts < 1)
        Begin
            //insert the data into the table
        End




**Scenario - 2** 


set @jsonStr = {"Manufacurer": "test", "Model": "A", "Color":"New Color"}
    //Do the same above process of inserting

In the 2nd Scenario, my data is not getting inserted into table as the count is 1. But I want both the string in scenario 1 and 2 to be treated as different and both the data to be inserted into database.

Answer 1

I think the best solution here is actually to use JSON_QUERY instead of JSON_VALUE, since your keys may be dynamic. Once you switch to JSON_QUERY, you no longer need to execute dynamic SQL.

CREATE TABLE #tblTest
(
    id INT IDENTITY(1, 1)
    ,ItemOfProduct NVARCHAR(MAX)
);


/*Scenario 1*/
DECLARE @jsonStr NVARCHAR(MAX) = N'{"Manufacturer": "test", "Model": "A", "Color":"New Color","Thickness":"1 mm"}';
DECLARE @counts INT;

SELECT @counts = COUNT(*)
FROM #tblTest
WHERE JSON_QUERY(ItemOfProduct, '$') = JSON_QUERY(@jsonStr, '$');

IF(@counts < 1)
BEGIN
    INSERT INTO #tblTest(ItemOfProduct)
    VALUES(@jsonStr);
END;


/*Scenario 2*/
SET @jsonStr =
    N'{"Manufacturer": "test", "Model": "A", "Color":"New Color"}';

SELECT @counts = COUNT(*)
FROM #tblTest
WHERE JSON_QUERY(ItemOfProduct, '$') = JSON_QUERY(@jsonStr, '$');

IF(@counts < 1)
BEGIN
    INSERT INTO #tblTest(ItemOfProduct)
    VALUES(@jsonStr);
END;

SELECT *
FROM #tblTest;