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.

stage, -- lists the JSON object
stage:user_id::string, --strips the quotes from JSON data stage:batch_uid::string,
from cdm_lms.person where

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:

   stage, -- lists the JSON object 
  stage:batch_uid::string, --strips the quotes from JSON data 7 
  from cdm_lms.course 
  stage:batch_uid = '2019.spring.cis.101.12345'