{"id":82472,"date":"2018-12-17T18:07:41","date_gmt":"2018-12-17T18:07:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=82472"},"modified":"2019-01-22T16:24:23","modified_gmt":"2019-01-22T16:24:23","slug":"constraining-and-checking-json-data-in-sql-server-tables","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/constraining-and-checking-json-data-in-sql-server-tables\/","title":{"rendered":"Constraining and checking JSON Data in SQL Server Tables"},"content":{"rendered":"

So you have a database with JSON in it. Can you validate it? I don\u2019t mean just to ensure that it is valid JSON, but ensure that the JSON contains values that are legitimate. Are NI values, postcodes or bank codes valid? Can the dates or GUIDs be successfully parsed? Are those integers really integers? Are any of those dates of birth possible for a person who could be alive today? Are those part numbers valid?<\/p>\n

The short answer is that if the JSON document represents a list or a table, then it is easy: But if it is a complex hierarchy, you are better off doing it in PowerShell using JSON Schema. In this article we\u2019ll be showing how it is possible to use a mixture of ordinary constraints and table variables to achieve clean data.<\/p>\n

Document columns<\/h2>\n

In relational databases, each column holds an \u2018atomic\u2019 value, meaning that it isn\u2019t a collection of data items such as a list of values. A geographic location may considered to be a collection of coordinate values but from the data perspective, it is just a location: nobody is ordinarily interested in latitude or longitude or anything else, so from the perspective of the database users, the geographic location can be treated as \u2018atomic\u2019. You check it as such.<\/p>\n

Putting a data document into a column is a slippery slope. By \u2018data document\u2019, I\u2019m referring to a structured string from which more than one data element can be stored or extracted. A list is a simple example. An XML document or fragment is another one, and so is JSON. Whereas, with an ordinary relation column you can enforce constraints on what can get stored there, or on an XML document with an XML schema, this isn\u2019t so with a JSON document or a list. If circumstances force you to store JSON Data in a table and it is not \u2018atomic\u2019, it is your responsibility to check that the data within it is clean. In other words, If you have to store such a thing, then you have to know what sort of data is in there, and must prevent other data being stored in there. You must, in fact, \u2018constrain\u2019 the data.<\/p>\n

Why the need to constrain values?<\/h3>\n

Why? This is because the data is very easily presented in a relational format in views. There is no \u2018chinese wall\u2019 between JSON Data and your well-checked and disciplined relational data. It is there and it might be wrong. If you fail to enforce the rules that underlie any datatype, then you can get errors in all sorts of places, including, heaven help us, financial reporting. To take a silly example, if someone slips a new weekday into your innocuous list of days of the week, your daily revenues will be wrong. An inadvertent negative value in revenue figures can cause executives to run up and down corridors shouting. It is not only financial data that has to be checked. If your organisation gets a request to remove someone\u2019s data, you can remove their record and the associated rows in the associated tables. How do you know if their data hasn\u2019t leaked into other tables? Maybe you have a data document that is saved whenever an employee interacts with that customer? What if you decide to mask your data for development work and it turns out that a customer can be identified from a data document containing \u2018associated details\u2019 in an address table.<\/p>\n

The managers of any organisation are legally obliged to know where personal data is stored, and that it is held responsibly and securely. They must allow customers, members, clients or whatever to inspect, amend and have it deleted. The organisation relies on you to ensure that this corresponds to reality.<\/p>\n

Constraints in databases aren\u2019t a luxury. Your job, and the health of the organisation that employs you, depends on ensuring that your date is as clean as automation can manage.<\/p>\n

Validating a data-document column<\/h2>\n

Leaving JSON to one side for a moment, let\u2019s take the simplest sort of data document: a list.<\/p>\n

Let\u2019s imagine that you have a list of postcodes. You want to insert it in the table to represent a delivery route for the route of a carrier\u2019s van. (we leave out all the other columns because they\u2019re unlikely to be relevant.)<\/p>\n

CREATE TABLE deliveryRoutes (\r\n  routeid INT IDENTITY,\r\n  Driverid int,\r\n  DriverRoute varchar(2000))\r\n;\r\n<\/pre>\n
You can check a postcode such as 'CB4 0WZ'<\/code> like this<\/p>\n
SELECT \r\n  CASE \r\n   WHEN 'CB4 0WZ' LIKE '[A-Z][A-Z0-9] [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n     OR 'CB4 0WZ' LIKE '[A-Z][A-Z0-9]_ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n     OR 'CB4 0WZ' LIKE '[A-Z][A-Z0-9]__ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n   THEN 1 ELSE 0 END;\r\n<\/pre>\n
You can create a function to test a list of these postcodes like this:<\/p>\n
Select dbo.IsValidPostcodelist (\r\n  'SK1 3AU,BN27 3D,BT7 3GQ,NR1 3SR,SE9 5LB,PO4 0PX,BT78 5LU,W1F 7JR,B46 2QP,BL8 4AA,NR2 4AZ,CM 96G')<\/pre>\n
Here is the code. I\u2019m using the new string_split() function but you can do one of the older techniques<\/p>\n
CREATE FUNCTION [dbo].[IsValidPostcodeList]\r\n\/**\r\nSummary: >\r\n  this routine takes a list of postcodes, comma-delimited and rerturns 0 if any \r\n  of them is invalid. It returns 1 if the entire list of postcodes is valid\r\nAuthor: phil factor\r\nDate: 14\/12\/2018\r\nExamples:\r\n   - Select dbo.IsValidPostcodelist ('SK1 3AU,BN27 3D,BT7 3GQ,NR1 3SR,SE9 5LB,PO4 0PX,BT78 5LU,W1F 7JR,B46 2QP,BL8 4AA,NR2 4AZ,CM 96G')\r\n   - Select dbo.IsValidPostcodelist ('SK1 3AU,BT7 3GQ,NR1 3SR,SE9 5LB,PO4 0PX,BT78 5LU,W1F 7JR,B46 2QP,BL8 4AA,NR2 4AZ')\r\nReturns: >\r\n  an integer returns 1 if the entire list of postcodes is valid\r\n**\/\r\n(\r\n    @postcodes Varchar(2000)\r\n)\r\nRETURNS INT\r\nAS\r\nBEGIN\r\nIF EXISTS( SELECT * FROM STRING_SPLIT(@postcodes, ',')  \r\nWHERE CASE WHEN value LIKE '[A-Z][A-Z0-9] [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n             OR value  LIKE '[A-Z][A-Z0-9]_ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n             OR value  LIKE '[A-Z][A-Z0-9]__ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n\t       THEN 1 ELSE 0 END=0)\r\n   RETURN 0\r\nRETURN 1\r\nEND\r\n<\/pre>\n
With this function, we can now add a constraint to our table that checks the postcode. If you insert a drivers route that contains an invalid postcode, you get an error. We try it out.<\/p>\n
\/* create a test table that uses this constraint *\/\r\nIF Object_Id('dbo.deliveryRoutes') IS NOT NULL\r\n\tDROP TABLE deliveryRoutes\r\nCREATE TABLE deliveryRoutes (\r\n  routeid INt IDENTITY,\r\n  Driverid int,\r\n  DriverRoute varchar(2000) \r\n  CONSTRAINT for_Valid_Postcodes_In_List CHECK (dbo.IsValidPostcodelist(DriverRoute)=1),\r\n  TheDate Date DEFAULT getdate());\r\n\r\n-- this won't go in because a couple of these    \r\nINSERT INTO DeliveryRoutes (Driverid, driverroute) \r\n SELECT 354,'SK1 3AU,BN27 3D,BT7 3GQ,NR1 3SR,SE9 5LB,PO4 0PX,BT78 5LU,W1F 7JR,B46 2QP,BL8 4AA,NR2 4AZ,CM 96G'\r\n\/*\r\nMsg 547, Level 16, State 0, Line 78\r\nThe INSERT statement conflicted with the CHECK constraint \"for_Valid_Postcodes_In_List\". \r\nThe conflict occurred in database \"business\", table \"dbo.deliveryRoutes\", column 'DriverRoute'.\r\nThe statement has been terminated.\r\n*\/\r\nINSERT INTO DeliveryRoutes (Driverid, driverroute) \r\n SELECT 354,'SK1 3AU,BT7 3GQ,NR1 3SR,SE9 5LB,PO4 0PX,BT78 5LU,W1F 7JR,B46 2QP,BL8 4AA,NR2 4AZ'\r\n--that second one worked well\r\n<\/pre>\n
Try these out, altering the postcodes slightly and you\u2019ll see when one or more becomes invalid.<\/p>\n
Now, With this function, we can check the list to make sure that every member of the list is a valid postcode.<\/p>\n
What if your developers are storing such a thing in a JSON string rather than a list? Doing this is probably better as a JSON String because you can store null values in it and the parsing is quicker.<\/p>\n
We can check it just as easily. Firstly we create the function to do it.<\/p>\n
CREATE OR ALTER FUNCTION [dbo].[IsValidPostcodeJSONList]\r\n\/**\r\nSummary: >\r\n  this routine takes a JSON list of postcodes, and rerturns 0 if any \r\n  of them is invalid. It returns 1 if the entire list of postcodes is valid\r\nAuthor: phil factor\r\nDate: 14\/12\/2018\r\nExamples:\r\n   - Select dbo.IsValidPostcodeJSONlist ('\r\n      [\"SK1 3AU\",\"BN27 3D\",\"BT7 3GQ\",\"NR1 3SR\",\"SE9 5LB\",\"PO4 0PX\",\r\n      \"BT78 5LU\",\"W1F 7JR\",\"B46 2QP\",\"BL8 4AA\",\"NR2 4AZ\",\"CM 96G\"]')\r\n   - Select dbo.IsValidPostcodeJSONlist ('\r\n      [\"SK1 3AU\",\"BT7 3GQ\",\"NR1 3SR\",\"SE9 5LB\",\"PO4 0PX\",\"BT78 5LU\",\r\n      \"W1F 7JR\",\"B46 2QP\",\"BL8 4AA\",\"NR2 4AZ\"]')\r\nReturns: >\r\n  an integer returns 1 if the entire list of postcodes is valid\r\n**\/\r\n(\r\n    @postcodes Varchar(2000)\r\n)\r\nRETURNS INT\r\nAS\r\nBEGIN\r\n\r\nIF IsJson(@postcodes)=0 return -1\r\nIF EXISTS (SELECT * FROM OPENJSON(@postcodes)\r\n      WHERE CASE WHEN value LIKE '[A-Z][A-Z0-9] [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n             OR value  LIKE '[A-Z][A-Z0-9]_ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n             OR value  LIKE '[A-Z][A-Z0-9]__ [0-9][ABD-HJLNP-UW-Z][ABD-HJLNP-UW-Z]'\r\n\t       THEN 1 ELSE 0 END=0)return 0\r\nRETURN 1\r\nEND\r\n\r\n<\/pre>\n
Now we have the function, we can use this in an almost identical table<\/p>\n
\/* create a test table that uses this constraint *\/\r\nIF Object_Id('dbo.deliveryRoutesJSON') IS NOT NULL\r\n\tDROP TABLE deliveryRoutesJSON\r\n\r\nCREATE TABLE deliveryRoutesJSON (\r\n  routeid INt IDENTITY,\r\n  Driverid int,\r\n  DriverRoute NVARCHAR(MAX) \r\n  CONSTRAINT for_Valid_JSON_Postcodes_In_List CHECK (dbo.IsValidPostcodeJSONList(DriverRoute)=1),\r\n  TheDate Date DEFAULT getdate());\r\n\r\nINSERT INTO DeliveryRoutesJSON (Driverid, driverroute) \r\n SELECT 354,'[\"SK1 3AU\",\"BT7 3GQ\",\"NR1 3SR\",\"SE9 5LB\",\"PO4 0PX\",\"BT78 5LU\",\r\n      \"W1F 7JR\",\"B46 2QP\",\"BL8 4AA\",\"NR2 4AZ\"]'\r\n --this first one worked well\r\n \r\n -- this won't go in because a couple of these are wrong   \r\nINSERT INTO DeliveryRoutesJSON (Driverid, driverroute) \r\n SELECT 354,'[\"SK1 3AU\",\"BN27 3D\",\"BT7 3GQ\",\"NR1 3SR\",\"SE9 5LB\",\"PO4 0PX\",\r\n      \"BT78 5LU\",\"W1F 7JR\",\"B46 2QP\",\"BL8 4AA\",\"NR2 4AZ\",\"CM 96G\"]'\r\n\/*\r\nMsg 547, Level 16, State 0, Line 108\r\nThe INSERT statement conflicted with the CHECK constraint \"for_Valid_JSON_Postcodes_In_List\".\r\nThe conflict occurred in database \"business\", table \"dbo.deliveryRoutesJSON\", column 'DriverRoute'.\r\nThe statement has been terminated.\r\n*\/\r\n<\/pre>\n
All we are doing is to iterate through the values checking each in turn<\/p>\n
We\u2019re still coping as we get to more complex examples than a simple list. However, the JSON could be anything. If it is representing tabular data, then it is easy.<\/p>\n
Imagine we have to validate the JSON before we insert it. Imagine, too, that you have JSON consisting of a name, a birthdate, a guid and a modification-date,<\/p>\n
[{\"SalesPerson\":\"Suroor R Fatima\",\"birthdate\":\"1978-02-25\",\"Gender\":\"M\",\"rowguid\":\"14010B0E-C101-4E41-B788-21923399E512\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"John T Campbell\",\"birthdate\":\"1956-08-07\",\"Gender\":\"M\",\"rowguid\":\"D4ED1F78-7C28-479B-BFEF-A73228BA2AAA\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Lori A Kane\",\"birthdate\":\"1980-07-18\",\"Gender\":\"F\",\"rowguid\":\"23D436FC-08F7-4988-8B4D-490AA4E8B7E7\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Karen A Berg\",\"birthdate\":\"1978-05-19\",\"Gender\":\"F\",\"rowguid\":\"45C3D0F5-3332-419D-AD40-A98996BB5531\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Alice O Ciccu\",\"birthdate\":\"1978-01-26\",\"Gender\":\"F\",\"rowguid\":\"7E632B21-0D11-4BBA-8A68-8CAE14C20AE6\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Sandeep P Kaliyath\",\"birthdate\":\"1970-12-03\",\"Gender\":\"M\",\"rowguid\":\"606C21E2-3EC0-48A6-A9FE-6BC8123AC786\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"David M Bradley\",\"birthdate\":\"1975-03-19\",\"Gender\":\"M\",\"rowguid\":\"E87029AA-2CBA-4C03-B948-D83AF0313E28\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Don L Hall\",\"birthdate\":\"1971-06-13\",\"Gender\":\"M\",\"rowguid\":\"E720053D-922E-4C91-B81A-A1CA4EF8BB0E\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Tom M Vande Velde\",\"birthdate\":\"1986-10-01\",\"Gender\":\"M\",\"rowguid\":\"B3BF7FC5-2014-48CE-B7BB-76124FA8446C\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Tengiz N Kharatishvili\",\"birthdate\":\"1990-04-28\",\"Gender\":\"M\",\"rowguid\":\"C609B3B2-7969-410C-934C-62C34B63C4EE\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Mikael Q Sandberg\",\"birthdate\":\"1984-08-17\",\"Gender\":\"M\",\"rowguid\":\"D0FD55FF-42FA-491E-8B3B-AB3316018909\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Ben T Miller\",\"birthdate\":\"1973-06-03\",\"Gender\":\"M\",\"rowguid\":\"B9641CAE-765C-4662-B760-C167A1F2B8B5\",\"ModifiedDate\":\"2014-06-30T00:00:00\"}]<\/pre>\n
We can check this lot out easily enough, though the code might raise a few eyebrows. First we create a function. It starts by checking, just as the previous one did, for a valid JSON Document. Then it makes sure that there are the correct number of key\/value pairs in each object of the array, to correspond with the number of columns in a relational table. It\u00a0 then creates a table variable with all the SQL Server constraints, including NULL\/NOT NULL in it that you would apply if the JSON table were a relational table, along with the correct SQL Server datatype. If one of these fire, then the error will appear at the point at which the JSON was inserted into the table. Also, if the JSON couldn\u2019t be coerced into that datatype the function will once again \u2018error-out\u2019 and abort the data insertion. Finally, the table variable runs the constraints. That insertion will then \u2018error out\u2019 and cause the insertion into the table to fail.<\/p>\n
In effect, you\u2019ve done a dress rehearsal of inserting that data into a relational table, with all the constraints that are available to you.<\/p>\n
CREATE OR ALTER FUNCTION [dbo].[IsSalesPeopleJSONValid]\r\n\/**\r\nSummary: >\r\n  this routine takes a JSON document of sales peole, and returns 0 if any \r\n  of them is invalid. It returns 1 if the entire document is valid\r\nAuthor: phil factor\r\nDate: 14\/12\/2018\r\nExamples:\r\n   - Select dbo.IsSalesPeopleJSONValid('\r\n     [{\"SalesPerson\":\"Suroor R Fatima\",\"birthdate\":\"1978-02-25\",\r\n\t \"Gender\":\"M\",\"rowguid\":\"14010B0E-C101-4E41-B788-21923399E512\",\r\n\t \"ModifiedDate\":\"2014-06-30T00:00:00\"}]')\r\n   - Select dbo.IsSalesPeopleJSONValid('\r\n     [{\"SalesPerson\":\"Suroor R Fatima\",\"birthdate\":\"1878-02-25\",\r\n\t \"Gender\":\"M\",\"rowguid\":\"14010B0E-C101-4E41-B788-21923399E512\",\r\n\t \"ModifiedDate\":\"2014-06-30T00:00:00\"}]')\r\nReturns: >\r\n  an integer returns 1 if the json document of sales people  is valid\r\n**\/\r\n(\r\n    @SalesPeopleJSON NVARCHAR(MAX)\r\n)\r\nRETURNS INT\r\nAS\r\nBEGIN\r\nIF IsJson(@SalesPeopleJSON)=0 return -1 --is this valid JSON?\r\n--Does it have extra key value pairs squirelled away anywhere?\r\nIF EXISTS (SELECT Count(*), doc.[key] FROM OpenJson(@SalesPeopleJSON) doc\r\nCROSS APPLY OpenJson([value] ) row\r\nGROUP BY doc.[key]\r\nHAVING Count(*)<>5) RETURN 0 --go to be just one per column, fivwe in all\r\n\r\nDECLARE @SalesPeople TABLE ( --create a table variable with check constraints\r\n\tSalesPerson NVARCHAR(40) NOT NULL,\r\n\tBirthDate DATE NOT NULL  CHECK (Year(BirthDate) BETWEEN 1900 AND YEAR(GETDATE())-16),\r\n\tGender  CHAR(1) NOT null CHECK  ((Gender LIKE '[MFZmfz]')),\r\n\tRowGUID  UNIQUEIDENTIFIER NOT null,\r\n\tModifiedDate DATETIME2 NOT null)\r\n-- now insert a tabular version of the JSON document into the table variable\r\nINSERT INTO @SalesPeople(SalesPerson,BirthDate, Gender, Rowguid, ModifiedDate)\r\nSELECT SalesPerson,BirthDate, Gender,RowGUID,ModifiedDate from OPENJSON(@SalesPeopleJSON)\r\nWITH( SalesPerson NVARCHAR(40),birthdate date, Gender CHAR(1),rowguid UNIQUEIDENTIFIER, ModifiedDate Datetime2)\r\n--if you got here without an error you've succeeded\r\nRETURN 1\r\nEND\r\n<\/pre>\n
With this function, we can now insert JSON data into the table without cringing, because it will all be checked. It is now up to us to get those constraint definitions right! First we create the test table. We\u2019ll leave out everything except that which is relevant to the demonstration.<\/p>\n
\/* create a test table that uses this constraint *\/\r\nIF Object_Id('Notes') IS NOT NULL\r\n\tDROP TABLE Notes\r\nCREATE TABLE  Notes (\r\n   Note_id INt IDENTITY,\r\n  Customer INT NOT null,\r\n  Note NVARCHAR(MAX),\r\n  SalesPeopleInvolved NVARCHAR(MAX)\r\n  CONSTRAINT for_Valid_SalesPeopleList CHECK (dbo.IsSalesPeopleJSONValid(SalesPeopleInvolved)=1),\r\n  TheDate Date NULL DEFAULT getdate());<\/pre>\n
Now we insert a row<\/p>\n
INSERT INTO Notes(customer,Note,SalesPeopleInvolved)\r\nSELECT 354, 'Did not reply to our second payment request',\r\n'[{\"SalesPerson\":\"John T Campbell\",\"birthdate\":\"1956-08-07\",\r\n\"Gender\":\"M\",\"rowguid\":\"D4ED1F78-7C28-479B-BFEF-A73228BA2AAA\",\r\n\"ModifiedDate\":\"2014-06-30T00:00:00\"}]'\r\n<\/pre>\n
OK. That went well<\/p>\n
INSERT INTO Notes(customer,Note,SalesPeopleInvolved)\r\nSELECT 3234, 'Now happy with our product',\r\n'[{\"SalesPerson\":\"Ben T Miller\",\"birthdate\":\"1973-06-03\",\r\n\"Gender\":\"B\",\"rowguid\":\"B9641CAE-765C-4662-B760-C167A1F2B8B5\",\r\n\"ModifiedDate\":\"2014-06-30T00:00:00\"}]'\r\n<\/pre>\n
Oooh! An error! It has spotted that Ben Miller has a gender (__Gende__<\/code>) of \u2018B\u2019<\/p>\n
\/*\r\nMsg 547, Level 16, State 0, Line 229\r\nThe INSERT statement conflicted with the CHECK constraint \"CK__#A23D3581__Gende__A4257DF3\". \r\nThe conflict occurred in database \"tempdb\", table \"@SalesPeople\".\r\n*\/\r\n<\/pre>\n
So lets try again, but getting the gender right but the GUID (Uniqueidentifier) wrong<\/p>\n
INSERT INTO Notes(customer,Note,SalesPeopleInvolved)\r\nSELECT 3234, 'Now happy with our product',\r\n'[{\"SalesPerson\":\"Ben T Miller\",\"birthdate\":\"1973-06-03\",\r\n\"Gender\":\"M\",\"rowguid\":\"B964-765C-4662-B760-C167A1F2B8B5\",\r\n\"ModifiedDate\":\"2014-06-30T00:00:00\"}]'\r\n\r\n\/* Msg 8169, Level 16, State 2, Line 235\r\nConversion failed when converting from a character string to uniqueidentifier.*\/<\/pre>\n
Right. Now we get a larger JSON document into the table<\/p>\n
INSERT INTO Notes(customer,Note,SalesPeopleInvolved)\r\nSELECT 334, 'This customer has phoned the entire office up and reduced them to tears',\r\n'[{\"SalesPerson\":\"Suroor R Fatima\",\"birthdate\":\"1976-02-25\",\"Gender\":\"Z\",\"rowguid\":\"14010B0E-C101-4E41-B788-21923399E512\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"John T Campbell\",\"birthdate\":\"1956-08-07\",\"Gender\":\"M\",\"rowguid\":\"D4ED1F78-7C28-479B-BFEF-A73228BA2AAA\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Lori A Kane\",\"birthdate\":\"1980-07-18\",\"Gender\":\"F\",\"rowguid\":\"23D436FC-08F7-4988-8B4D-490AA4E8B7E7\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Karen A Berg\",\"birthdate\":\"1978-05-19\",\"Gender\":\"F\",\"rowguid\":\"45C3D0F5-3332-419D-AD40-A98996BB5531\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Alice O Ciccu\",\"birthdate\":\"1978-01-26\",\"Gender\":\"F\",\"rowguid\":\"7E632B21-0D11-4BBA-8A68-8CAE14C20AE6\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Sandeep P Kaliyath\",\"birthdate\":\"1970-12-03\",\"Gender\":\"M\",\"rowguid\":\"606C21E2-3EC0-48A6-A9FE-6BC8123AC786\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"David M Bradley\",\"birthdate\":\"1975-03-19\",\"Gender\":\"M\",\"rowguid\":\"E87029AA-2CBA-4C03-B948-D83AF0313E28\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Don L Hall\",\"birthdate\":\"1971-06-13\",\"Gender\":\"M\",\"rowguid\":\"E720053D-922E-4C91-B81A-A1CA4EF8BB0E\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Tom M Vande Velde\",\"birthdate\":\"1986-10-01\",\"Gender\":\"M\",\"rowguid\":\"B3BF7FC5-2014-48CE-B7BB-76124FA8446C\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Tengiz N Kharatishvili\",\"birthdate\":\"1990-04-28\",\"Gender\":\"M\",\"rowguid\":\"C609B3B2-7969-410C-934C-62C34B63C4EE\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Mikael Q Sandberg\",\"birthdate\":\"1984-08-17\",\"Gender\":\"M\",\"rowguid\":\"D0FD55FF-42FA-491E-8B3B-AB3316018909\",\"ModifiedDate\":\"2014-06-30T00:00:00\"},{\"SalesPerson\":\"Ben T Miller\",\"birthdate\":\"1973-06-03\",\"Gender\":\"M\",\"rowguid\":\"B9641CAE-765C-4662-B760-C167A1F2B8B5\",\"ModifiedDate\":\"2014-06-30T00:00:00\"}]'\r\n<\/pre>\n
Good. All went well.<\/p>\n
Without that test in the function to make sure there were just five key\/value pairs in each document, someone could slip extra data into the database. This could perhaps be data that should never go in there such as customer personal data. We have prevented this from happening<\/p>\n
INSERT INTO Notes(customer,Note,SalesPeopleInvolved)\r\nSELECT 3234, 'Now happy with our product',\r\n'[{\"SalesPerson\":\"Ben T Miller\",\"birthdate\":\"1973-06-03\",\r\n\"Gender\":\"M\",\"rowguid\":\"B9641CAE-765C-4662-B760-C167A1F2B8B5\",\r\n\"credit card\":\"4657-6758-4538-0987\", \"ModifiedDate\":\"2014-06-30T00:00:00\"}]'<\/pre>\n
…which gives the result \u2026<\/p>\n
\/*\r\nMsg 547, Level 16, State 0, Line 298\r\nThe INSERT statement conflicted with the CHECK constraint \"for_Valid_SalesPeopleList\". The conflict occurred in database \"business\", table \"dbo.Notes\", column 'SalesPeopleInvolved'.\r\nThe statement has been terminated.\r\n*\/<\/pre>\n
Conclusion<\/h2>\n
As long as the JSON document being stored in a table is tabular in nature rather than hierarchical, we can deal with it in SQL Server. An array-in-array JSON document needs just light editing of the OPENJSON function call to access array elements rather than specifying the key. I\u2019ve explained how to do this in another article.<\/p>\n
There are a lot of advantages in using the weapons you know, and have to hand, when dealing with bad data. Constraints and coercion are good simple ways of ensuring that the data is correct.<\/p>\n
If the JSON is hierarchical, then we are generally forced to deal with it by checking against the JSON Schema. I do this via PowerShell, so it can\u2019t be done at the point of insertion. It also requires the developers to be organized enough to provide you an up-to-date JSON Schema. I\u2019ve explained in another article how one can open up a hierarchical JSON document and investigate the values. This method can be used if you need to keep the checks \u2018in-house\u2019, but it is slow to debug and will need to be maintained if the JSON Schema changes as a development process.<\/p>\n","protected":false},"excerpt":{"rendered":"
So you have a database with JSON in it. Can you validate it? I don\u2019t mean just to ensure that it is valid JSON, but ensure that the JSON contains values that are legitimate. Are NI values, postcodes or bank codes valid? Can the dates or GUIDs be successfully parsed? Are those integers really integers?……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5134],"coauthors":[6813],"class_list":["post-82472","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82472","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82472"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82472\/revisions"}],"predecessor-version":[{"id":82573,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82472\/revisions\/82573"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82472"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82472"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82472"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}