Survey Designer and surveyResponse JSON

The latest iteration of the Survey Designer tool, originally released in Campus.2040, is built using a JavaScript library called SurveyJS. Full documentation of the SurveyJS library is available. This latest version of the tool saves data in a different way than the previous tool, so any user-created SQL scripts that point to survey response tables will need to be adjusted. Both the survey and the survey responses are now stored as a string of JavaScript Object Notation (JSON). JSON supports hierarchical data structures, making use of objects and arrays. 

An object is a key/value pair. (e.q., {"key1" : "value1", "key2":"value2"})

An array is a list of values. e.q., [value1, value2, value3]

Within the JSON, objects and arrays can contain other objects or arrays, giving the data a tree-like structure. eq., {"key1" : [{ "values1" : [1, 2], "values2" : [3,4] }] } 

Understanding SurveyJSON

When building a survey in the Survey Designer, the user begins with an empty survey and is able to add components like pages, and questions. The survey components translate to Objects in the SurveyJSON string. 

screenshot delineating each compontent level

#

Survey Component

JSON Object

contains things like...

1Survey BodyROOT objectTitle, description, survey properties, containers
2ContainersPagespage title, page description, page properties, elements
3QuestionsElementsquestion properties, Choices

In the surveyJSON, Survey is the Root Object that contains the pages and other survey properties like title and description. Pages are the top-level container object within the survey. Pages can contain elements. Elements can contain question properties, and question choices. Choices can contain Items.

For example, the following surveyJSON describes a survey with two pages, each with page title and page description filled in, and with one question on each page:

{"title":"Survey Title","description":"Survey description","pages":[{"name":"page1","elements":[{"type":"radiogroup","name":"question1","choices":["item1","item2","item3"]}],"title":"page1","description":"page1 description"},{"name":"page2","elements":[{"type":"checkbox","name":"question2","title":"question2","choices":["item1","item2","item3"]}],"title":"page2","description":"page2 description"}]}

Formatting the JSON makes it easier to understand the object hierarchy (google JSON Pretty Print to find an online formatting tool like https://jsonformatter.org/json-pretty-print).

SurveyJSON Formatted

example of the formatted JSON code

Understanding ResponseJSON

Compared to the SurveyJSON, the ResponseJSON is a bit simpler to understand. When a user responds to a survey, the JSON string containing that user's complete response is saved to the surveyResponse table in the responseJSON column. The responseJSON stores the respondent's name and question answers. If the survey was anonymous, the respondent name is not stored. 

For example, the following responseJSON is what would be saved if user Carlota Johnson  selected item1 in question 1, and then item2 and item3 in question 2 in the example survey from the previous section:

{"firstname":"CARLOTA","question1":"item1","question2":["item2","item3"],"lastname":"JOHNSON"}

ResponseJSON Formatted

screenshot of the response JSON formatted code

The name in the ResponseJSON is static. That means if the user's name changes in the future, the name within the survey will not change. This functionality might change in the future.

The ResponseJSON can be viewed when testing the survey.

screenshot of using the As JSON tab within survey result options

Working with ResponseJSON in SQL Server

Working with JSON in Microsoft SQL Server is possible. To see in-depth SQL examples, we recommend reviewing the official Microsoft documentation for the OPENJSON function:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15

A simple SQL OPENJSON Example

This SQL statement will return data from the surveyResponse table along with the key : value pairs in the responseJSON based on a specified responseID. 

  1. Declare a variable for responseID
  2. Set the variable
  3. Select the desired columns from surveyResponse table along with the key and value from the responseJSON
  4. Cross join the ResonseJSON results with the surveyResponse results where responseID is the same

screenshot of a simple SQL OPENJSON Example

Example Result set

surveyID

personID

respondentID

startTimestamp

endTimestamp

question

answer

1123412021-04-22 13:23:09.3072021-04-22 13:23:15.730firstnameCARLOTA
1123412021-04-22 13:23:09.3072021-04-22 13:23:15.730question2["item2","item3"]
1123412021-04-22 13:23:09.3072021-04-22 13:23:15.730question1item1
1123412021-04-22 13:23:09.3072021-04-22 13:23:15.730lastnameJOHNSON