Guide to Working With JSON in SQL 2016 Part 1 - Storing and Querying
/Microsoft SQL Server 2016 introduced support for JSON. Microsoft added the FOR JSON clause to build a JSON structured result from an existing relational DB, and they also added a handful of new functions to query a JSON document stored in a SQL table. That is the focus of Part 1 in this series, how to store JSON documents in a SQL database and how to query those documents.
Here are the new functions we will be exploring in this post:
JSON_VALUE – Extract a value from a JSON string
OPENJSON – Table Valued Function that parses JSON text. Returns objects and properties as rows and columns.
A few important notes:
- JSON is case sensitive. If the JSON object is "FilmDetails" and you try querying "filmdetails", it will fail. Make sure you check your case when trouble shooting your queries.
- There isn't a JSON data type in SQL. We have JSON support in SQL 2016 not a data type. JSON documents are stored as nvarchar(max), but don't let this discourage you from exploring these features.
- You can create a CHECK constraint on the table you store your JSON documents to check the document is valid JSON, but this is not required (ISJSON(<JSONData>) > 0
- You can create your table as memory optimized, FYI.
If you need a refresher on JSON structures, Look Here.
Here is our table definition and the JSON document we will be storing in the table:
CREATE TABLE JSONTable (
ID int IDENTITY (1,1) PRIMARY KEY CLUSTERED
,JSONDocument nvarchar(max) );
INSERT INTO JSONTable
SELECT '{
"FilmDetails":{
"ProductNumber":"9912088751",
"Title":"Brave",
"Type":"Movie",
"Runtime":93,
"ReleaseYear":2012,
"Synopses":[
{
"Locale":"en",
"Text":"Princess Merida uses her bravery and archery skills to battle a curse and restore peace..."
},
{
"Locale":"de",
"Text":"Animiert"
},
{
"Locale":"fr",
"Text":"Au coeur des contrées sauvages dÉcosse, Merida, la fille du roi Fergus et de la reine Elinor..."
}
],
"Assets":[
{
"Name":"Stickers",
"AssetType":"Stickers",
"FileType":"PDF",
"Location":"http://media.brave.stickers.pdf",
"Locale":"en-US"
},
{
"Name":"Trailer - WMV",
"AssetType":"Trailer - WMV",
"FileType":"Video",
"Location":"http://youtu.be/Shg79Shgn",
"Locale":"en-US"
}
]
}
}';First let's look at the JSON_VALUE function, we will use this to return values from our JSON document.
SELECT JSON_VALUE(JSONDocument, '$.FilmDetails.ProductNumber') AS ProductNumber ,JSON_VALUE(JSONDocument, '$.FilmDetails.Title') AS Title ,JSON_VALUE(JSONDocument, '$.FilmDetails.Type') AS ContentType ,JSON_VALUE(JSONDocument, '$.FilmDetails.Runtime') AS Runtime ,JSON_VALUE(JSONDocument, '$.FilmDetails.ReleaseYear') AS ReleaseYear FROM JSONTable
In the example above, we are simply grabbing the values from the name/value pairs found in the FilmDetails object. Simple enough, but we also have a few arrays defined and we want to extract values from the different arrays as well. You could try calling the specific array key, but if you have many arrays you might not know the position. For example:
SELECT JSON_VALUE(JSONDocument, '$.FilmDetails.ProductNumber') AS ProductNumber ,JSON_VALUE(JSONDocument, '$.FilmDetails.Synopses[0].Text') AS SynopsesTextEn ,JSON_VALUE(JSONDocument, '$.FilmDetails.Synopses[1].Locale') AS Locale ,JSON_VALUE(JSONDocument, '$.FilmDetails.Assets[1].Location') AS TrailerLocation FROM JSONTable
You see the [0], [1] which follows the Synopses object? That is the key value for the element we are trying to extract our values from. While this does work, let's take a look at another option, OPENJSON. OPENJSON is a table valued function that returns objects and properties as rows and columns. You can use OPENJSON with the default schema or you can create a custom schema for your output. Each produces very different results so let's look at both of them. First using the default schema:
SELECT JSON_VALUE(JSONDocument, '$.FilmDetails.ProductNumber') AS ProductNumber ,JSON_VALUE(JSONDocument, '$.FilmDetails.Title') AS Title ,[key] AS JSONObject ,[value] AS JSONValue ,[type] AS TypeOfValue FROM JSONTable CROSS APPLY OPENJSON(JSONDocument)
When you use OPENJSON with the default schema, the function returns a table with one row for each property of the object or for each element in the array. What happens if we target an object in the function?
SELECT JSON_VALUE(JSONDocument, '$.FilmDetails.ProductNumber') AS ProductNumber ,JSON_VALUE(JSONDocument, '$.FilmDetails.Title') AS Title ,[key] AS ArrayElementKey ,[value] AS ArrayValue ,[type] AS TypeOfValue FROM JSONTable CROSS APPLY OPENJSON(JSONDocument, '$.FilmDetails.Synopses')
If jsonExpression contains a JSON object (in this case $.FilmDetails.Synopses) , OPENJSON returns all the first level properties of the object. That's cool, but we still need to return values from the different arrays. Let's try to define a custom schema and see what happens:
SELECT * FROM JSONTable CROSS APPLY OPENJSON(JSONDocument, '$.FilmDetails.Synopses') WITH ( Locale varchar(3) '$.Locale' ,SynopsesText nvarchar(2000) '$.Text') WHERE JSON_VALUE(JSONDocument, '$.FilmDetails.Title') LIKE '%Brave%'
Added a where clause to simplify the result.
We now have results for Locale and SynopsesText, but we are also getting the entire JSONDocument and ID. We want to be able to return a single filtered result. Notice in the query below we can use the column names defined in the WITH clause of the OPENJSON table valued function at the beginning of our select. We are also adding the value from JSON_VALUE from the FilmDetails object. The output is starting to look more meaningful.
SELECT JSON_VALUE(JSONDocument, '$.FilmDetails.ProductNumber') AS ProductNumber ,JSON_VALUE(JSONDocument, '$.FilmDetails.Title') AS Title ,JSON_VALUE(JSONDocument, '$.FilmDetails.Type') AS ContentType ,JSON_VALUE(JSONDocument, '$.FilmDetails.Runtime') AS Runtime ,JSON_VALUE(JSONDocument, '$.FilmDetails.ReleaseYear') AS ReleaseYear ,Locale ,SynopsesText FROM JSONTable CROSS APPLY OPENJSON(JSONDocument, '$.FilmDetails.Synopses') WITH ( Locale varchar(3) '$.Locale' ,SynopsesText nvarchar(2000) '$.Text') WHERE JSON_VALUE(JSONDocument, '$.FilmDetails.Title') LIKE '%Brave%' AND Locale = 'en'
We can assume that our JSON document will have multiple arrays, and we'll need to extract the details from those arrays, so how do we do that? Let's try to add another CROSS APPLY to the "Assets" array and add the values to our main SELECT to see what we get:
SELECT JSON_VALUE(JSONDocument, '$.FilmDetails.ProductNumber') AS ProductNumber ,JSON_VALUE(JSONDocument, '$.FilmDetails.Title') AS Title ,JSON_VALUE(JSONDocument, '$.FilmDetails.Type') AS ContentType ,JSON_VALUE(JSONDocument, '$.FilmDetails.Runtime') AS Runtime ,JSON_VALUE(JSONDocument, '$.FilmDetails.ReleaseYear') AS ReleaseYear ,Locale ,SynopsesText ,Name AS AssetName ,FileType AS AssetFileType ,[Location] AS AssetLocation FROM JSONTable CROSS APPLY OPENJSON(JSONDocument, '$.FilmDetails.Synopses') WITH ( Locale varchar(3) '$.Locale' ,SynopsesText nvarchar(2000) '$.Text') CROSS APPLY OPENJSON(JSONDocument, '$.FilmDetails.Assets') WITH ( Name varchar(25) '$.Name' ,FileType varchar(25) '$.FileType' ,[Location] nvarchar(500) '$.Location' ) WHERE JSON_VALUE(JSONDocument, '$.FilmDetails.Title') LIKE '%Brave%' AND Locale = 'en' AND FileType = 'video'
At this point you should be returning a single line of filtered data that gets values from multiple arrays and objects in your JSON document. You can add indexes to your JSON data to increase performance. Microsoft has introduced an interesting new feature set with JSON support in 2016, you should check it out in its entirety.
In part 2 of this series we are going to explore outputting JSON documents from normalized tables. We will look at outputting arrarys and assembling complex JSON documents from SQL.
