SQL Server 2016 JSON Support FOR Data Warehousing

SQL Server 2016 JSON Support FOR Data Warehousing Sifiso W. Ndlovu @mafiswana

Agenda Part I: Convert JSON Data to Relational Part II: Convert Relational Data to JSON

Agenda JSON Editor https: //jsonformatter. curiousconcept. com/

PART I: Convert JSON Data to Relational

What Is JSON (Java. Script Object Notation) is a lightweight, readable format for structuring data It is based on a subset of Java. Script language It is used primarily to transmit data between a server and web application, as an alternative to XML It is easy for humans to read and write. It is easy for machines to parse and generate

What Is JSON Sample JSON document

Convert JSON Data to Relational Requirement for Data Warehouse, T-SQL, Report development JSON by itself doesn’t not make sense to business or reporting tools

Convert JSON Data to Relational Available functions 1) ISJSON 2) OPENJSON 3) JSON_VALUE

Convert JSON Data to Relational 1) ISJSON

Convert JSON Data to Relational 1) ISJSON § Validates JSON content § Returns 1 for a valid JSON dataset and 0 for invalids § Demo

Convert JSON Data to Relational 2) OPENJSON § Works similar to OPENXML § Takes in an object and convert its data into rows and columns § Demo

Convert JSON Data to Relational 3) JSON_VALUE § Extracts a scalar value from a JSON string § Demo

PART JSON II: Convert Relational Data to

Convert Relational Data to JSON Unsupported § Data Types Spatial o Geometry, Geography, CLR (Common Language Runtime) Supported Data Types SQL Server Data Type JSON Data Type char, nchar, varchar, nvarchar, datetime, datetime 2, time, datetimeoffset, uniqueidentifier, money string int, bigint, float, decimal, numeric number Bit Boolean varbinary, image, timestamp, rowversion BASE 64 -encoded string

Convert Relational Data to JSON 1) 2) FOR JSON Clause 1) AUTO Mode 2) PATH Mode Options FOR JSON Clause 1) ROOT 2) INCLUDE_NULL_VALUES 3) WITHOUT_ARRAY_WRAPPER

Convert Relational Data to JSON 1) FOR JSON Clause 1) AUTO Mode § simplest way to convert relational data into a JSON § the structure of the JSON output is determined by a combination of the order of columns in your SELECT statement as well as the tables that are referenced by the SELECT statement § Demo

Convert Relational Data to JSON 1) FOR JSON Clause 2) PATH Mode a) Without a dot syntax b) With a dot syntax

Convert Relational Data to JSON 1) FOR JSON Clause 2) PATH Mode a) Without a dot syntax § When you are using it without a dot syntax, it works similar to the AUTO mode in that it will generate a JSON output based on the ordering of columns in your SELECT statement § Demo

Convert Relational Data to JSON 1) FOR JSON Clause 2) PATH Mode b) With a dot syntax b) Used to organise the JSON output into child arrays c) Demo

Convert Relational Data to JSON 2) Options FOR JSON Clause 1) ROOT § Used assign a label to the top-level array § Demo

Convert Relational Data to JSON 2) Options FOR JSON Clause 2) INCLUDE_NULL_VALUES § Default behaviour when using FOR JSON clause is that NULL values will not be included in your JSON output § This can be overridden by specifying the INCLUDE_NULL_VALUES option § Demo

Convert Relational Data to JSON 2) Options FOR JSON Clause 3) WITHOUT_ARRAY_WRAPPER § Default every FOR JSON clause returns JSON data wrapped around square brackets § To override this, use WITHOUT_ARRAY_WRAPPER option § Demo

References www. SQLShack. com

Thank you to our sponsors

Questions