Skip to main content
Below are example SQL queries you can use to query the data. 

​
Standard Framework-Level Queries

To learn more about the entities and relationships related to these queries, read more here.

​
List all standard frameworks

SELECT * FROM standards_framework;

​
Find standard framework by subject or state

SELECT *
FROM standards_framework
WHERE "academicSubject" = 'Mathematics'
  AND "jurisdiction" = 'California';

​
Get standard framework by CASE UUID

SELECT *
FROM standards_framework WHERE "caseIdentifierUUID" = 'c64961be-d7cb-11e8-824f-0242ac160002'

​
Standard-Level Queries 

To learn more about the entities and relationships related to these queries, read more here.

​
Get all standards in a standard framework

-- WARNING: This recursive query may hit performance issues or database recursion limits
-- on large/deep hierarchies. If execution fails, either increase your database's recursion
-- limit settings or use an iterative approach in application code.

WITH RECURSIVE all_descendants AS (
  -- Base case: direct children of the StandardsFramework
  SELECT sfi."caseIdentifierUUID"
  FROM relationships r
  JOIN standards_framework_item sfi
    ON sfi."caseIdentifierUUID" = r."targetEntityValue"
  WHERE r."sourceEntity" = 'StandardsFramework'
    AND r."sourceEntityKey" = 'caseIdentifierUUID'
    AND r."sourceEntityValue" = 'c64961be-d7cb-11e8-824f-0242ac160002'
    AND r."targetEntity" = 'StandardsFrameworkItem'
    AND r."targetEntityKey" = 'caseIdentifierUUID'
  
  UNION ALL
  
  -- Recursive case: children of already found items
  SELECT sfi."caseIdentifierUUID"
  FROM relationships r
  JOIN standards_framework_item sfi
    ON sfi."caseIdentifierUUID" = r."targetEntityValue"
  JOIN all_descendants ad
    ON ad."caseIdentifierUUID" = r."sourceEntityValue"
  WHERE r."sourceEntity" = 'StandardsFrameworkItem'
    AND r."sourceEntityKey" = 'caseIdentifierUUID'
    AND r."targetEntity" = 'StandardsFrameworkItem'
    AND r."targetEntityKey" = 'caseIdentifierUUID'
    AND r."relationshipType" = 'hasChild'
)
SELECT "caseIdentifierUUID"
FROM all_descendants;

​
List child standards for a standard

SELECT child.*
FROM relationships r
JOIN standards_framework_item child
  ON child."caseIdentifierUUID" = r."targetEntityValue"
WHERE r."relationshipType" = 'hasChild'
  AND r."sourceEntity" = 'StandardsFrameworkItem'
  AND r."sourceEntityKey" = 'caseIdentifierUUID'
  AND r."sourceEntityValue" = '57165a88-d7cc-11e8-824f-0242ac160002'
  AND r."targetEntity" = 'StandardsFrameworkItem'
  AND r."targetEntityKey" = 'caseIdentifierUUID';

​
Get parent standard for a standard

SELECT parent.*
FROM relationships r
JOIN standards_framework_item parent
  ON parent."caseIdentifierUUID" = r."sourceEntityValue"
WHERE r."relationshipType" = 'hasChild'
  AND r."sourceEntity" = 'StandardsFrameworkItem'
  AND r."sourceEntityKey" = 'caseIdentifierUUID'
  AND r."targetEntity" = 'StandardsFrameworkItem'
  AND r."targetEntityKey" = 'caseIdentifierUUID'
  AND r."targetEntityValue" = '57165d0c-d7cc-11e8-824f-0242ac160002';

​
Get standard by standard code

SELECT *
FROM standards_framework_item
WHERE "statementCode" = '7.G.A.1';

​
Get standard by standard ID

SELECT *
FROM standards_framework_item
WHERE "caseIdentifierUUID" = 'c4401baa-b0e5-496c-92c3-352fda95e5ae';

​
Get standards for a grade

SELECT *
FROM standards_framework_item
WHERE EXISTS (
  SELECT 1 FROM json_array_elements_text("gradeLevel") AS elem 
  WHERE elem = '6'
)
AND "jurisdiction" = 'Multi-State';

​
Learning Components queries

To learn more about the entities and relationships related to these queries, read more here.

​
List all learning components

SELECT *
FROM learning_component;

​
Get a learning component by ID

SELECT *
FROM learning_component
WHERE "identifier" = '0046446a-0a9b-5ace-92a3-23d4bb158c68';

​
Learning Progressions queries

To learn more about the entities and relationships related to these queries, read more here.

​
List all standards that relates to a standard

SELECT sfi.*
FROM relationships r
JOIN standards_framework_item sfi
  ON sfi."caseIdentifierUUID" = r."sourceEntityValue"
WHERE r."relationshipType" = 'relatesTo'
  AND r."sourceEntity" = 'StandardsFrameworkItem'
  AND r."sourceEntityKey" = 'caseIdentifierUUID'
  AND r."targetEntity" = 'StandardsFrameworkItem'
  AND r."targetEntityKey" = 'caseIdentifierUUID'
  AND r."targetEntityValue" = '6ba115f2-d7cc-11e8-824f-0242ac160002';

​
List standards that build toward a standard

SELECT sfi.*
FROM relationships r
JOIN standards_framework_item sfi
  ON sfi."caseIdentifierUUID" = r."sourceEntityValue"
WHERE r."relationshipType" = 'buildsTowards'
  AND r."sourceEntity" = 'StandardsFrameworkItem'
  AND r."sourceEntityKey" = 'caseIdentifierUUID'
  AND r."targetEntity" = 'StandardsFrameworkItem'
  AND r."targetEntityKey" = 'caseIdentifierUUID'
  AND r."targetEntityValue" = '6ba1b8b8-d7cc-11e8-824f-0242ac160002';

​
Combined dataset queries 

​
List all learning components in a grade 

WITH grade6_standards AS (
  SELECT "caseIdentifierUUID"
  FROM standards_framework_item
  WHERE EXISTS (
    SELECT 1 FROM json_array_elements_text("gradeLevel") AS elem 
    WHERE elem = '6'
  )
),
supporting_components AS (
  SELECT DISTINCT r."sourceEntityValue" AS lc_id
  FROM relationships r
  JOIN grade6_standards g
    ON g."caseIdentifierUUID" = r."targetEntityValue"
  WHERE r."relationshipType" = 'supports'
    AND r."sourceEntity" = 'LearningComponent'
    AND r."sourceEntityKey" = 'identifier'
    AND r."targetEntity" = 'StandardsFrameworkItem'
    AND r."targetEntityKey" = 'caseIdentifierUUID'
)
SELECT lc.*
FROM learning_component lc
JOIN supporting_components c
  ON lc."identifier" = c.lc_id;

​
List learning components supporting a standard

SELECT lc.*
FROM relationships r
JOIN learning_component lc
  ON lc."identifier" = r."sourceEntityValue"
WHERE r."relationshipType" = 'supports'
  AND r."sourceEntity" = 'LearningComponent'
  AND r."sourceEntityKey" = 'identifier'
  AND r."targetEntity" = 'StandardsFrameworkItem'
  AND r."targetEntityKey" = 'caseIdentifierUUID'
  AND r."targetEntityValue" = '6ba1c7ad-d7cc-11e8-824f-0242ac160002';

​
List standard(s) supported by a learning component

SELECT sfi.*
FROM relationships r
JOIN standards_framework_item sfi
  ON sfi."caseIdentifierUUID" = r."targetEntityValue"
WHERE r."relationshipType" = 'supports'
  AND r."sourceEntity" = 'LearningComponent'
  AND r."sourceEntityKey" = 'identifier'
  AND r."targetEntity" = 'StandardsFrameworkItem'
  AND r."targetEntityKey" = 'caseIdentifierUUID'
  AND r."sourceEntityValue" = '019a93e7-1877-5e9b-b4b3-476f201fccc8';

​
Curriculum queries

To learn more about the entities and relationships related to these queries, read more here.

​
Get activities in a lesson in a course

​
Return all the activities for lesson 1 and grade 6, unit 2, section A 

SELECT c.*, u.*, s.*, l.*, a.*
FROM course c
JOIN relationships r1 ON r1.source_key = c.identifier 
  AND r1.source_label = 'Course' 
  AND r1.target_label = 'LessonGrouping' 
  AND r1.relationship_name = 'hasPart'
JOIN lessongrouping u ON u.identifier = r1.target_key AND u."ordinalName" = 'Unit 2'

JOIN relationships r2 ON r2.source_key = u.identifier 
  AND r2.source_label = 'LessonGrouping' 
  AND r2.target_label = 'LessonGrouping' 
  AND r2.relationship_name = 'hasPart'
JOIN lessongrouping s ON s.identifier = r2.target_key AND s."ordinalName" = 'Section A'

JOIN relationships r3 ON r3.source_key = s.identifier 
  AND r3.source_label = 'LessonGrouping' 
  AND r3.target_label = 'Lesson' 
  AND r3.relationship_name = 'hasPart'
JOIN lesson l ON l.identifier = r3.target_key AND l."ordinalName" = 'Lesson 1'

JOIN relationships r4 ON r4.source_key = l.identifier 
  AND r4.source_label = 'Lesson' 
  AND r4.target_label = 'Activity' 
  AND r4.relationship_name = 'hasPart'
JOIN activity a ON a.identifier = r4.target_key
WHERE c.name = 'Grade 6';

​
Get all activities that use an instructional routine in a course

​
Return all 7th grade activities that uses the “Notice and Wonder” instructional routine

SELECT a.*, ir.*
FROM activity a
JOIN relationships r ON a.identifier = r.source_key
  AND r.source_label = 'Activity'
  AND r.target_label = 'InstructionalRoutine'
  AND r.relationship_name = 'usesRoutine'
JOIN instructionalroutine ir ON ir.identifier = r.target_key
WHERE ir.name = 'Notice and Wonder'
  AND a."gradeLevel" LIKE '%7%';

​
Get names and IDs of all units in a course

​
Return all units in the grade 4 course

SELECT u."ordinalName", u.name, u.identifier
FROM course c
JOIN relationships r ON c.identifier = r.source_key
  AND r.source_label = 'Course'
  AND r.target_label = 'LessonGrouping'
  AND r.relationship_name = 'hasPart'
JOIN lessongrouping u ON u.identifier = r.target_key
WHERE c.name = 'Grade 4'
  AND u."curriculumLabel" = 'Unit'
ORDER BY u."ordinalName";

​
Get all unit dependencies for a unit in a course

​
Find all the prerequisite units for Unit 5 in course im360:8 and return those units

SELECT DISTINCT lg.*, lg1.*
FROM lessongrouping lg
JOIN relationships r ON r.source_key = lg.identifier
JOIN lessongrouping lg1 ON lg1.identifier = r.target_key
WHERE r.relationship_name = 'hasDependency'
  AND r.source_label = 'LessonGrouping'
  AND r.target_label = 'LessonGrouping'
  AND lg."ordinalName" = 'Unit 5'
  AND lg."courseCode" = 'im360:8';