Data Table to JSON

Sometimes you want to serialize the data table into a JSON string so you can feed javascript widgets that require such format. In this example, we get the filtered rows from a given data table and generate it's JSON

Consider the following input table

A	B	C
A1	B1	C1
A2	B2	C2

The output should look like this after running the script:

[{'A':'A1','B':'B1','C':'C1'},{'A':'A2','B':'B2','C':'C2'},{'A':'A3','B':'B3','C':'C3'}]


#get filtered rows
#dataTable can be a Script parameter pointing to a specific Data Table. In this case, we use the current one

dataTable = Document.ActiveDataTableReference

#get rows and columns from our selection
rows = Document.ActiveFilteringSelectionReference.GetSelection(dataTable).AsIndexSet()
cols = dataTable.Columns

#generate our "json" data
data=[]
for r in rows:
 item={}
 for c in cols:
  item[c.Name] = c.RowValues.GetFormattedValue(r)
 data.append(item)

#convert json to string
import clr
clr.AddReference('System.Web.Extensions')
from System.Web.Script.Serialization import JavaScriptSerializer
json=JavaScriptSerializer().Serialize(data)

#save to document property to use later in javascript

Document.Properties["json"]=json

Note:  If you want to limit the number of columns in your output, you can use the FindAll method. Suppose you have many columns that contains the word Project (Project Name, Project Date, Big Project, Small Project and you also want to include the Budget column, then you can use the Spotfire Search Syntax

for c in cols.FindAll("*Project* OR Budget"):
  print c