Welcome back for another session of exploring Blackboard Data. Previously we looked over SQL that indicated
student and faculty activity in the LMS. Now let’s take a look at some hidden data in the tables.
What if you’re looking for batch_uid data in the PERSON or COURSE table? Or what if you’re looking for the
Blackboard login name, user_id, in the PERSON table?
The data is there, but encoded a JSON field called STAGE. You have to use specific functions or syntax to access the data. You can both display the data or use it in the where clause.
select stage, -- lists the JSON object stage:user_id::string, --strips the quotes from JSON data stage:batch_uid::string, stage:uuid from cdm_lms.person where stage:user_id='machajes'
Notice in the field selection we use “::string” at the end. Without that the fields will include a double quote as they are stored in a JSON object.
Here is an example of extracting COURSE table data and reacing the batch_uid:
select name, course_number, stage, -- lists the JSON object stage:batch_uid::string, --strips the quotes from JSON data 7 stage:allow_guest, stage:allow_observer, stage:avl_rule_indicator, stage:data_src_pk1, stage:service_level, stage:sos_id_pk2, stage:uuid from cdm_lms.course where stage:batch_uid = '2019.spring.cis.101.12345'