Exporting Trello Boards With JSON, T-SQL and SSIS, Part 2: T-SQL Parsing and Rendering

Stored Procedure Construction, Step-by-Step

In my previous post, I outlined a scenario where we’d like to take a Kanban board from Trello, export it to a JSON document and parse it with T-SQL in order to distribute it via email. The challenge with this type of document is the variable number of lists in the Kanban board which will make up the columns of our resultset. Our T-SQL solution will incorporate elements of importing external data sources, JSON query functions, and methods to compose and execute dynamic T-SQL.

To follow along, you can open a free individual account on the Trello site and create a board with the Company Overview template, then export the board using JSON to produce the source file for the stored procedure. Use any user database on a SQL Server 2016 instance or higher with a compatibility level of 130 or higher (this setting is needed to execute the OPENJSON function).

For the definition of my stored procedure, I opted for 3 parameters, 5 variables and a temporary table to start things off.

Parameters
- @PathAndFile — this inbound parameter will be the fully qualified name of the JSON-formatted export file from the Trello board page, saved to a path accessible to you and your SQL instance.
- @BoardName — this outbound parameter will return the name of the board the export file is reporting on, retrieved by a JSON query.
- @Debug — a bit flag to output transformed data at the end of each block for troubleshooting.
Variables
- @json — this variable will hold the contents of the JSON document.
- @BulkTSQL — this variable will hold the T-SQL to be executed to bulk insert the file. The syntax requires a literal for the source file so we’ll use dynamic SQL to construct this code.
- @PivotTSQL — the variable for constructing the dynamic SQL for the final report.
- @PivotColumns — this variable will be used in preparing @PivotTSQL to create a pivot table of our results.
- @PivotColumnHeaders — this variable is similar to @PivotColumns but will have additional formatting logic for final output.
Temp Tables
- #CardListsTable — this will be the denormalized listing of the Trello board’s lists and their associated cards in order of appearance.

USE [MattyZDBA_DB]
GO

