MS SQL - Extract JSON from a Text column

Robin Michael -

Previously a design choice was made to store raw JSON responses in a table as  VARCHAR(MAX) field.  As the person coming in afterwards to understand and debug the problem this was a pain to read and work with.  First I don't know the schema and second it sits in the database as a blob, but is in fact very careful structured and needs to be destructured properly to get the intent.

The code running this application is complicated and not available for inspection at this time (it will be at soon enough, but due to immediate needs was not available when requested).  This is how I extracted the key data and formatted to run some queries against and find the structured intent out.

DECLARE @jsonResponses TABLE
(
    id INT IDENTITY(1,1),
    jsonText VARCHAR(MAX)
)
INSERT INTO @jsonResponses
(
    jsonText
)

SELECT [t].[Response]
FROM [dbo].[TargetTable] as [t]
WHERE [t].[Id] = 1

DECLARE @count INT
SELECT @count = COUNT(j.jsonText)
FROM @jsonResponses j

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= @count)
BEGIN
    DECLARE @textToParse VARCHAR(MAX)
    SELECT @textToParse = jj.jsonText
    FROM @jsonResponses jj
    WHERE jj.id = @Counter
    SELECT x.[Key], x.[Value] 
    FROM OPENJSON(@textToParse, '$') AS x
    SET @Counter  = @Counter  + 1
END

First create a sql table variable and insert the relevant raw JSON responses

DECLARE @jsonResponses TABLE
(
    id INT IDENTITY(1,1),
    jsonText VARCHAR(MAX)
)

INSERT INTO @jsonResponses
(
    jsonText
)
SELECT [t].[Response]
FROM [dbo].[TargetTable] as [t]
WHERE [t].[Id] = 1

Now run through each raw response and split it up and write each key+value pair as a sql row

DECLARE @count INT
SELECT @count = COUNT(j.jsonText)
FROM @jsonResponses j

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= @count)
BEGIN
    DECLARE @textToParse VARCHAR(MAX)
    SELECT @textToParse = jj.jsonText
    FROM @jsonResponses jj
    WHERE jj.id = @Counter
    SELECT x.[Key], x.[Value] 
    FROM OPENJSON(@textToParse, '$') AS x
    SET @Counter  = @Counter  + 1
END

Key here is the OPENJSON function breaking apart the raw JSON from the @textToParse variable