Hiya,
I’ve been working with JSON a lot lately, and one thing that frustrated me was I couldn’t find a way to take a sample .json file and generate JSON schema. There’s probably a tool out there, but my google foo wasn’t getting me to it.
So I turned to some of my favourite tools; Azure Data Studio and Visual Studio Code.
Let’s start with a sample file.
[
{ "ProductID": 680
, "Name": "HL Road Frame - Black, 58"
, "Color": "Black"
}
]
Now what well let’s use T-SQL to help us, the caveat here is we’re going to use OPENJSON, which Microsoft introduced with SQL 2016.
DECLARE @jsonData nvarchar(max);
SET @jsonData='
[
{ "ProductID": 680
, "Name": "HL Road Frame - Black, 58"
, "Color": "Black"
}
]
select * from OpenJSON(@jsonData)
Ok, well, it’s a start, type=5 what’s that, answer it is an object. It makes sense because it’s surrounded by ‘[]’. Hmm, so if I remove those, then what.
DECLARE @jsonData nvarchar(max);
SET @jsonData='
[
{ "ProductID": 680
, "Name": "HL Road Frame - Black, 58"
, "Color": "Black"
}
] select * from OpenJSON(replace(replace(@jsonData,'[',''),']',''))
Much better, now we can see other types like string (type=1) and int (type = 2).
So now, we can add a calculated column to generate the JSON structure for that column.
DECLARE @jsonData nvarchar(max);
SET @jsonData='
[
{ "ProductID": 680
, "Name": "HL Road Frame - Black, 58"
, "Color": "Black"
}
]'
select *
, [key]
, [value]
, [type]
,JSONSchema = CASE WHEN [type] IN (0, 1, 2) THEN CONCAT (',', [key], ' VARCHAR(4000) ', '''$.', [key], '''')
ELSE CASE WHEN [type] = 3 THEN CONCAT (',', [key], ' bit ', '''$.', [key], '''') ELSE CASE WHEN [type] IN (4, 5) THEN CONCAT (',', [key], ' NVARCHAR(max) ', '''$.', [key], ''' AS JSON')
END END END
from OpenJSON(replace(replace(@jsonData,'[',''),']',''))
Yeah now we have the JSON Schema. There’s probably many ways to get this outcome but hey it was fun giving this a go.
Nice article. You can also use a Parse JSON step in Power Automate and the Generate Schema from Sample.
Oh great suggestion adding to my try this out list. I’ve used Power Automate for PowerBI, Sharepoint file processing, emails so this will be great to try too