The JSON has landed (in beta at least) – and I think it’s now some practical use. I’ve collected a few technical references here. There may not be a native JSON type yet, but there are workarounds to provide the kind of assurance control-freak RDBMS developers find comforting. I’ve shown some of them in the code sample below.
You may have to store your JSON as an nvarchar(max), but you can ensure that it is valid JSON with a column constraint using the ISJSON function (see the NameProperties column in the script).
Indexing
You can even index on a JSON property by creating a computed column to extract a property value from the JSON. If you persist the column (as in the CREATE TABLE below) you’ve taken all the overhead at INSERT time at the price of a little extra storage. A persisted computed column can also be defined as NOT NULL. If the JSON doesn’t contain the property you need for the computed column, the INSERT will fail.
Application Design
Of course, this could swiftly turn into “MAN+HAMMER, WORLD=NAIL”. JSON is useful because it allows the application more flexibility as to what it saves. JSON in Sql may allow that flexibility to be combined with the flexibility at query time that an RDBMS offers. It shouldn’t be treated as an opportunity to drag application logic back into the data layer against the will of the application programmers.
It’s not another nail in the coffin of NoSql, either. Some data is more cheaply and naturally housed in a NoSQL database. This isn’t a way of dragging all that data back into an expensive and resource-intensive RDBMS. So take some of the ideas below as options, not even suggestions. Sorry the code doesn’t look very nice here, when you copy it into SSMS the formatting should improve.
/*
--drop all changes made in this script--
DROP FULLTEXT INDEX ON Contact
DROP FULLTEXT CATALOG ftc
DROP TABLE [dbo].[Contact]
*/
--comment out if you don't create a new test db--
USE TestDb
go
SELECT @@VERSION --ran on Microsoft SQL Server 2016 (CTP3.2) - 13.0.900.73 (X64) Dec 10 2015 18:49:31 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )
SET NOCOUNT ON
SET ANSI_NULLS ON;
go
SET QUOTED_IDENTIFIER ON
go
SET ANSI_PADDING ON
go
CREATE TABLE [dbo].[Contact](
[Id] [int] IDENTITY(1,1) NOT NULL,
[NameProperties] [nvarchar](max) NOT NULL
--ensure only valid JSON stored--
CONSTRAINT CHK_CONNamePropertiesIsJSON CHECK(ISJSON(NameProperties) = 1),
--NameKey provides main lookup: is persisted and NOT NULL so NameProperties has to contain a nameKey value--
[NameKey] AS (JSON_VALUE([NameProperties],'$.nameKey')) PERSISTED NOT NULL,
[Names] AS (JSON_QUERY([NameProperties],'$.names')),
--non-clustered index as NameKey will be indexed for lookup
CONSTRAINT [PK_CON] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go
CREATE UNIQUE CLUSTERED INDEX [IX_CONameKey_NUQ] ON [dbo].[Contact]
(
[NameKey] ASC,
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
go
/*
You'll receive this warning here because Sql Server is unable to evaluate length of computed column source; size of data passed in will be controlled by application
--Warning! The maximum key length for a clustered index is 900 bytes. The index 'IX_CONameKey_NUQ' has maximum length of 8004 bytes. For some combination of large values, the insert/update operation will fail.
*/
DECLARE @JsonName varchar(max) =
'{
"titlePrefix" : "Atty.",
"names" : ["Guiseppi", "Sapone"],
"nameKey" : "Sapone",
"titleSuffix" : "MDiv",
"nameSuffix": null
}'
,@JsonName1 varchar(max) =
'{
"titlePrefix" : "Mr.",
"names" : ["Joseph", "Sapone"],
"nameKey" : "Sapone",
"titleSuffix" : null,
"nameSuffix": null
}'
,@JsonName2 varchar(max) =
'{
"titlePrefix" : "Rev.",
"names" : ["Joseph", "Sabon"],
"nameKey" : "Sabon",
"titleSuffix" : null,
"nameSuffix": null
}'
INSERT INTO Contact(NameProperties) VALUES(@JsonName),(@JsonName1), (@JsonName2)
--try to insert JSON without required nameKey property required for NameKey computed column--
DECLARE @JsonNameBAD varchar(max) =
'{
"titlePrefix" : "Atty.",
"names" : ["Guiseppi", "Sapone"],
"titleSuffix" : "MDiv",
"nameSuffix": null
}'
BEGIN TRY
INSERT INTO Contact(NameProperties) VALUES(@JsonNameBAD)
END TRY
BEGIN CATCH
PRINT 'ERROR NUMBER' + ' ' + CAST(ERROR_NUMBER() as varchar(12)) + ':' + ' ' + ERROR_MESSAGE()
END CATCH
/*
Msg 515, Level 16, State 2, Line 91
Cannot insert the value NULL into column 'NameKey', table 'TestDb.dbo.Contact'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
--select table column/s as JSON--
SELECT names, NameKey FROM Contact FOR JSON PATH
--remove square bracket wrapper from JSON--
SELECT names, NameKey FROM Contact FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
--return multiple rows - will process JSON on client to find desired item
SELECT Names, NameKey FROM Contact WHERE NameKey = 'Sapone'
/*
full text indexing would be one way of server-side processing JSON array in computed column but look at performance and maintenance issues
*/
--set up full-text indexing
CREATE FULLTEXT CATALOG ftc;
go
CREATE FULLTEXT INDEX ON Contact(Names)
KEY INDEX PK_CON ON ftc
WITH STOPLIST = SYSTEM;
go
--server-side processing to filter for single row--
SELECT Names, NameKey FROM Contact WHERE NameKey = 'Sapone' And CONTAINS(names, 'Guiseppi')
blog.philipsymons.com 