SQL Server 2016 SSMS Json Formatting

Question

I am working with JSON in SSMS in SQL Server 2016. Is there any way to get around that the results of a FOR JSON statement are presented as an XML column?

Are there any settings changes, additional components, external tools, etc. or even hacks to make this less painful?

In my ideal world, clicking on a JSON column (or an XML column for that matter) would open in my text editor of choice, such as Visual Studio Code, with SSMS knowing that the string in the column is JSON and saving it to file with a .json extension.

Hinshaw

Nope. I just copy / paste into VSCode and do a Code Format from there... – Almazan

Answer 1

Azure Data Studio will do most of what you want.

Run your query:

Clicking on the RESULTS will open a new tab with formatted JSON:

Brazilein

Yeah. I had actually found Azure Data Studio since posting this question. The name is a bit misleading as it is not just for Azure. I'm still surprised that the SMS (including 2019 so far) support for JSON is so weak... – Hinshaw

I agree -- it's a nice tool with a horrible name. – Brazilein

why they didn't add simple feature as "View as text" | "View as table" | "View as Json" !! thanks – Rivalee

I have a table with a NVarchar(Max) column containing JSON - any idea if it is possible to format this as nicely formatted JSON like the above? – Scopolamine

Answer 2

I just want to add that the "MsSql Extension" for VS Code is really starting to be very useful with the kind of JSON support that you would expect.

It doesn't replace SSMS but works for basic SQL work and when you click on a JSON column it actually opens in formatted JSON! The added bonus is that I usually have VS Code open for other things anyway these days.

Hinshaw

Answer 3

How about something like this? I just wrote this so it's not "carefully" tested, but seems to work.

DROP FUNCTION IF EXISTS PrettyJSONInternal
GO
CREATE OR ALTER FUNCTION PrettyJSONInternal (
  @JSON NVARCHAR(MAX),
  @IndentString AS NVARCHAR(32) = '  ',
  @Level INT = 0,
  @NL NVARCHAR(2),
  @CurrIndent AS NVARCHAR(MAX) = '  '
) RETURNS NVARCHAR(MAX)
AS BEGIN
  DECLARE @JSONTbl AS TABLE (i INT IDENTITY(0,1), k NVARCHAR(MAX), v NVARCHAR(MAX), t INT)
  DECLARE @N AS INT, @I AS INT = 0

  INSERT INTO @JSONTbl (k, v, t)
  SELECT [key], [value], [type]
    FROM OPENJSON(@JSON)
  SET @N = @@ROWCOUNT

  DECLARE @Return AS NVARCHAR(MAX) = ''
  IF @Level = 0 SET @Return = '{' + @NL
  SET @I = 0
  WHILE @I < @N
  BEGIN
    DECLARE @EOL AS NVARCHAR(10) = ',' + @NL
    IF @I + 1 = @N SET @EOL = @NL
    DECLARE @k AS NVARCHAR(MAX), @v AS NVARCHAR(MAX), @t AS INT
    SELECT @k = k, @v = v, @t = t FROM @JSONTbl WHERE i = @I
    IF @t = 1
      SET @Return = @Return + @CurrIndent + '"' + @k + '" : "' + @v + '"' + @EOL
    ELSE IF @t = 2 OR @t = 3
      SET @Return = @Return + @CurrIndent + '"' + @k + '" : ' + @v + @EOL
    ELSE IF @t = 4
      SET @Return = @Return + @CurrIndent + '"' + @k + '" : [' + @NL + 
                    dbo.PrettyJSONInternal(@v, @IndentString, @Level + 1, @NL, @CurrIndent + @IndentString) +
                    @CurrIndent + ']' + @EOL
    ELSE IF @t = 5
      SET @Return = @Return + @CurrIndent + '{' + @NL + 
                    dbo.PrettyJSONInternal(@v, @IndentString, @Level + 1, @NL, @CurrIndent + @IndentString) +
                    @CurrIndent + '}' + @EOL
    SET @I = @I + 1
  END
  IF @Level = 0 SET @Return = @Return + '}' + @NL

  RETURN @Return
END
GO
CREATE OR ALTER FUNCTION PrettyJSON (
  @JSON NVARCHAR(MAX)
) RETURNS NVARCHAR(MAX)
AS BEGIN
  RETURN dbo.PrettyJSONInternal(@JSON, '  ', 0, CHAR(13) + CHAR(10), '  ')
END
GO

Promissory

This has some problems with arrays of literals, escaping double-quotes, but could probably be fixed pretty easily. – Promissory