Blackboard Data: Exploration ( scenario 3 ) user_id and batch_uid from STAGE

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'