bertwagner’s gists

bertwagner / JSON_MODIFY 1.sql

Last active February 6, 2017 22:04

SQL Server 2016 JSON's MODIFY_JSON

	-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
	DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'

	-- I upgraded some features in my Volkswagen recently, technically making it equivalent to a "GLI" instead of a "GL".
	-- Let's update our JSON using JSON_MODIFY:
	SET @garage = JSON_MODIFY(@garage, '$.Cars[0].Model.Trim', 'GLI')
	SELECT @garage
	-- Output: { "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }

bertwagner / SQL Basic Json.sql

Last active February 6, 2017 22:05

SQL Server 2016 JSON's direct JSON string creation

	-- The most primative way of creating JSON in SQL. We don't want to have to do this
	DECLARE @garage nvarchar(100) = '{ "Cars" : [{ "Make" : "Volkswagen"}, { "Make" : "Subaru"}] }'

	-- But it works!
	SELECT @garage
	-- Output: { "Cars" : [{ "Make" : "Volkswagen"}, { "Make" : "Subaru"}] }

	-- And with our SQL 2016 ISJSON() function we can check that the JSON string is valid
	SELECT ISJSON(@garage)
	-- Output: 1

bertwagner / FOR JSON.sql

Created January 25, 2017 00:12

SQL Server 2016 JSON's FOR JSON

	-- Create our table with test data
	DROP TABLE IF EXISTS ##Garage;
	CREATE TABLE ##Garage
	(
	Id int IDENTITY(1,1),
	Make varchar(100),
	BaseModel varchar(50),
	Trim varchar(50),
	Year int,
	PurchaseDate datetime2

bertwagner / FOR JSON PATH.sql

Last active February 6, 2017 22:08

SQL Server 2016 JSON's FOR JSON PATH

	-- PATH will format a result using dot syntax in the column aliases. Here's an example with just default column names
	SELECT Make, BaseModel, Trim, Year, PurchaseDate
	FROM ##Garage
	FOR JSON PATH, ROOT('Cars');
	-- Output: {"Cars":[{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]}

	-- And here is the same example, just assigning aliases to define JSON nested structure
	SELECT Make, BaseModel as [Model.Base], Trim AS [Model.Trim], Year, PurchaseDate
	FROM ##Garage
	FOR JSON PATH, ROOT('Cars');

bertwagner / FOR JSON AUTO.sql

Last active February 6, 2017 22:09

SQL Server 2016 JSON's FOR JSON AUTO

	-- AUTO will format a result into JSON following the same structure of the result set
	SELECT Make, BaseModel, Trim, Year, PurchaseDate
	FROM ##Garage
	FOR JSON AUTO;
	-- Output: [{"Make":"Volkswagen","BaseModel":"Golf","Trim":"GL","Year":2003,"PurchaseDate":"2006-10-05T00:00:00"},{"Make":"Subaru","BaseModel":"Impreza","Trim":"Premium","Year":2016,"PurchaseDate":"2015-08-18T00:00:00"}]

	-- Using aliases will rename JSON keys
	SELECT Make AS [CarMake]
	FROM ##Garage
	FOR JSON AUTO;

bertwagner / JSON_QUERY.sql

Last active February 6, 2017 22:09

SQL Server 2016 JSON's JSON_QUERY() Function Part 2

	-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
	DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'

	-- We use JSON_QUERY to get the JSON representation of the Cars array
	SELECT JSON_QUERY(@garage, '$.Cars')
	-- Output: [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }]

	-- If we combine it with JSON_VALUE we can then pull out specific scalar values
	SELECT JSON_VALUE(JSON_QUERY(@garage, '$.Cars') , '$[0].Make')
	-- Output: Volkswagen

bertwagner / JSON_QUERY.sql

Last active February 6, 2017 22:10

SQL Server 2016 JSON's JSON_QUERY() Function Part 1

	-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
	DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'

	-- This returns NULL because the values of Cars is an array instead of a simple object
	SELECT JSON_VALUE(@garage, '$.Cars')
	-- Output: NULL

	-- Using JSON_QUERY() however returns the JSON string representation of our array object
	SELECT JSON_QUERY(@garage, '$.Cars')
	-- Output: [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }]

bertwagner / OPENJSON2.sql

Last active February 6, 2017 22:13

SQL Server 2016 JSON's OPENJSON() Function Part 2

	-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
	DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'

	-- Here we retrieve the Make of each vehicle in our Cars array
	SELECT JSON_VALUE(value, '$.Make') FROM OPENJSON(@garage, '$.Cars')
	/* Output:
	------------
	Volkswagen
	Subaru
	*/

bertwagner / OPENJSON.sql

Last active February 6, 2017 22:17

SQL Server 2016 JSON's OPENJSON() Function Part 1

	-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
	DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'

	SELECT * FROM OPENJSON(@garage, '$.Cars') -- Displaying the values of our "Cars" array. We additionally get the order of the JSON objects outputted in the "key" column and the JSON object datatype in the "type" column
	/* Output:
	key value type
	------ ------------------------------------------------------------------------------------------------------------------------------------ ----
	0 { "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 20

bertwagner / JSON_VALUE.sql

Last active September 22, 2017 12:30

SQL Server 2016 JSON's JSON_VALUE() Function

	-- See https://gist.github.com/bertwagner/356bf47732b9e35d2156daa943e049e9 for a formatted version of this JSON
	DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GL" }, "Year": 2003, "PurchaseDate": "2006-10-05T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" }] }'

	SELECT JSON_VALUE(@garage, '$.Cars[0].Make') -- Return the make of the first car in our array
	-- Output: Volkswagen

	SELECT CAST(JSON_VALUE(@garage, '$.Cars[0].PurchaseDate') as datetime2) -- Return the Purchase Date of the first car in our array and convert it into a DateTime2 datatype
	-- Output: 2006-10-05 00:00:00.0000000

	SELECT JSON_VALUE(@garage, '$.Cars') -- This returns NULL because the values of Cars is an array instead of a simple object

Bert Wagner bertwagner