I am trying to take data from a table in SQL Server and convert it to a JSON format. I achieved conversion to a rudimentary JSON format using Marshmallow. I have not worked with JSON extensively. The requirement is to convert it into a hierarchical JSON format like shown below:
Required JSON format:
{
"Year" : [{
"2016" : [{
"Q1" : [{
"Jan" : [{
"Week1" : [{
"Sunday" : [1,2,3],
"Monday" : [1,2,3],
"Tuesday" : [1,2,3],
"Wednesday" : [1,2,3],
"Thursday" : [1,2,3],
"Friday" : [1,2,3],
"Saturday" : [1,2,3]
}],
"Week2" : [{
"Sunday" : [1,2,3],
"Monday" : [1,2,3],
"Tuesday" : [1,2,3],
"Wednesday" : [1,2,3],
"Thursday" : [1,2,3],
"Friday" : [1,2,3],
"Saturday" : [1,2,3]
}],
"Week3" : [{
"Sunday" : [1,2,3],
"Monday" : [1,2,3],
"Tuesday" : [1,2,3],
"Wednesday" : [1,2,3],
"Thursday" : [1,2,3],
"Friday" : [1,2,3],
"Saturday" : [1,2,3]
}],
"Week4" : [{
"Sunday" : [1,2,3],
"Monday" : [1,2,3],
"Tuesday" : [1,2,3],
"Wednesday" : [1,2,3],
"Thursday" : [1,2,3],
"Friday" : [1,2,3],
"Saturday" : [1,2,3]
}],
}],
}],
}],
}],
}
I have read data from table, created some functions and have achieved this format:
Sample achieved format:
[{"sent_year": 2017, "sent_quarter": 4, "sent_month": "December", "sent_week": "Week5", "sent_day": "Sunday", "application_name": "App Two", "sent_score": "3"},
{"sent_year": 2017, "sent_quarter": 4, "sent_month": "December", "sent_week": "Week5", "sent_date": "Monday", "application_name": "App One", "sent_score": "1"},
{"sent_year": 2017, "sent_quarter": 4, "sent_month": "December", "sent_week": "Week5", "sent_date": "Tuesday", "application_name": "App Two", "sent_score": "3"},
{"sent_year": 2016, "sent_quarter": 4, "sent_month": "December", "sent_week": "Week5", "sent_date": "Wednesday", "application_name": "App One", "sent_score": "2"},
{"sent_year": 2016, "sent_quarter": 4, "sent_month": "December", "sent_week": "Week5", "sent_date": "Thursday", "application_name": "App Two", "sent_score": "1"},
{"sent_year": 2016, "sent_quarter": 4, "sent_month": "December", "sent_week": "Week5", "sent_date": "Friday", "application_name": "App One", "sent_score": "4"}]
Since this involves grouping of data based on values, I'm unable to figure out how to proceed. Please help.
