Formatted JSON Statement Causes Error

hafnera

I am getting some errors when I format code using SQL Prompt 7.3.0.775 on SQL 2016 using the JSON FOR syntax. Here is some example code:

This code works:

DROP TABLE IF EXISTS #Test
CREATE TABLE #Test
(
    Id INT,
	FileLoadStatsId INT
)

INSERT INTO #Test
        ( Id, FileLoadStatsId )
VALUES  ( 0, -- Id - int
          0  -- FileLoadStatsId - int
          )

INSERT INTO #Test
        ( Id, FileLoadStatsId )
VALUES  ( 2, -- Id - int
          40 -- FileLoadStatsId - int
          )
  
-- SQL Prompt formatting off
DECLARE @InsertRecordJSON NVARCHAR(MAX) = 
(SELECT id, ISNULL(FileLoadStatsId, -1) AS FileLoadStatsId FROM  #Test FOR JSON AUTO);
-- SQL Prompt formatting on

 SELECT id,
    IIF(FileLoadStatsId = -1, NULL, FileLoadStatsId) AS FileLoadStatsId
FROM
    OPENJSON(@InsertRecordJSON)
    WITH
    (
        id INT 'strict $.id',
        FileLoadStatsId INT 'strict $.FileLoadStatsId'
    );

However, if I format it using SQL Prompt to the following, it does not work as SSMS will throw an error of " Property cannot be found on the specified JSON path." The only thing that is changing between the two code blocks is where I am setting the value of the @InsertRecordJSON variable. Here is the code that doesn't work :

DROP TABLE IF EXISTS #Test
CREATE TABLE #Test
(
    Id INT,
	FileLoadStatsId INT
)

INSERT INTO #Test
        ( Id, FileLoadStatsId )
VALUES  ( 0, -- Id - int
          0  -- FileLoadStatsId - int
          )

INSERT INTO #Test
        ( Id, FileLoadStatsId )
VALUES  ( 2, -- Id - int
          40 -- FileLoadStatsId - int
          )
  
 DECLARE @InsertRecordJSON NVARCHAR(MAX) = (
                                              SELECT Id,
                                                  ISNULL(FileLoadStatsId, -1) AS FileLoadStatsId
                                              FROM  #Test
                                              FOR JSON AUTO
                                          );


 SELECT id,
    IIF(FileLoadStatsId = -1, NULL, FileLoadStatsId) AS FileLoadStatsId
FROM
    OPENJSON(@InsertRecordJSON)
    WITH
    (
        id INT 'strict $.id',
        FileLoadStatsId INT 'strict $.FileLoadStatsId'
    );

I have tried taking the formatted code and removing all the CRLFs and the spacing and it still fails. Any thoughts on why the formatted code causes it to throw errors? If I take the formatted code and remove the formatting manually, it still won't work. So it seems like something is happening during the format, that I can't see, that is causing this to fail.

Thanks in advance!

Harry Frankish

Hi hafnera

This had me spooked for a while!

SQL Prompt has changed "SELECT id" to "SELECT Id" when declaring @InsertRecordJSON. This is changed to match the casing of the column defined in #Test because "SQL Prompt > Options > Format > Actions > Apply casing options" is on. This causes the last SELECT to fail because you've got 'strict $.id' rather than 'strict $.Id'. SQL Prompt doesn't change the casing in strings so it results in a mismatch. I think changing to 'strict $.Id' and then formatting should solve the problem.

I think SQL Prompt is working as intended here but please let me know if I've misunderstood what's happening!

Many thanks
Harry

hafnera

That is a good catch! I stared at those statements forever and never noticed it! It is possible that the "Apply casing options" feature could do some deeper analysis when the JSON statements are used so that it does not change case on things like that? I prefer the casing options, but it is a paint to have to run SQL prompt and then go back and correct the casing stuff manually.

Michael Clark

Thanks for the feedback, I think you have a point there -- we've logged this as SP-6093 and will get back to you if we get a chance to fix it