{"id":81895,"date":"2018-12-05T14:19:35","date_gmt":"2018-12-05T14:19:35","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=81895"},"modified":"2019-01-21T23:04:35","modified_gmt":"2019-01-21T23:04:35","slug":"producing-data-and-schemas-in-json-array-of-array-format","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/producing-data-and-schemas-in-json-array-of-array-format\/","title":{"rendered":"Producing Data and Schemas in JSON array-of-array format."},"content":{"rendered":"

JSON was initially designed for the informal transfer of data that has no schema. It has no concept of a table, or of an array of identical arrays. This means that it must tell you each key for each object, even if the original data object was a table. With the happy minimum of key-value pairs, the JSON document that is produced will produce tabular data that tells you the column to which the data object belongs, and it will do a reasonable job of implying a data type. Sadly, that just isn\u2019t enough for us because JSON only recognises just four base data types; string, number, Boolean and null. In SQL Server, we\u2019re used to a lot more than that. BSON and other extensions supplement this with functions that coerce the string into the correct datatype, but we don\u2019t need to do that because all of the data for any one column will be under the same constraints. In fact, there is a lot of redundant data in a JSON document produced by SQL Server that represents a table or result. Not only that, but there isn\u2019t enough of the right sort of metadata until we add a JSON Schema.<\/p>\n

CSV provides the most economical way of transferring tabular data as an ASCII file, but SQL Server doesn\u2019t always support it properly to the rfc4180 standard<\/a>. However, JSON can transfer tabular data in a way that is almost as economical in space, and more reliably. Here, just to convince you that it is compact, are the first three records of Adventureworks humanResources.employee table in array-of-array JSON.<\/p>\n

[[1,\"295847284\",\"adventure-works\\\\ken0\",null,null,\"Chief Executive Officer\",\"1969-01-29\",\"S\",\"M\",\"2009-01-14\",true,99,69,true,\"F01251E5-96A3-448D-981E-0F99D789110D\",\"2014-06-30T00:00:00\"], \r\n[2,\"245797967\",\"adventure-works\\\\terri0\",\"\\\/1\\\/\",1,\"Vice President of Engineering\",\"1971-08-01\",\"S\",\"F\",\"2008-01-31\",true,1,20,true,\"45E8F437-670D-4409-93CB-F9424A40D6EE\",\"2014-06-30T00:00:00\"], \r\n[11,\"974026903\",\"adventure-works\\\\ovidiu0\",\"\\\/1\\\/1\\\/5\\\/\",3,\"Senior Tool Designer\",\"1978-01-17\",\"S\",\"M\",\"2010-12-05\",false,7,23,true,\"F68C7C19-FAC1-438C-9BB7-AC33FCC341C3\",\"2014-06-30T00:00:00\"] \r\n] <\/pre>\n
It is valid JSON (RFC 4627). It isn\u2019t the usual way of storing table data, which is much more verbose, being an array of JSON objects of key-value pairs. We are no longer bound to do this the conventional way because we can now transfer the metadata with the data. The JSON Schema tells you how it is stored.<\/p>\n
JSON can be persuaded into this array-of-array format, and if we can read and write it in SQL Server, then we can use it. To prove this, we need to be able to save a database in this format, and to save the schema in this format.<\/p>\n
In this article, I\u2019ll demonstrate how to produce an array-in-array JSON document, and the schema to go with it. With this, you have sufficient information to make it easy to transfer such data.<\/p>\n
JSON Arrays to Relational Table<\/h2>\n
Before we turn to the task of producing this sort of data from a table or expression we ought to show to turn the specimen json document that I\u2019ve just shown you back into a relational table. We\u2019ll start by doing the task manually, without a schema, as if it were CSV. You might think I\u2019m being ironic when you first see the code that performs this action, but no. We can then go on to generate all the laborious stuff automatically:<\/p>\n
Let\u2019s shred our sample JSON. We have two alternatives. We can do it the JSON_Value way, referencing array elements within the row rather than key\/value pairs.<\/p>\n
DECLARE  @MyJSON NVARCHAR(MAX) ='[\r\n  [1,\"295847284\",\"adventure-works\\\\ken0\",null,null,\"Chief Executive Officer\",\"1969-01-29\",\"S\",\"M\",\"2009-01-14\",true,99,69,true,\"F01251E5-96A3-448D-981E-0F99D789110D\",\"2014-06-30T00:00:00\"],\r\n  [2,\"245797967\",\"adventure-works\\\\terri0\",\"\\\/1\\\/\",1,\"Vice President of Engineering\",\"1971-08-01\",\"S\",\"F\",\"2008-01-31\",true,1,20,true,\"45E8F437-670D-4409-93CB-F9424A40D6EE\",\"2014-06-30T00:00:00\"],\r\n  [11,\"974026903\",\"adventure-works\\\\ovidiu0\",\"\\\/1\\\/1\\\/5\\\/\",3,\"Senior Tool Designer\",\"1978-01-17\",\"S\",\"M\",\"2010-12-05\",false,7,23,true,\"F68C7C19-FAC1-438C-9BB7-AC33FCC341C3\",\"2014-06-30T00:00:00\"]\r\n]\r\n'\r\nSELECT \r\n  Convert(int,Json_Value(value, 'strict $[0]')) as [BusinessEntityID],\r\n  Convert(nvarchar(15),Json_Value(value, 'strict $[1]')) as [NationalIDNumber],\r\n  Convert(nvarchar(256),Json_Value(value, 'strict $[2]')) as [LoginID],\r\n  Convert(hierarchyid,Json_Value(value, 'strict $[3]')) as [OrganizationNode],\r\n  Convert(smallint,Json_Value(value, 'strict $[4]')) as [OrganizationLevel],\r\n  Convert(nvarchar(50),Json_Value(value, 'strict $[5]')) as [JobTitle],\r\n  Convert(date,Json_Value(value, 'strict $[6]')) as [BirthDate],\r\n  Convert(nchar(1),Json_Value(value, 'strict $[7]')) as [MaritalStatus],\r\n  Convert(nchar(1),Json_Value(value, 'strict $[8]')) as [Gender],\r\n  Convert(date,Json_Value(value, 'strict $[9]')) as [HireDate],\r\n  Convert(bit,Json_Value(value, 'strict $[10]')) as [SalariedFlag],\r\n  Convert(smallint,Json_Value(value, 'strict $[11]')) as [VacationHours],\r\n  Convert(smallint,Json_Value(value, 'strict $[12]')) as [SickLeaveHours],\r\n  Convert(bit,Json_Value(value, 'strict $[13]')) as [CurrentFlag],\r\n  Convert(uniqueidentifier,Json_Value(value, 'strict $[14]')) as [rowguid],\r\n  Convert(datetime,Json_Value(value, 'strict $[15]')) as [ModifiedDate]\r\nFROM OpenJson(@myJSON) AS lines;\r\nGo\r\n<\/pre>\n
 <\/p>\n
<\/p>\n
You can make sure that it is returning all the data in the correct format, even the pesky hierarchy ID, by doing a SELECT INTO. There is a big, big, problem here, though, that they don\u2019t warn you about. JSON_Value has a maximum of 4000 for a string.<\/p>\n
For data of any size, we will need to use the second alternative, OPENJSON. This has the rather neater Explicit Schema syntax. Note that, in our rendition of the explicit schema used by OpenJSON, we need to reference array elements rather than key\/value pairs<\/p>\n
SELECT BusinessEntityID, NationalIDNumber, LoginID, OrganizationLevel,\r\n  JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag,\r\n  VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate\r\n  FROM\r\n  OpenJson(@myJSON)\r\n  WITH\r\n    (\r\n    BusinessEntityID INT 'strict $[0]',\r\n    NationalIDNumber NVARCHAR(15) 'strict $[1]',\r\n    LoginID NVARCHAR(256) 'strict $[2]',\r\n    --[OrganizationNode] hierarchyid 'strict $[3]',\r\n    OrganizationLevel SMALLINT 'strict $[4]',\r\n    JobTitle NVARCHAR(50) 'strict $[5]', BirthDate DATE 'strict $[6]',\r\n    MaritalStatus NCHAR(1) 'strict $[7]', Gender NCHAR(1) 'strict $[8]',\r\n    HireDate DATE 'strict $[9]', SalariedFlag BIT 'strict $[10]',\r\n    VacationHours SMALLINT 'strict $[11]',\r\n    SickLeaveHours SMALLINT 'strict $[12]', CurrentFlag BIT 'strict $[13]',\r\n    rowguid UNIQUEIDENTIFIER 'strict $[14]',\r\n    ModifiedDate DATETIME 'strict $[15]'\r\n    );\r\nGO\r\n<\/pre>\n
Unfortunately, in this \u2018explicit schema\u2019 format, they haven\u2019t yet got around to supporting CLR types so you can\u2019t create a HierarchyID from JSON this way. Instead, you need to specify the CLR type as being NVARCHAR and coerce it into its CLR type in the result, thus with the OrganizationNode<\/strong> in the HumanResources.Employee<\/strong> table \u2026.<\/p>\n
SELECT BusinessEntityID, NationalIDNumber, LoginID, \r\n  Convert(HIERARCHYID,OrganizationNode) AS \"OrganizationNode\",\r\n  JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag,\r\n  VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate\r\n  FROM\r\n  OpenJson(@myJSON)\r\n  WITH\r\n    (\r\n    BusinessEntityID INT 'strict $[0]',\r\n    NationalIDNumber NVARCHAR(15) 'strict $[1]',\r\n    LoginID NVARCHAR(256) 'strict $[2]',\r\n    [OrganizationNode] nvarchar(30) 'strict $[3]',\r\n    OrganizationLevel SMALLINT 'strict $[4]',\r\n    JobTitle NVARCHAR(50) 'strict $[5]', BirthDate DATE 'strict $[6]',\r\n    MaritalStatus NCHAR(1) 'strict $[7]', Gender NCHAR(1) 'strict $[8]',\r\n    HireDate DATE 'strict $[9]', SalariedFlag BIT 'strict $[10]',\r\n    VacationHours SMALLINT 'strict $[11]',\r\n    SickLeaveHours SMALLINT 'strict $[12]', CurrentFlag BIT 'strict $[13]',\r\n    rowguid UNIQUEIDENTIFIER 'strict $[14]',\r\n    ModifiedDate DATETIME 'strict $[15]'\r\n    );\r\n<\/pre>\n
This works fine but complicates the code. If you are inserting into a table, you can rely on implicit coercion of the datatype to convert the NVARCHAR into a hierarchyid or geography.<\/p>\n
As well as getting this from the JSON Schema if it is available, we can get the explicit schema as well as the JSON_Value() column list. We can use either the schema or the sys.dm_exec_describe_first_result_set<\/strong> directly. The latter approach allows you to create a JSON Schema from any expression, which greatly extends the usefulness of this approach. if you already have the table that matches the JSON data, <\/strong>you merely specify a SELECT * from your table ( we used Humanresources.Employee) and the result contains your spec which you can then paste into the browser pane. This time, just so it is a bit more obvious what\u2019s going on, I\u2019ll do it without the aggregation of the lines. It is a bit more manual because you need to cut and paste the result, and don\u2019t forget to nick out that last comma.<\/p>\n
\/* the JSON_VALUE syntax *\/\r\nDECLARE @TheExpression sysname='adventureworks2016.HumanResources.Employee'\r\nDECLARE @SelectStatement NVARCHAR(200)=(SELECT 'Select * from '+@TheExpression)\r\nSELECT 'convert('+System_type_name + ',Json_Value(value, ''strict $['+ Convert(VARCHAR(3),column_ordinal-1)+']'')) as ['+f.name+']'\r\nFROM sys.dm_exec_describe_first_result_set\r\n  (@SelectStatement, NULL, 1) AS f \r\nORDER BY column_ordinal\r\ngo\r\n\r\n\/* the WITH Explicit Schema syntax *\/\r\nDECLARE @TheExpression sysname='adventureworks2016.HumanResources.Employee'\r\nDECLARE @SelectStatement NVARCHAR(200)=(SELECT 'Select * from '+@TheExpression)\r\nSELECT '['+f.name+']'+ ' '+System_type_name + ' ''strict $['+ Convert(VARCHAR(3),column_ordinal-1)+']'','\r\nFROM sys.dm_exec_describe_first_result_set\r\n  (@SelectStatement, NULL, 1) AS f \r\nORDER BY column_ordinal\r\nGo\r\n<\/pre>\n
That select statement can be derived like this \u2026<\/p>\n
DECLARE @TheExpression sysname='adventureworks2016.HumanResources.Employee'\r\nDECLARE @SelectStatement NVARCHAR(200)=(SELECT 'Select * from '+@TheExpression)\r\nSelect\r\n  String_Agg(\r\n    CASE \r\n\t WHEN user_type_id in (128,129,130)  THEN  'convert('+system_type_name+','+name+') as \"'+[name]+'\"'   \r\n\t --hierarchy (128) geometry (130) and geography types (129) can be coerced. \r\n\t WHEN user_type_id in (35)  THEN  'convert(varchar(max),'+name+') as \"'+[name]+'\"'   \r\n\t WHEN user_type_id in (99)  THEN  'convert(nvarchar(max),'+name+') as \"'+[name]+'\"'   \r\n\t WHEN user_type_id in (34)  THEN  'convert(varbinary(max),'+name+') as \"'+[name]+'\"'   \r\n\t ELSE quotename([name]) \r\n\tEND,', ')\r\n FROM \r\n   sys.dm_exec_describe_first_result_set\r\n\t  (@SelectStatement, NULL, 1) \r\n<\/pre>\n
Relational table to JSON Array<\/h3>\n
You can store tables or results from SQL Server in this economical format, though the process of generating the data isn\u2019t so pretty. FOR JSON doesn\u2019t support this directly, which is sad. Somehow, I was hoping for FOR JSON RAW.<\/p>\n
Just as with CSV, the array-within-array format is only valuable if both ends of the data transfer are aware of enough of the schema\/metadata to transform the document into a table. We\u2019ll go into the detail of how we do this with JSON Schema later. The first task is to squeeze the array-in-array format from the somewhat reluctant OpenJSON function.<\/p>\n
To get the JSON in array-of-array format from a particular table, and we\u2019ve chosen adventureworks2016.person.person<\/strong>, you do this in SQL Server 2017. I\u2019ve used the String_Agg() aggregation function, but you can do it as easily, but more messily, via the XML trick if you are on SQL Server 2016. Here is a simplified version of the way that we\u2019ll do it<\/p>\n
DECLARE @TheData NVARCHAR(MAX)=(\r\nSELECT '['+ String_Agg(f.EachLine,',')+']'\r\nFROM \r\n  (SELECT '['+String_Agg (\r\n     CASE WHEN shredded.type=1 \r\n       THEN '\"'+String_Escape(Coalesce(shredded.value,'null'),'json')+'\"'\r\n     ELSE Coalesce(shredded.value,'null') \r\n     END, ',') +']'\r\n     AS TheValue\r\n  FROM OpenJson((SELECT * \r\n                 FROM adventureworks2016.person.person \r\n         FOR JSON AUTO, INCLUDE_NULL_VALUES )) f\r\n   CROSS apply OpenJson(f.value) shredded\r\n   GROUP BY f.[Key])f(EachLine)\r\n)\r\n<\/pre>\n
Note that we need to specify \u2018INCLUDE_NULL_VALUES<\/strong>\u2019 in that OpenJSON expression. This is because we need all the columns to be converted in that array, even if they are null in some rows. If we don\u2019t we get the array in the right order but with maybe one or more fields missing, but without being able to know which!<\/p>\n
This is much faster than the old way we did the conversion to JSON, using XML.<\/p>\n
DECLARE @OurTable XML=(SELECT * FROM adventureworks2016.person.person FOR XML path, ROOT('root') )\r\nDECLARE @Json nvarchar(MAX)=(SELECT \r\n  '['--create a list (rows) of lists (tuples)\r\n  +Stuff( --we want to snip out the leading comma\r\n    (SELECT TheLine from --this is to glue each row into a string\r\n      (SELECT ',\r\n      ['+ --this is the start of the row, representing the row object in the JSON list\r\n        --the local-name(.) is an eXPath function that gives you the name of the node\r\n        Stuff((SELECT ',\"'+ b.c.value('text()[1]','NVARCHAR(MAX)') +'\"' \r\n               -- 'text()[1]' gives you the text contained in the node      \r\n               from x.a.nodes('*') b(c) --get the row XML and split it into each node\r\n               for xml path(''),TYPE).value('(.\/text())[1]','NVARCHAR(MAX)')\r\n          ,1,1,'')+']'--remove the first comma \r\n     from @OurTable.nodes('\/root\/*') x(a) --get every row\r\n     ) JSON(theLine) --each row \r\n    for xml path(''),TYPE).value('.','NVARCHAR(MAX)' )\r\n  ,1,1,'')--remove the first leading comma\r\n  +'\r\n  ]\r\n')\r\n<\/pre>\n
Whereas the XML version unfortunately shares a vice with the JSON version,\u00a0 because it also dislikes tables with CLR Datatypes and \u2018errors out\u2019.<\/p>\n
Msg 13604, Level 16, State 1, Line 3\r\nFOR JSON cannot serialize CLR objects. Cast CLR types explicitly into one of the supported types in FOR JSON queries.<\/pre>\n
This is a shame. Where there are no CLR types, it is possible to save the entire contents of a database with a routine like this, which is very satisfying. Note we have a hard-wired directory path which we ought to avoid. You\u2019ll need to alter that to a suitable server directory if you want to try this out.<\/p>\n
EXEC sp_msforeachtable '\r\nprint ''Creating JSON for ?''\r\nDECLARE @TheData NVARCHAR(MAX)=(\r\nSELECT ''[''+ String_Agg(f.EachLine,'','')+'']''\r\nFROM \r\n  (SELECT ''[''+String_Agg (\r\n     CASE WHEN shredded.type=1 \r\n       THEN ''\"''+String_Escape(Coalesce(shredded.value,''null''),''json'')+''\"''\r\n     ELSE Coalesce(shredded.value,''null'') \r\n     END, '','') +'']''\r\n     AS TheValue\r\n  FROM OpenJson((SELECT * \r\n                 FROM ? \r\n         FOR JSON AUTO, INCLUDE_NULL_VALUES )) f\r\n   CROSS apply OpenJson(f.value) shredded\r\n   GROUP BY f.[Key])f(EachLine)\r\n)\r\nCREATE TABLE ##myTemp (Bulkcol nvarchar(MAX))\r\nINSERT INTO ##myTemp (Bulkcol) SELECT @TheData\r\nprint ''Writing out ?''\r\nEXECUTE xp_cmdshell ''bcp ##myTemp out C:\\data\\RawData\\JsonData\\adventureworks\\?.JSON -c -C 65001 -T''\r\nDROP TABLE ##myTemp'\r\n<\/pre>\n
Instead of this, We\u2019ll add supporting temporary procedure to do the difficult bits that I\u2019ve already illustrated<\/p>\n
CREATE OR ALTER PROCEDURE #ArrayInArrayJsonDataFromTable\r\n  \/**\r\nSummary: >\r\n  This gets the JSON data from a table in Array\r\nAuthor: phil factor\r\nDate: 26\/10\/2018\r\n\r\nExamples: >\r\n  - use Adventureworks2016\r\n    DECLARE @Json NVARCHAR(MAX)\r\n    EXECUTE #ArrayInArrayJsonDataFromTable\r\n      @database='pubs', \r\n\t  @Schema ='dbo', \r\n\t  @table= 'authors',\r\n\t  @JSONData=@json OUTPUT\r\n    PRINT @Json\r\n\r\n  - DECLARE @Json NVARCHAR(MAX)\r\n\tEXECUTE #ArrayInArrayJsonDataFromTable @TableSpec='bigpubs.[dbo].[oldTitles]',@JSONData=@json OUTPUT\r\n    PRINT @Json\r\nReturns: >\r\n  The JSON data\r\n**\/\r\n  (@database sysname = NULL, @Schema sysname = NULL, @table sysname = NULL,\r\n  @Tablespec sysname = NULL, @jsonData NVARCHAR(MAX) OUTPUT\r\n  )\r\nAS\r\n  BEGIN\r\n    DECLARE @Data NVARCHAR(MAX);\r\n    IF Coalesce(@table, @Tablespec) IS NULL\r\n    OR Coalesce(@Schema, @Tablespec) IS NULL\r\n      RAISERROR('{\"error\":\"must have the table details\"}', 16, 1);\r\n\r\n    IF @table IS NULL SELECT @table = ParseName(@Tablespec, 1);\r\n    IF @Schema IS NULL SELECT @Schema = ParseName(@Tablespec, 2);\r\n    IF @database IS NULL SELECT @database = Coalesce(ParseName(@Tablespec, 3),Db_Name());\r\n    IF @table IS NULL OR @Schema IS NULL OR @database IS NULL\r\n      RAISERROR('{\"error\":\"must have the table details\"}', 16, 1);\r\n\r\n    DECLARE @SourceCode NVARCHAR(255) =\r\n              (\r\n              SELECT 'SELECT * FROM ' + QuoteName(@database) + '.'\r\n                     + QuoteName(@Schema) + '.' + QuoteName(@table)\r\n              );\r\n\r\n    DECLARE @params NVARCHAR(MAX) =(\r\n      SELECT String_Agg(\r\n        CASE\r\n\t\t WHEN user_type_id IN (128, 129, 130) \r\n\t\t   THEN'convert(nvarchar(100),' + name + ') as \"' + name + '\"'\r\n          --hierarchyid (128) geometry (130) and geography types (129) can be coerced. \r\n         WHEN user_type_id IN (35) \r\n\t\t   THEN 'convert(varchar(max),' + name + ') as \"' + name + '\"'\r\n         WHEN user_type_id IN (99) \r\n\t\t   THEN 'convert(nvarchar(max),' + name + ') as \"' + name + '\"'\r\n         WHEN user_type_id IN (34) \r\n\t\t   THEN 'convert(varbinary(max),' + name + ') as \"' + name + '\"'\r\n\t\t ELSE QuoteName(name) END, ', ' )\r\n      FROM sys.dm_exec_describe_first_result_set(@SourceCode, NULL, 1) );\r\n\r\n\r\nDECLARE @expression NVARCHAR(800) =\t'\r\nUSE ' + @database + '\r\nSELECT @TheData=(SELECT ' + @params + ' FROM ' + QuoteName(@database) + '.'\r\n      + QuoteName(@Schema) + '.' + QuoteName(@table)\r\n      + ' FOR JSON auto, INCLUDE_NULL_VALUES)';\r\n    EXECUTE sp_executesql @expression, N'@TheData nvarchar(max) output',\r\n            @TheData = @Data OUTPUT;\r\n\r\nSELECT @jsonData ='['+ String_Agg(f.EachLine,',')+']'\r\nFROM \r\n  (SELECT '['+String_Agg (\r\n     CASE WHEN shredded.type=1 \r\n       THEN '\"'+String_Escape(Coalesce(shredded.value,'null'),'json')+'\"'\r\n     ELSE Coalesce(shredded.value,'null') \r\n     END, ',') +']'\r\n     AS TheValue\r\n  FROM OpenJson(@data) f\r\n   CROSS apply OpenJson(f.value) shredded\r\n   GROUP BY f.[Key])f(EachLine)\r\n  END;\r\nGO\r\n\r\n\r\nDECLARE @ourPath sysname = 'C:\\data\\RawData\\JsonData\\AdventureWorks\\';\r\nDeclare @command NVARCHAR(4000)= '\r\nprint ''Creating JSON file for ?''\r\nDECLARE @Json NVARCHAR(MAX)\r\nEXECUTE #ArrayInArrayJsonDataFromTable @TableSpec=''?'',@JSONData=@json OUTPUT\r\nCREATE TABLE ##myTemp (Bulkcol nvarchar(MAX))\r\nINSERT INTO ##myTemp (Bulkcol) SELECT @JSON\r\nprint ''Writing out ?''\r\nEXECUTE xp_cmdshell ''bcp ##myTemp out '+@ourPath+'?.JSON -c -C 65001 -T''\r\nDROP TABLE ##myTemp'\r\nEXECUTE sp_msforeachtable @command\r\nGO\r\n<\/pre>\n
Now this isn\u2019t fast: it is two minutes rather than eighteen seconds to dump out Adventureworks<\/strong> in native mode or twenty-one seconds in tab-delimited mode. However we want JSON, especially as we can validate it and distinguish between blank strings and nulls, and attach a schema to the table<\/p>\n
Adding a schema.<\/h3>\n
The schema that accompanies this is reasonably simple to generate, Here, just to illustrate how we do it, is a batch that does it for the query ‘SELECT * FROM adventureworks2016.HumanResources.Employee<\/code>‘.<\/p>\n
DECLARE @schema NVARCHAR(4000)\r\nSELECT @schema=SELECT 'https:\/\/mml.uk\/jsonSchema\/HREmployeeArray.json\u2019 AS id,--just a unique reference to a real place\r\n  'http:\/\/json-schema.org\/draft-04\/schema#' AS [schema],--the minimum standard you want to use\r\n  'Array (rows) within an array (table) of adventureworks2016.HumanResources.Employee' AS description,\r\n  'array' AS type, 'array' AS [items.type],\r\n  (\r\n  SELECT  \r\n      f.name, --the individual columns as an array of objects with standard and custom fields\r\n      CASE WHEN f.is_nullable = 1 THEN Json_Query('[\"null\",\"'+f.type+'\"]') -- must be array!\r\n      ELSE  Json_Query('[\"'+f.type+'\"]') END AS [type],--must be an array!\r\n      f.SQLtype, f.is_nullable, Coalesce(EP.value,'') AS description\r\n    FROM\r\n      (--the basic columns we need. (the type is used more than once in the outer query) \r\n      SELECT r.name, r.system_type_name AS sqltype, r.source_column, r.is_nullable,\r\n             CASE WHEN r.system_type_id IN (58,52,56,58,59,60,62,106,108,122,127) THEN 'number' \r\n               WHEN system_type_id =104 THEN 'boolean' ELSE 'string' END AS type,\r\n             Object_Id(r.source_database + '.' + r.source_schema + '.' + r.source_table) \r\n              AS table_id\r\n        FROM sys.dm_exec_describe_first_result_set\r\n               ('SELECT * FROM adventureworks2016.HumanResources.Employee', NULL, 1) AS r\r\n      ) AS f\r\n      LEFT OUTER  JOIN sys.extended_properties AS EP -- to get the extended properties\r\n        ON EP.major_id = f.table_id\r\n         AND EP.minor_id = ColumnProperty(f.table_id, f.source_column, 'ColumnId')\r\n         AND EP.name = 'MS_Description'\r\n         AND EP.class = 1\r\n    FOR JSON PATH\r\n  ) AS [items.items]\r\nFOR JSON PATH, WITHOUT_ARRAY_WRAPPER;\r\n<\/pre>\n
This turns out a JSON Schema that, nicely formatted, looks like this<\/p>\n
\"id\": \"https:\/\/mml.uk\/jsonSchema\/HREmployeeArray.json\",\r\n  \"schema\": \"http:\/\/json-schema.org\/draft-04\/schema#\",\r\n  \"description\": \"Array (rows) within an array (table) of adventureworks2016.HumanResources.Employee\",\r\n  \"type\": \"array\",\r\n  \"items\": {\r\n    \"type\": \"array\",\r\n    \"items\": [\r\n      {\r\n        \"name\": \"BusinessEntityID\",\r\n        \"type\": [\r\n          \"number\"\r\n        ],\r\n        \"SQLtype\": \"int\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 1,\r\n        \"description\": \"Primary key for Employee records.  Foreign key to BusinessEntity.BusinessEntityID.\"\r\n      },\r\n      {\r\n        \"name\": \"NationalIDNumber\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"nvarchar(15)\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 2,\r\n        \"description\": \"Unique national identification number such as a social security number.\"\r\n      },\r\n      {\r\n        \"name\": \"LoginID\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"nvarchar(256)\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 3,\r\n        \"description\": \"Network login.\"\r\n      },\r\n      {\r\n        \"name\": \"OrganizationNode\",\r\n        \"type\": [\r\n          \"null\",\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"hierarchyid\",\r\n        \"is_nullable\": true,\r\n        \"column_ordinal\": 4,\r\n        \"description\": \"Where the employee is located in corporate hierarchy.\"\r\n      },\r\n      {\r\n        \"name\": \"OrganizationLevel\",\r\n        \"type\": [\r\n          \"null\",\r\n          \"number\"\r\n        ],\r\n        \"SQLtype\": \"smallint\",\r\n        \"is_nullable\": true,\r\n        \"column_ordinal\": 5,\r\n        \"description\": \"The depth of the employee in the corporate hierarchy.\"\r\n      },\r\n      {\r\n        \"name\": \"JobTitle\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"nvarchar(50)\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 6,\r\n        \"description\": \"Work title such as Buyer or Sales Representative.\"\r\n      },\r\n      {\r\n        \"name\": \"BirthDate\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"date\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 7,\r\n        \"description\": \"Date of birth.\"\r\n      },\r\n      {\r\n        \"name\": \"MaritalStatus\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"nchar(1)\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 8,\r\n        \"description\": \"M = Married, S = Single\"\r\n      },\r\n      {\r\n        \"name\": \"Gender\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"nchar(1)\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 9,\r\n        \"description\": \"M = Male, F = Female\"\r\n      },\r\n      {\r\n        \"name\": \"HireDate\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"date\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 10,\r\n        \"description\": \"Employee hired on this date.\"\r\n      },\r\n      {\r\n        \"name\": \"SalariedFlag\",\r\n        \"type\": [\r\n          \"boolean\"\r\n        ],\r\n        \"SQLtype\": \"bit\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 11,\r\n        \"description\": \"Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.\"\r\n      },\r\n      {\r\n        \"name\": \"VacationHours\",\r\n        \"type\": [\r\n          \"number\"\r\n        ],\r\n        \"SQLtype\": \"smallint\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 12,\r\n        \"description\": \"Number of available vacation hours.\"\r\n      },\r\n      {\r\n        \"name\": \"SickLeaveHours\",\r\n        \"type\": [\r\n          \"number\"\r\n        ],\r\n        \"SQLtype\": \"smallint\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 13,\r\n        \"description\": \"Number of available sick leave hours.\"\r\n      },\r\n      {\r\n        \"name\": \"CurrentFlag\",\r\n        \"type\": [\r\n          \"boolean\"\r\n        ],\r\n        \"SQLtype\": \"bit\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 14,\r\n        \"description\": \"0 = Inactive, 1 = Active\"\r\n      },\r\n      {\r\n        \"name\": \"rowguid\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"uniqueidentifier\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 15,\r\n        \"description\": \"ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.\"\r\n      },\r\n      {\r\n        \"name\": \"ModifiedDate\",\r\n        \"type\": [\r\n          \"string\"\r\n        ],\r\n        \"SQLtype\": \"datetime\",\r\n        \"is_nullable\": false,\r\n        \"column_ordinal\": 16,\r\n        \"description\": \"Date and time the record was last updated.\"\r\n      }\r\n    ]\r\n  }\r\n}\r\n<\/pre>\n
Now, if you send the JSON schema with the JSON, either within the same JSON document or separately, you can create the OpenJSON explicit schema from the values in the JSON Schema. Let\u2019s do this manually, just to show the \u2018man behind the curtain\u2019.<\/p>\n
DECLARE @jsonSchema NVARCHAR(MAX) ='\r\n{\"id\": \"http:\\\/\\\/mml.uk\\\/json\\\/schemas\\\/ahemployee.json\",\r\n  \"schema\": \"http:\\\/\\\/json-schema.org\\\/draft-04\\\/schema#\",\r\n  \"description\": \"Array (rows) within an array (table) of adventureworks2016.HumanResources.Employee\", \"type\": \"array\", \"items\": {\"type\": \"array\", \"items\": [{\"name\": \"BusinessEntityID\", \"type\": [\"number\"],\"SQLtype\": \"int\", \"is_nullable\": false,\"column_ordinal\": 1,\"description\": \"Primary key for Employee records.  Foreign key to BusinessEntity.BusinessEntityID.\"},{\"name\": \"NationalIDNumber\", \"type\": [\"string\"],\"SQLtype\": \"nvarchar(15)\", \"is_nullable\": false,\"column_ordinal\": 2,\"description\": \"Unique national identification number such as a social security number.\"},{\"name\": \"LoginID\", \"type\": [\"string\"],\"SQLtype\": \"nvarchar(256)\", \"is_nullable\": false,\"column_ordinal\": 3,\"description\": \"Network login.\"},{\"name\": \"OrganizationNode\", \"type\": [\"null\", \"string\"],\"SQLtype\": \"hierarchyid\", \"is_nullable\": true,\"column_ordinal\": 4,\"description\": \"Where the employee is located in corporate hierarchy.\"},{\"name\": \"OrganizationLevel\", \"type\": [\"null\", \"number\"],\"SQLtype\": \"smallint\", \"is_nullable\": true,\"column_ordinal\": 5,\"description\": \"The depth of the employee in the corporate hierarchy.\"},{\"name\": \"JobTitle\", \"type\": [\"string\"],\"SQLtype\": \"nvarchar(50)\", \"is_nullable\": false,\"column_ordinal\": 6,\"description\": \"Work title such as Buyer or Sales Representative.\"},{\"name\": \"BirthDate\", \"type\": [\"string\"],\"SQLtype\": \"date\", \"is_nullable\": false,\"column_ordinal\": 7,\"description\": \"Date of birth.\"},{\"name\": \"MaritalStatus\", \"type\": [\"string\"],\"SQLtype\": \"nchar(1)\", \"is_nullable\": false,\"column_ordinal\": 8,\"description\": \"M = Married, S = Single\"},{\"name\": \"Gender\", \"type\": [\"string\"],\"SQLtype\": \"nchar(1)\", \"is_nullable\": false,\"column_ordinal\": 9,\"description\": \"M = Male, F = Female\"},{\"name\": \"HireDate\", \"type\": [\"string\"],\"SQLtype\": \"date\", \"is_nullable\": false,\"column_ordinal\": 10,\"description\": \"Employee hired on this date.\"},{\"name\": \"SalariedFlag\", \"type\": [\"boolean\"],\"SQLtype\": \"bit\", \"is_nullable\": false,\"column_ordinal\": 11,\"description\": \"Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.\"},{\"name\": \"VacationHours\", \"type\": [\"number\"],\"SQLtype\": \"smallint\", \"is_nullable\": false,\"column_ordinal\": 12,\"description\": \"Number of available vacation hours.\"},{\"name\": \"SickLeaveHours\", \"type\": [\"number\"],\"SQLtype\": \"smallint\", \"is_nullable\": false,\"column_ordinal\": 13,\"description\": \"Number of available sick leave hours.\"},{\"name\": \"CurrentFlag\", \"type\": [\"boolean\"],\"SQLtype\": \"bit\", \"is_nullable\": false,\"column_ordinal\": 14,\"description\": \"0 = Inactive, 1 = Active\"},{\"name\": \"rowguid\", \"type\": [\"string\"],\"SQLtype\": \"uniqueidentifier\", \"is_nullable\": false,\"column_ordinal\": 15,\"description\": \"ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.\"},{\"name\": \"ModifiedDate\", \"type\": [\"string\"],\"SQLtype\": \"datetime\", \"is_nullable\": false,\"column_ordinal\": 16,\"description\": \"Date and time the record was last updated.\"}]}}'\r\n\r\nSELECT \r\n  String_Agg(\r\n    Json_Value(\r\n      value,\r\n      'strict $.name')+' '+Json_Value(value,'strict $.SQLtype')+\r\n      ' $['+Convert(NvARCHAR(3),Json_Value(value,'strict $.column_ordinal')-1) +\r\n       ']',',\r\n')  \r\nFROM OpenJson(@jsonSchema,'$.items.items')\r\n\r\n<\/pre>\n
Now so far, we\u2019ve Managed to create a schema for a single table. We need to automate this now to do any table. Let\u2019s create this now and try it out<\/p>\n
CREATE OR ALTER PROCEDURE  #CreateJSONArrayInArraySchemaFromTable\r\n\/**\r\nSummary: >\r\n  This creates a JSON schema from a table that\r\n  matches the JSON you will get from doing a \r\n  classic FOR JSON select * statemenmt on the entire table\r\n\r\nAuthor: phil factor\r\nDate: 4\/12\/2018\r\n\r\nExamples: >\r\n  DECLARE @Json NVARCHAR(MAX)\r\n  EXECUTE #CreateJSONArrayInArraySchemaFromTable @database='pubs', @Schema ='dbo', @table= 'authors',@JSONSchema=@json OUTPUT\r\n  PRINT @Json\r\n  SELECT @json=''\r\n  EXECUTE #CreateJSONArrayInArraySchemaFromTable @TableSpec='pubs.dbo.authors',@JSONSchema=@json OUTPUT\r\n  PRINT @Json\r\nReturns: >\r\n  nothing\r\n**\/\r\n    (@database sysname=null, @Schema sysname=NULL, @table sysname=null, @Tablespec sysname=NULL,@jsonSchema NVARCHAR(MAX) output)\r\n\r\n--WITH ENCRYPTION|SCHEMABINDING, ...\r\nAS\r\n\r\nDECLARE @required NVARCHAR(max), @NoColumns INT, @properties NVARCHAR(max);\r\n\t\t\t\r\n\tIF Coalesce(@table,@Tablespec) IS NULL\r\n\t\tOR Coalesce(@schema,@Tablespec) IS NULL\r\n\t\tRAISERROR ('{\"error\":\"must have the table details\"}',16,1)\r\n\t\t\t\r\n\tIF @table is NULL SELECT @table=ParseName(@Tablespec,1)\r\n\tIF @Schema is NULL SELECT @schema=ParseName(@Tablespec,2)\r\n\tIF @Database is NULL SELECT @Database=Coalesce(ParseName(@Tablespec,3),Db_Name())\r\n\tIF @table IS NULL OR @schema IS NULL OR @database IS NULL\r\n\t\tRAISERROR  ('{\"error\":\"must have the table details\"}',16,1)\r\n           \r\nDECLARE @SourceCode NVARCHAR(255)=\r\n  'SELECT * FROM '+QuoteName(@database)+ '.'+ QuoteName(@Schema)+'.'+QuoteName(@table)\r\n\r\nSELECT @jsonschema= \r\n  (SELECT \r\n    'https:\/\/mml.uk\/jsonSchema\/'+@table+'.json' AS id,--just a unique reference to a real place\r\n    'http:\/\/json-schema.org\/draft-04\/schema#' AS [schema],--the minimum standard you want to use\r\n    'Array (rows) within an array (table) of'+@Schema+'.'+@table AS description,\r\n    'array' AS type, 'array' AS [items.type],\r\n    (\r\n    SELECT  \r\n      f.name, --the individual columns as an array of objects with standard and custom fields\r\n      CASE WHEN f.is_nullable = 1 THEN Json_Query('[\"null\",\"'+f.type+'\"]') -- must be array!\r\n      ELSE  Json_Query('[\"'+f.type+'\"]') END AS [type],--must be an array!\r\n      f.SQLtype, f.is_nullable, Coalesce(EP.value,'') AS description\r\n    FROM\r\n      (--the basic columns we need. (the type is used more than once in the outer query) \r\n      SELECT r.name, r.system_type_name AS sqltype, r.source_column, r.is_nullable,\r\n             CASE WHEN r.system_type_id IN (58,52,56,58,59,60,62,106,108,122,127) THEN 'number' \r\n               WHEN system_type_id =104 THEN 'boolean' ELSE 'string' END AS type,\r\n             Object_Id(r.source_database + '.' + r.source_schema + '.' + r.source_table) \r\n              AS table_id\r\n        FROM sys.dm_exec_describe_first_result_set\r\n               (@SourceCode, NULL, 1) AS r\r\n      ) AS f\r\n    LEFT OUTER  JOIN sys.extended_properties AS EP -- to get the extended properties\r\n      ON EP.major_id = f.table_id\r\n       AND EP.minor_id = ColumnProperty(f.table_id, f.source_column, 'ColumnId')\r\n       AND EP.name = 'MS_Description'\r\n       AND EP.class = 1\r\n    FOR JSON PATH\r\n  ) AS [items.items]\r\n   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);\r\n\tIF(IsJson(@jsonschema)=0) \r\n\tRAISERROR ('invalid schema \"%s\"',16,1,@jsonSchema)\r\n\tIF @jsonschema IS NULL RAISERROR ('Null schema',16,1)\r\nGO\r\n<\/pre>\n
We can now try this out by writing the schemas of all the tables in AdventureWorks.<\/p>\n
USE Adventureworks2016\r\nDECLARE @ourPath sysname = 'C:\\data\\RawData\\JsonSchema\\AdventureWorks\\';\r\nDeclare @command NVARCHAR(4000)= '\r\nprint ''Creating JSON file for ?''\r\nDECLARE @Json NVARCHAR(MAX)\r\nEXECUTE #CreateJSONArrayInArraySchemaFromTable @TableSpec=''?'',@JSONSchema=@json OUTPUT\r\nCREATE TABLE ##myTemp (Bulkcol nvarchar(MAX))\r\nINSERT INTO ##myTemp (Bulkcol) SELECT @JSON\r\nprint ''Writing out ?''\r\nEXECUTE xp_cmdshell ''bcp ##myTemp out '+@ourPath+'?.JSON -c -C 65001 -T''\r\nDROP TABLE ##myTemp'\r\nEXECUTE sp_msforeachtable @command\r\nGO\r\n<\/pre>\n
It takes 11 seconds to do them all on my machine. You aren\u2019t limited to tables, you can do any SQL Query.<\/p>\n
Conclusions<\/h1>\n
We seem to have used a lot of SQL to achieve our ends. However, we now have data that we can validate outside the database, share with JSON-friendly applications or import into JSON-savvy databases. We have a version of a JSON tabular document that is economical in storage.<\/p>\n
The next stage is to use it to build a database. I\u2019ve described elsewhere how to do it with the more conventional Object-within-array JSON document and schema but not in array-in-array JSON. That\u2019s next.<\/p>\n
SourceCode<\/h3>\n
The source to this article and various blogs on the topic of importing, validating and exporting both JSON Schema and data in SQL Server\u00a0\u00a0is on github here<\/a>.\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"
JSON was initially designed for the informal transfer of data that has no schema. It has no concept of a table, or of an array of identical arrays. This means that it must tell you each key for each object, even if the original data object was a table. With the happy minimum of key-value……<\/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-81895","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\/81895","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=81895"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81895\/revisions"}],"predecessor-version":[{"id":81953,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/81895\/revisions\/81953"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=81895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=81895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=81895"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=81895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}