What are the challenges you face when working across database platforms? Take the survey
Options

Formatted JSON Statement Causes Error

hafnerahafnera Posts: 16 New member
edited February 16, 2017 8:01AM in SQL Prompt
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!

Comments

  • Options
    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
  • Options
    hafnerahafnera Posts: 16 New member
    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.
  • Options
    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 :)
    Michael Clark
    Technical Lead, SQL Monitor
Sign In or Register to comment.