canvas data 2 export

JimEgan
Community Explorer

I'm working with a Canvas Data 2 CSV export of the QUIZ_SUBMISSIONS table.  The column SUBMISSION_DATA is usually a well formed JSON string.  I have two questions about the column.

1. There are two rows (out of over 10 million) that are not proper JSON syntax.  Instead, the values are both "__dap_oversized_truncated__".  In our import we're going to watch for that value and change it to a NULL so it doesn't trip up our processing.  Is the actual value stored somewhere else?

2. For quiz answers where question_type is 'multiple answer question', the answers are stored in the JSON string with a syntax like this:

{"correct":"partial","points":1.0,"question_id":5363828,"text":"","answer_8325":"1","answer_3704":"0","answer_9236":"1","answer_446":"0","answer_3639":"0","more_comments":""}

This is just one "row" in the JSON string.  Since the answer value isn't provided as a known JSON key in the string, how can I extract the value for the answer?  I do understand that the key name has the answer_id embedded in it.  But I don't know how to extract the value for a key when I don't know name of the key.

0 Likes