/****** Object:  StoredProcedure [dbo].[usp_ParseTrelloBoard]    Script Date: 12/29/2019 2:48:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[usp_ParseTrelloBoard] 
	@PathAndFile NVARCHAR(255) = 'C:\Labs\Archive\qFGFIjKw.json'
	,@BoardName NVARCHAR(255) OUTPUT
	,@Debug bit=0
AS
BEGIN
	DECLARE @json NVARCHAR(max) 
		--to hold the JSON document
	DECLARE @BulkTSQL NVARCHAR(512) 
		--to hold the dynamic SQL to import the file via OPENROWSET (the function can only use literals)
	DECLARE @PivotColumns NVARCHAR(max) 
		--to be used in the PIVOT..FOR operator in the final output
	DECLARE @PivotColumnHeaders NVARCHAR(max) 
		--to be used in the outermost select for final output, checking for NULL conditions
	DECLARE @PivotTSQL NVARCHAR(max) 
		--for construction of the final output's statement with dynamic TSQL

	IF OBJECT_ID('tempdb.dbo.#CardsListsTable') IS NOT 
		NULL
		DROP TABLE #CardsListsTable 
			--temp table to hold the essential shredded JSON results

	CREATE TABLE #CardsListsTable (
		ListName NVARCHAR(255)
		,ListColumnNumber INT
		,CardName NVARCHAR(255)
		,CardRowNumber INT
		)

USE [MattyZDBA_DB]

/****** Object: StoredProcedure [dbo].[usp_ParseTrelloBoard] Script Date: 12/29/2019 2:48:09 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE OR ALTER PROCEDURE [dbo].[usp_ParseTrelloBoard]

@PathAndFile NVARCHAR(255) = 'C:\Labs\Archive\qFGFIjKw.json'

,@BoardName NVARCHAR(255) OUTPUT

,@Debug bit=0

BEGIN

DECLARE @json NVARCHAR(max)

--to hold the JSON document

DECLARE @BulkTSQL NVARCHAR(512)

--to hold the dynamic SQL to import the file via OPENROWSET (the function can only use literals)

DECLARE @PivotColumns NVARCHAR(max)

--to be used in the PIVOT..FOR operator in the final output

DECLARE @PivotColumnHeaders NVARCHAR(max)

--to be used in the outermost select for final output, checking for NULL conditions

DECLARE @PivotTSQL NVARCHAR(max)

--for construction of the final output's statement with dynamic TSQL

IF OBJECT_ID('tempdb.dbo.#CardsListsTable') IS NOT

NULL

DROP TABLE #CardsListsTable

--temp table to hold the essential shredded JSON results

CREATE TABLE #CardsListsTable (

ListName NVARCHAR(255)

,ListColumnNumber INT

,CardName NVARCHAR(255)

,CardRowNumber INT

)

The block below deals with the bulk insert of the JSON document into the @json variable using OPENROWSET. There are a couple of prerequisites…

OPENROWSET…BULK requires bulkadmin server role membership. Because of this, the login that will invoke this larger import process should be a limited scope-service account (i.e., a credential with matching SQL Server Agent proxy to run a SSIS package executing this procedure).
You can’t use a variable with OPENROWSET to specify the source file so we’re going to need to use dynamic SQL to construct the statement with its OUTPUT parameter.
1. There is a significant risk from SQL injection attacks using this method considering the number of commands that can fit in an nvarchar(255) variable.
2. To mitigate the risk, I’m using QUOTENAME to wrap the incoming path + filename as a literal, plus I check for semicolon (;) statement terminators and put a double-dash (–) comment to thwart those attack types.
3. For additional methods to guard against SQL Injection, please check out Kimberly Tripp’s classic post at https://www.sqlskills.com/blogs/kimberly/little-bobby-tables-sql-injection-and-execute-as/
With dynamic SQL, we’ll need to use sp_executesql to designate @json as an output parameter and get the imported document.

After retrieval, we parse out the board name with a scalar function JSON_VALUE. The strict tag will force an error to be thrown if the property requested isn’t present (lax would return a null if not found).

	--you'll need bulkadmin role membership or comparable for OPENROWSET (BULK
	--as well as rights to the filepath and file indicated
	--This is risky due to the risk of SQL injection attack (remember DROP Bobby.Tables?)
	--I'll add a first-level defense with QUOTENAME and a REPLACE of semicolons with an additional -- comment tag
	--defend in depth here. add  based on your SQL injection counterattack strategy
	SET @BulkTSQL = REPLACE(
			'SELECT @json=BulkColumn FROM OPENROWSET(BULK ' 
			+ QUOTENAME(@PathAndFile, '''') + 
			', SINGLE_CLOB) AS j', ';', ';--')

	EXEC sp_executesql @BulkTSQL
		,N'@json nvarchar(max) OUTPUT'
		,@json OUTPUT

	IF @Debug=1
		SELECT @json [JSON]

	SELECT @BoardName = JSON_VALUE(@json, 
			'strict $.name')

	IF @Debug=1
		SELECT @BoardName [BoardName]

--you'll need bulkadmin role membership or comparable for OPENROWSET (BULK

--as well as rights to the filepath and file indicated

--This is risky due to the risk of SQL injection attack (remember DROP Bobby.Tables?)

--I'll add a first-level defense with QUOTENAME and a REPLACE of semicolons with an additional -- comment tag

--defend in depth here. add based on your SQL injection counterattack strategy

SET @BulkTSQL = REPLACE(

'SELECT @json=BulkColumn FROM OPENROWSET(BULK '

+ QUOTENAME(@PathAndFile, '''') +

', SINGLE_CLOB) AS j', ';', ';--')

EXEC sp_executesql @BulkTSQL

,N'@json nvarchar(max) OUTPUT'

,@json OUTPUT

IF @Debug=1

SELECT @json [JSON]

SELECT @BoardName = JSON_VALUE(@json,

'strict $.name')

IF @Debug=1

SELECT @BoardName [BoardName]

We can see that we were able to retrieve the JSON file and the board name…

We now start working on the collection of lists from the JSON document to prepare what will become the column headers of our Kanban board recreation.

To allow the user to move lists anywhere on the board for the life of the board, Trello utilizes a position value which defaults to a multiple of 64k and is also a decimal value. That way, if a list needs to be inserted in between two existing lists, all Trello needs to do is to change the list position value to the median of the two existing lists’ position values, avoiding the need to rewrite position values across the entire board.

For the solution, I use two common table expressions (CTEs) here to display the lists in the proper order. I’m using the OPENJSON table-valued function to shred the JSON into a tabular resultset using the WITH clause the indicate which properties to return.

The first one, Lists, orders the lists and assigns them a simple ordinal using the ROW_NUMBER() windowing function. The second CTE, BoardLists,”pivots” the lists into two similar concatenated lists of columns.

@PivotColumns will be used downstream in the final query using the PIVOT operator so we’ll need to delimit the list values with square brackets.
We go one additional step with @PivotColumnHeaders as it will determine output file formatting, replacing NULLs with empty strings.

By using a simple ordinal, we area able to establish a join in the second CTE between the current and the next items in the list, constructing our column headers in the proper sequence like a breadcrumb trail.

	;WITH Lists (
		ListName --name of list on the Trello Board
		,ListPosition 
		--decimal value for its position in the Trello document
		,RowNumber 
		--oridinal position we'll calculate with a windowing function
		)
	AS (
		SELECT ListName
			,ListPosition
			,ROW_NUMBER() OVER (
				ORDER BY ListPosition
				)
		FROM OPENJSON(@json) WITH (Lists NVARCHAR(max) 'strict $.lists' AS json
				)
		CROSS APPLY OPENJSON(Lists) WITH (
				--the .pos property defaults to multiples of 64k in Trello JSON documents. 
				--Whenever you move cards and lists around, you'll observe in the document that they calculate the median 
				--(including fractions hence the precision=3, watch out for numeric overflow here)
				--between the lists to the left and the right avoid a key conflict and guarantee proper ordering
				ListPosition NUMERIC(10, 3) '$.pos'
				,ListName NVARCHAR(max) '$.name'
				)
		)
	,BoardList (
		ListPosition
		,ListName
		,ListPath
		,ListPathValues
		,RowNumber
		)
	AS (
		SELECT ListPosition
			,ListName
			--ListPath will be be used in the PIVOT...FOR operator to provide the list of valid column headers
			--using QUOTENAME to automatically add brackets [], might be a good deterrent from SQL injection too
			,ListPath = CAST(QUOTENAME(ListName, '[') AS 
				NVARCHAR(4000))
			--ListPathValues will be used in the outer query to check for NULLs and replace them with empty strings
			--so that a copy/paste into Excel won't require extra cleanup
			,ListPathValues = 'ISNULL(' + CAST(QUOTENAME(
					ListName, '[') AS NVARCHAR(4000)) + ','''') ' + CAST
			(QUOTENAME(ListName, '[') AS NVARCHAR(4000))
			,RowNumber
		FROM Lists Anchor
		WHERE RowNumber = 1
		
		UNION ALL
		
		SELECT Lists.ListPosition
			,Lists.ListName
			--concantenation of the above; no risk of a trailing comma due to the inner join beloww
			,BoardList.ListPath + ',' + QUOTENAME(Lists.
				ListName, '[')
			,BoardList.ListPathValues + ',' + 'ISNULL(' + CAST(
				QUOTENAME(Lists.ListName, '[') AS NVARCHAR(4000)) 
			+ ','''') ' + CAST(QUOTENAME(Lists.ListName, '[') AS 
				NVARCHAR(4000))
			,Lists.RowNumber
		FROM Lists
		--Join back to the CTE offsetting the row number by one to get the next member
		INNER JOIN BoardList ON Lists.RowNumber = BoardList.
			RowNumber + 1
		)
	--Save these until later in the final query
	--We just need the last one after all the list names have been concatenated
	SELECT @PivotColumns = ListPath
		,@PivotColumnHeaders = ListPathValues
	FROM BoardList
	ORDER BY RowNumber DESC
		--Could have used TOP 1 but wanted to go ANSI standard this time
		OFFSET 0 ROWS
	FETCH NEXT 1 ROW ONLY

	IF @Debug=1
		SELECT @PivotColumns [PivotColumns], @PivotColumnHeaders [PivotColumnHeaders]

;WITH Lists (

ListName --name of list on the Trello Board

,ListPosition

--decimal value for its position in the Trello document

,RowNumber

--oridinal position we'll calculate with a windowing function

)

AS (

SELECT ListName

,ListPosition

,ROW_NUMBER() OVER (

ORDER BY ListPosition

)

FROM OPENJSON(@json) WITH (Lists NVARCHAR(max) 'strict $.lists' AS json

)

CROSS APPLY OPENJSON(Lists) WITH (

--the .pos property defaults to multiples of 64k in Trello JSON documents.

--Whenever you move cards and lists around, you'll observe in the document that they calculate the median

--(including fractions hence the precision=3, watch out for numeric overflow here)

--between the lists to the left and the right avoid a key conflict and guarantee proper ordering

ListPosition NUMERIC(10, 3) '$.pos'

,ListName NVARCHAR(max) '$.name'

)

,BoardList (

ListPosition

,ListName

,ListPath

,ListPathValues

,RowNumber

)

AS (

SELECT ListPosition

,ListName

--ListPath will be be used in the PIVOT...FOR operator to provide the list of valid column headers

--using QUOTENAME to automatically add brackets [], might be a good deterrent from SQL injection too

,ListPath = CAST(QUOTENAME(ListName, '[') AS

NVARCHAR(4000))

--ListPathValues will be used in the outer query to check for NULLs and replace them with empty strings

--so that a copy/paste into Excel won't require extra cleanup

,ListPathValues = 'ISNULL(' + CAST(QUOTENAME(

ListName, '[') AS NVARCHAR(4000)) + ','''') ' + CAST

(QUOTENAME(ListName, '[') AS NVARCHAR(4000))

,RowNumber

FROM Lists Anchor

WHERE RowNumber = 1

UNION ALL

SELECT Lists.ListPosition

,Lists.ListName

--concantenation of the above; no risk of a trailing comma due to the inner join beloww

,BoardList.ListPath + ',' + QUOTENAME(Lists.

ListName, '[')

,BoardList.ListPathValues + ',' + 'ISNULL(' + CAST(

QUOTENAME(Lists.ListName, '[') AS NVARCHAR(4000))

+ ','''') ' + CAST(QUOTENAME(Lists.ListName, '[') AS

NVARCHAR(4000))

,Lists.RowNumber

FROM Lists

--Join back to the CTE offsetting the row number by one to get the next member

INNER JOIN BoardList ON Lists.RowNumber = BoardList.

RowNumber + 1

)

--Save these until later in the final query

--We just need the last one after all the list names have been concatenated

SELECT @PivotColumns = ListPath

,@PivotColumnHeaders = ListPathValues

FROM BoardList

ORDER BY RowNumber DESC

--Could have used TOP 1 but wanted to go ANSI standard this time

OFFSET 0 ROWS

FETCH NEXT 1 ROW ONLY

IF @Debug=1

SELECT @PivotColumns [PivotColumns], @PivotColumnHeaders [PivotColumnHeaders]

We end up with these values which will be utilized in our dynamic SQL query generating the ouput later…

This next set of CTEs parses the JSON document of both the Lists and Cards collections and establishes the parent-child relationship between them.

CardsLists returns both collections as JSON values for easier reference downstream.
CardSet further parses the Cards collection into a set of names and positions along with its foreign key of ListID to refer back to its parent list. The position in the list uses the same 64k interval to allow for splits during repositioning. We replace it with a simple ordinal via the ROW_NUMBER windowing function.
ListSet reparses the collection of lists like we did in the previous CTE set with our primary key being its ListID property.
Finally, our CardsListsTable CTE joins the two on ListID.

	;WITH CardsLists (
		Cards
		,Lists
		)
	AS (
		SELECT Cards
			,Lists
		FROM OPENJSON(@json) WITH (
				Cards NVARCHAR(max) 'strict $.cards' AS json
				,Lists NVARCHAR(max) 'strict $.lists' AS json
				)
		)
	,CardSet (
		CardName
		,CardRowNumber
		,ListId
		)
	AS (
		SELECT CardName
			,ROW_NUMBER() OVER (
				PARTITION BY ListID ORDER BY CardPosition
				) CardRowNumber
			,
			--you need to partitition by ListID as CardPositions are reusable across lists
			ListID
		FROM CardsLists
		CROSS APPLY OPENJSON(Cards) WITH (
				CardName NVARCHAR(max) '$.name'
				--Like for Lists above, the .pos property defaults to multiples of 65536+1 in Trello JSON documents. 
				,CardPosition NUMERIC(15, 3) '$.pos'
				--Foreign Key Reference back to the ListID
				,ListID NVARCHAR(max) '$.idList'
				)
		)
	,ListSet (
		ListID
		,ListColumnNumber
		,ListName
		)
	AS (
		SELECT ListId
			,ListColumnNumber
			,ListName
		FROM CardsLists
		CROSS APPLY OPENJSON(Lists) WITH (
				Listid NVARCHAR(max) '$.id'
				,ListColumnNumber NUMERIC(15, 3) '$.pos'
				,listname NVARCHAR(max) '$.name'
				)
		)
		,CardsListsTable (
		ListName
		,ListColumnNumber
		,CardName
		,CardRowNumber
		)
	AS (
		SELECT ListName
			,ListColumnNumber
			,CardName
			,CardRowNumber
		FROM CardSet
		INNER JOIN ListSet ON CardSet.ListId = ListSet.ListId
		)
	INSERT INTO #CardsListsTable (
		ListName
		,ListColumnNumber
		,CardName
		,CardRowNumber
		)
	SELECT ListName
		,ListColumnNumber
		,CardName
		,CardRowNumber
	FROM CardsListsTable

	IF @Debug=1
		SELECT
			ListName
			,ListColumnNumber
			,CardName
			,CardRowNumber
		FROM #CardsListsTable

;WITH CardsLists (

Cards

,Lists

)

AS (

SELECT Cards

,Lists

FROM OPENJSON(@json) WITH (

Cards NVARCHAR(max) 'strict $.cards' AS json

,Lists NVARCHAR(max) 'strict $.lists' AS json

)

,CardSet (

CardName

,CardRowNumber

,ListId

)

AS (

SELECT CardName

,ROW_NUMBER() OVER (

PARTITION BY ListID ORDER BY CardPosition

) CardRowNumber

--you need to partitition by ListID as CardPositions are reusable across lists

ListID

FROM CardsLists

CROSS APPLY OPENJSON(Cards) WITH (

CardName NVARCHAR(max) '$.name'

--Like for Lists above, the .pos property defaults to multiples of 65536+1 in Trello JSON documents.

,CardPosition NUMERIC(15, 3) '$.pos'

--Foreign Key Reference back to the ListID

,ListID NVARCHAR(max) '$.idList'

)

,ListSet (

ListID

,ListColumnNumber

,ListName

)

AS (

SELECT ListId

,ListColumnNumber

,ListName

FROM CardsLists

CROSS APPLY OPENJSON(Lists) WITH (

Listid NVARCHAR(max) '$.id'

,ListColumnNumber NUMERIC(15, 3) '$.pos'

,listname NVARCHAR(max) '$.name'

)

,CardsListsTable (

ListName

,ListColumnNumber

,CardName

,CardRowNumber

)

AS (

SELECT ListName

,ListColumnNumber

,CardName

,CardRowNumber

FROM CardSet

INNER JOIN ListSet ON CardSet.ListId = ListSet.ListId

)

INSERT INTO #CardsListsTable (

ListName

,ListColumnNumber

,CardName

,CardRowNumber

)

SELECT ListName

,ListColumnNumber

,CardName

,CardRowNumber

FROM CardsListsTable

IF @Debug=1

SELECT

ListName

,ListColumnNumber

,CardName

,CardRowNumber

FROM #CardsListsTable

We end up with the result set below…

Our final code block takes the contents of our temp table populated by CardsListsTable above and does our PIVOT operation to lay out the lists and cards as they appear on the Trello board page.

Because of the transformation tasks we did earlier for @PivotColumns and @PivotColumnHeaders, assembly via dynamic SQL is straightforward. Again, SQL injection is still a risk factor, but can be mitigated with cleansing operations, either here or in the previous CTE block when preparing individual card and list name values.

	--Add as many SQL injection checks as you feel are necessary
	--Noting the range of values that can be stored in the properties of the Trello document
	--a ;DROP Bobby.Tables or something more nefarious can sneak in (though the QUOTENAME brackets may thwart them)
	SET @PivotTSQL = 'SELECT ' + @PivotColumnHeaders + 
		' FROM 
(SELECT CardName,CardRowNumber,ListName FROM #CardsListsTable) Base
PIVOT
(MAX([CardName])
FOR
ListName IN (' 
		+ @PivotColumns + ')) AS P'

	--With this, you'll generate a replica of the Trello board the document is based on
	--In proper list and card order, ready for copy/paste into Excel to share with your non-Trello users
	IF @Debug=1
		PRINT (@PivotTSQL)
	EXEC (@PivotTSQL)

	RETURN 0
END
GO

--Add as many SQL injection checks as you feel are necessary

--Noting the range of values that can be stored in the properties of the Trello document

--a ;DROP Bobby.Tables or something more nefarious can sneak in (though the QUOTENAME brackets may thwart them)

SET @PivotTSQL = 'SELECT ' + @PivotColumnHeaders +

' FROM

(SELECT CardName,CardRowNumber,ListName FROM #CardsListsTable) Base

PIVOT

(MAX([CardName])

FOR

ListName IN ('

+ @PivotColumns + ')) AS P'

--With this, you'll generate a replica of the Trello board the document is based on

--In proper list and card order, ready for copy/paste into Excel to share with your non-Trello users

IF @Debug=1

PRINT (@PivotTSQL)

EXEC (@PivotTSQL)

RETURN 0

END

Our dynamically-generated T-SQL looks like this…

SELECT ISNULL([Teams],'') [Teams],ISNULL([Up Next],'') [Up Next],ISNULL([Current Projects],'') [Current Projects],ISNULL([Completed Projects],'') [Completed Projects],ISNULL([Bravos],'') [Bravos] FROM 
(SELECT CardName,CardRowNumber,ListName FROM #CardsListsTable) Base
PIVOT
(MAX([CardName])
FOR
ListName IN ([Teams],[Up Next],[Current Projects],[Completed Projects],[Bravos])) AS P

SELECT ISNULL([Teams],'') [Teams],ISNULL([Up Next],'') [Up Next],ISNULL([Current Projects],'') [Current Projects],ISNULL([Completed Projects],'') [Completed Projects],ISNULL([Bravos],'') [Bravos] FROM

(SELECT CardName,CardRowNumber,ListName FROM #CardsListsTable) Base

PIVOT

(MAX([CardName])

FOR

ListName IN ([Teams],[Up Next],[Current Projects],[Completed Projects],[Bravos])) AS P

…and our final results are below. Note the resemblance with the Trello board at left.

All-in-all, not a bad attempt in recreating a Trello board using JSON and T-SQL. This can be enhanced by concatenating additional properties from the cards and lists.

Now we can move onto exporting the results to a text file and distributing them via email.

Exporting Trello Boards With JSON, T-SQL and SSIS, Part 2: T-SQL Parsing and Rendering

Stored Procedure Construction, Step-by-Step

About the Author: Matt Zuberko

Leave A Comment Cancel reply

Exporting Trello Boards With JSON, T-SQL and SSIS, Part 2: T-SQL Parsing and Rendering

Stored Procedure Construction, Step-by-Step

About the Author: Matt Zuberko

Related Posts

Leave A Comment Cancel reply