Microsoft has finally implement support for JSON data in SQL Server 2016 and in Azure SQL Database. In a previous blog post I JSON and the NoSQL “advantage” over SQL Server I discussed which JSON support was missing from SQL Server and I’m very happy to see this much of this functionality coming. Microsoft has done some nice work with JSON support, but sadly I find that is not really as comprehensive as I had hoped.
How to Work with JSON Data in SQL Server 2016
OPENJSON
- Converting JSON to rowset data
EX
DECLARE @json NVARCHAR(MAX) = N'
{
"id": 1,
"name": "A green door",
"price": 12.50,
"tags": ["home", "green"]
}';
SELECT * FROM OPENJSON(@json);
->
key value type
id 1 2
name A green door 1
price 12.50 2
tags ["home", "green"] 4
FOR JSON
- Formatting query results as JSON
EX
SELECT object_id, name
FROM sys.tables
FOR JSON AUTO
->
[{"object_id":117575457,"name":"spt_fallback_db"},{"object_id":133575514,"name":"spt_fallback_dev"},{"object_id":149575571,"name":"spt_fallback_usg"},{"object_id":1483152329,"name":"spt_monitor"},{"object_id":1787153412,"name":"MSreplication_options"}]
ISJSON
- Test whether a text string is correctly formatted JSON
EX
DECLARE @json NVARCHAR(MAX) = N'
{
"id": 1,
"name": "A green door",
"price": 12.50,
"tags": ["home", "green"]
}';
SELECT CASE
WHEN ISJSON(@json) > 0
THEN 'The variable value is JSON.'
ELSE 'The variable value is not JSON.'
END;
->
The variable value is JSON.
JSON_VALUE
- Extract a scalar value from a JSON snippet
EX
;WITH CTE AS(
SELECT (
SELECT *
FROM sys.tables
FOR JSON AUTO
) as Result
)
SELECT JSON_VALUE(CTE.Result,'$[0].name') FROM CTE
->
spt_fallback_db
JSON_QUERY
- Extract an object or array from a JSON snippet
EX
;WITH CTE AS(
SELECT (
SELECT *
FROM sys.tables
FOR JSON AUTO
) as Result
)
SELECT JSON_QUERY(CTE.Result,'$[1]') FROM CTE
->
{"name":"spt_fallback_dev","object_id…}
JSON_MODIFY
- Updates the value of a property in a JSON string and returns the updated JSON string.
EX
DECLARE @json NVARCHAR(MAX) = N'
{
"id": 1,
"name": "A green door",
"price": 12.50,
"tags": ["home", "green"]
}';
SELECT JSON_MODIFY(@json, '$.price', '13.33')
->
{"id": 1, "name": "A green door", "price": "13.33", "tags": ["home", "green"] }
JSON Indexing
- You can index JSON columns the same way as all other types of columns in a table. You can use either standard non / clustered or full-text index. However, is that if you define the column as nvarchar (MAX) - What you usually want for JSON data - and want to use it as an index key column, it is not possible to index! This is due to SQL Server 2016 and Azure SQL Database have a maximum size for index keys for nonclustered indexes of 1700 bytes and the maximum key size for clustered indexes is 900 bytes. You can still use the include column option but then column is not searchable!
But there is a work around! Create a non persisted computed column using the JSON_VALUE function and index the column, BUT the solution does not work with arrays.
EX
DROP TABLE IF EXISTS [dbo].[KeyValueStore];
CREATE TABLE [dbo].[KeyValueStore](
[Key] [int] IDENTITY(1,1) NOT NULL,
[JsonValue] [nvarchar](MAX) NOT NULL
);
INSERT INTO [dbo].[KeyValueStore]([JsonValue]) VALUES('{
"id": 1,
"first_name": "Kathleen",
"last_name": "Russell",
"email": krussell0@etsy.com,
"gender": "Female",
"ip_address": "29.127.218.223"
}'),('{
"id": 2,
"first_name": "Jeffrey",
"last_name": "Russell",
"email": jrussell1@dot.gov,
"gender": "Male",
"ip_address": "48.129.254.196"
}'),('{
"id": 3,
"first_name": "Sarah",
"last_name": "Thompson",
"email": sthompson2@ebay.com,
"gender": "Female",
"ip_address": "59.123.56.234"
}');
--Before the index is created we can only list result by for example a like search
SELECT COUNT(*) FROM [dbo].[KeyValueStore]
WHERE [JsonValue] LIKE '%Russell%';
->
2
The Execution plan is an index scan
ALTER TABLE [dbo].[KeyValueStore]
ADD last_name AS JSON_VALUE([JsonValue], '$.last_name');
CREATE NONCLUSTERED INDEX [IX_last_name] ON [dbo].[KeyValueStore]
(
last_name ASC
);
--Now we can use the new computed persisted column with the index on
SELECT COUNT(*) FROM [dbo].[KeyValueStore]
WHERE last_name='Russell';
->
2
The Execution plan is an index Seek
JSON Datatype missing and Indexing JSON is clumsily
Missing in the SQL Server 2016 implementation of JSON is the fact that it includes no JSON-specific native data type and consequently none of the kinds of methods available to the for example XML data type. SQL Server 2016 continues to use the NVARCHAR type to store JSON data. This one of my biggest disappointment and after working with it still feels incomplete. The workaround to add a constraint with the ISJSON function to make sure our data is of the right type, but it still fell like a patch and when you think about indexing JSON you KNOW it’s a patch. To add an extra non persisted column in the table for each index you need is not good.
Conclusion
The end result is that SQL Server 2016 has reduced the NoSQL JSON “advantage” over SQL Server.
Plea for help
Our only option when faced with these problems is to partition and plea Microsoft fixing these JSON issues ASAP and here is the way todo it:
