-
Notifications
You must be signed in to change notification settings - Fork 0
/
psqljson-cheatsheet.txt
205 lines (169 loc) · 11.2 KB
/
psqljson-cheatsheet.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
Working with JSONB columns in Postgres is a bit weird.
In our case, the JSON data being loaded into Postgres by SORLoader is fairly complex. The reginfo data,
which is one key in the top-level json hash, consists of a hash of arrays of hashes
The reginfo data is the data we'll be mining with Grouper to create auto-populated course, section
plan, and term groups
The RegInfo data looks like this:
RegInfo
|____ Program [
| {
| name: BUSMOTMBA,
| type: MSR
| },
| {
| name: yyy,
| type: yyy
| }
| ]
|____ Affiliation [
| "GRAD"
| ]
|
|____ Course [
{
grp: BUS,
crsename: BUS
crsenum: 758
sect: G100,
semester: 1194
status: E|W|?,
statusreason: ENRL
}
]
Note that this data is subject to change if/when we get more data from the SIMS feed. The beauty of JSON
is that no changes to the database schema are needed, but tweaks to the Views or SQL queries definitely
will be
All SoR data will be loaded into the same table, and the data from HR and Instructor feeds looks very
different from Registration data. But JSONB queries automatically ignore rows whose JSONB data structures
don't match the query
Here are a few example SQL queries using Postgres's native json datatypes
Ex: Produce a table of SFUIDs, course name/num/sect for a given term - basically all enrollments for a term:
SELECT sfuid,c->>'crsename' as course, c->>'crsenum' as coursenum, c->>'sect' AS section FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c WHERE c->>'semester' = '1174';
How it works:
- "userdata" is the name of our JSONB column that holds all JSON data for a user record
- the "->" operator means return the JSON below the associated key, so "userdata->'reginfo'->'course' returns a JSONB array of all of the courses a student is enrolled in
- the jsonb_array_elements built-in Postgres function returns a table values in a JSONB array. In this case, each value will itself be a JSON object (the course hash)
- the WHERE clause searches the resulting array of objects for one where the semester key contains a value of '1174'
- the "->>" operator means "return the text value associated with the json key. So "c->>'crsename'" returns the value of crsename
Ex: Fetch all Program names
SELECT p->>'name' AS name,p->>'type' AS type FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'program') AS p GROUP BY name,type;
Ex: Fetch all active students and their associated Academic Plans
select sfuid,p->>'name' as program from sorpeople as t, jsonb_array_elements(t.userdata->'reginfo'->'program') as p where t.status='active';
Ex: Same, but using the Uuid table too
select u.uuid,p->>'name' as program from sorpeople as t, jsonb_array_elements(t.userdata->'reginfo'->'program') as p, sorpeople_uuid as u where t.status='active' and t.sfuid = u.sfuid;
Ex: fetch all employee affiliations and output as Grouper names:
select distinct concat_ws(':'::text,'basis:affiliations',j->>'affiliation') as group_name,u.uuid as "SUBJECT_ID" from sorpeople as t,sorpeople_uuid as u,jsonb_array_elements(t.userdata->'job') as j where j->>'status' <> 'T' and t.sfuid = u.sfuid;
(note that this query returns *all* users in an affiliation, even if it's not set as their primary job in the HR feed)
Views
-----
Grouper made use of Views into the Amaint database to structure the group data exactly the way Grouper needed it. We can do the same for the SoRObject data
with simpler views, as almost everything is in one table.
These are the Amaint views as defined in the MySQL database:
This view returns all course grouper groups and their corresponding reader and updater groups.
CREATE VIEW `grouper_course_group_all_query_v`
AS select distinct concat_ws(':','course',`c`.`semester`,`c`.`crsename`,`c`.`crsenum`,'students_systemOfRecord') AS `group_name`,
concat('course:security:readers,course:security:',`c`.`crsename`,':readers') AS `readers`,
concat('course:security:updaters,course:security:',`c`.`crsename`,':updaters') AS `updaters`,
`c`.`crsename` AS `crsename`,
`c`.`crsenum` AS `crsenum`,
`c`.`semester` AS `semester`
from ((`roster` `r` join `course` `c`) join `section` `s`) where ((`r`.`sectionid` = `s`.`oid`) and (`s`.`courseid` = `c`.`oid`)
Retrieve the sections of a course
CREATE VIEW `grouper_course_group_query_v`
AS select distinct concat_ws(':','course',`c`.`semester`,`c`.`crsename`,`c`.`crsenum`,`s`.`section`,'students_systemOfRecord') AS `group_name`,
concat('course:security:readers,course:security:',`c`.`crsename`,':readers') AS `readers`,
concat('course:security:updaters,course:security:',`c`.`crsename`,':updaters') AS `updaters`,
`c`.`crsename` AS `crsename`,
`c`.`crsenum` AS `crsenum`,
`c`.`semester` AS `semester`,
`s`.`section` AS `section`
from ((`roster` `r` join `course` `c`) join `section` `s`) where ((`r`.`sectionid` = `s`.`oid`) and (`s`.`courseid` = `c`.`oid`))
This view returns all students in all courses, organized into grouper groups
CREATE VIEW `grouper_course_list_all_v`
AS select distinct concat_ws(':','course',`c`.`semester`,`c`.`crsename`,`c`.`crsenum`,'students_systemOfRecord') AS `group_name`,
`r`.`username` AS `SUBJECT_ID`,
`c`.`crsename` AS `crsename`,`
c`.`crsenum` AS `crsenum`,
`c`.`semester` AS `semester`
from ((`roster` `r` join `course` `c`) join `section` `s`) where ((`r`.`sectionid` = `s`.`oid`) and (`s`.`courseid` = `c`.`oid`))
Retrieve all students in all sections of a course.
CREATE VIEW `grouper_course_list_v`
AS select distinct concat_ws(':','course',`c`.`semester`,`c`.`crsename`,`c`.`crsenum`,`s`.`section`,'students_systemOfRecord') AS `group_name`,
`r`.`username` AS `SUBJECT_ID`,
`c`.`crsename` AS `crsename`,
`c`.`crsenum` AS `crsenum`,
`c`.`semester` AS `semester`
from ((`roster` `r` join `course` `c`) join `section` `s`) where ((`r`.`sectionid` = `s`.`oid`) and (`s`.`courseid` = `c`.`oid`))
There are similar views to retrieve the instructors of courses, rather than students. These are used by Grouper to populate the Readers and Updaters groups.
Let's translate one of these views into a Postgres view into the SoRObject table:
CREATE OR REPLACE VIEW grouper_course_list_all_v AS
select distinct concat_ws(':','course',c->>'semester',LOWER(c->>'crsename'),LOWER(c->>'crsenum'),'students_systemOfRecord') AS group_name,
u.uuid AS "SUBJECT_ID",
LOWER(c->>'crsename') AS crsename,
c->>'crsenum' AS crsenum,
c->>'semester' AS semester
FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c, sorpeople_uuid as u WHERE t.sfuid = u.sfuid;
These views are also all needed for Grouper:
CREATE OR REPLACE VIEW grouper_course_list_v AS
select distinct concat_ws(':','course',c->>'semester',LOWER(c->>'crsename'),LOWER(c->>'crsenum'),LOWER(c->>'sect'),'students_systemOfRecord') AS group_name,
u.uuid AS "SUBJECT_ID",
LOWER(c->>'crsename') AS crsename,
c->>'crsenum' AS crsenum,c->>'sect' as section,
c->>'semester' AS semester
FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c, sorpeople_uuid as u WHERE t.sfuid = u.sfuid;
CREATE OR REPLACE VIEW grouper_course_list_100level_v AS
select distinct concat_ws(':','course',c->>'semester',LOWER(c->>'crsename'),'100-level','students_systemOfRecord') AS group_name,
u.uuid AS "SUBJECT_ID",
LOWER(c->>'crsename') AS crsename,
c->>'semester' AS semester
FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c, sorpeople_uuid as u WHERE t.sfuid = u.sfuid and c->>'crsenum' < '200';
CREATE OR REPLACE VIEW grouper_course_list_gradlevel_v AS
select distinct concat_ws(':','course',c->>'semester',LOWER(c->>'crsename'),'100-level','students_systemOfRecord') AS group_name,
u.uuid AS "SUBJECT_ID",
LOWER(c->>'crsename') AS crsename,
c->>'semester' AS semester
FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c, sorpeople_uuid as u WHERE t.sfuid = u.sfuid and c->>'crsenum' >= '500';
CREATE OR REPLACE VIEW grouper_course_list_200level_v AS
select distinct concat_ws(':','course',c->>'semester',LOWER(c->>'crsename'),'100-level','students_systemOfRecord') AS group_name,
u.uuid AS "SUBJECT_ID",
LOWER(c->>'crsename') AS crsename,
c->>'semester' AS semester
FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c, sorpeople_uuid as u WHERE t.sfuid = u.sfuid and c->>'crsenum' >= '200' and c->>'crsenum' < '300';
CREATE OR REPLACE VIEW grouper_course_list_300level_v AS
select distinct concat_ws(':','course',c->>'semester',LOWER(c->>'crsename'),'100-level','students_systemOfRecord') AS group_name,
u.uuid AS "SUBJECT_ID",
LOWER(c->>'crsename') AS crsename,
c->>'semester' AS semester
FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c, sorpeople_uuid as u WHERE t.sfuid = u.sfuid and c->>'crsenum' >= '300' and c->>'crsenum' < '400';
CREATE OR REPLACE VIEW grouper_course_list_400level_v AS
select distinct concat_ws(':','course',c->>'semester',LOWER(c->>'crsename'),'100-level','students_systemOfRecord') AS group_name,
u.uuid AS "SUBJECT_ID",
LOWER(c->>'crsename') AS crsename,
c->>'semester' AS semester
FROM sorpeople AS t, jsonb_array_elements(t.userdata->'reginfo'->'course') AS c, sorpeople_uuid as u WHERE t.sfuid = u.sfuid and c->>'crsenum' >= '400' and c->>'crsenum' < '500';
Grouper Employees Views
CREATE VIEW grouper_employees_v AS
select distinct concat_ws(':'::text,'basis:affiliations',j->>'afflname') as group_name,
u.uuid as "SUBJECT_ID",
j->>'status' as status,
u.sfuid as sfuid
FROM sorpeople as t,sorpeople_uuid as u,jsonb_array_elements(t.userdata->'job') as j where j->>'status' <> 'T' and t.sfuid = u.sfuid;
Grouper Employees View, replacing illegal chars in group_name:
CREATE OR REPLACE VIEW grouper_employees_v AS
select distinct concat_ws(':'::text,'basis:affiliations',regexp_replace(lower(j.value ->> 'afflname'::text),'[^a-zA-Z0-9-]','_','g') ) as group_name,
u.uuid as "SUBJECT_ID",
j->>'status' as status,
u.sfuid as sfuid
FROM sorpeople as t,sorpeople_uuid as u,jsonb_array_elements(t.userdata->'job') as j where j->>'status' <> 'T' and t.sfuid = u.sfuid and j.value->>'afflname'::text != '';
Grouper Departments View
CREATE VIEW grouper_depts_v AS
select distinct concat_ws(':'::text,'basis:depts:units',j->>'deptcode') as group_name,
u.uuid as "SUBJECT_ID",
j->>'status' as status,
u.sfuid as sfuid
FROM sorpeople as t,sorpeople_uuid as u,jsonb_array_elements(t.userdata->'job') as j where j->>'status' <> 'T' and j->>'status' <> 'Q' and j->>'status' <> 'R' and t.sfuid = u.sfuid;
Grouper Academic Plans Views
create view grouper_academic_plans_v as
select distinct concat_ws(':'::text, 'course:plans',p->>'name') as group_name,
u.uuid as "SUBJECT_ID"
from sorpeople as t, jsonb_array_elements(t.userdata->'reginfo'->'program') as p, sorpeople_uuid as u where t.status='active' and t.source = 'SIMS' and t.sfuid = u.sfuid;